«Невозможно создать тип данных datetime» при фильтрации данных, но все отфильтрованные значения ДЕЙСТВИТЕЛЬНО имеют допустимые даты

Я убежден, что этот вопрос НЕ является дубликатом: Невозможно создать тип данных datetime, некоторые аргументы имеют недопустимые значения

В этом случае переданные значения явно недействительны. Принимая во внимание, что в этом случае все значения, которые можно ожидать от функции, являются допустимыми.

Я знаю, в чем настоящая проблема, и это не то, что поможет большинству людей найти другой вопрос. Но это то, что было бы хорошо найти на SO.

Пожалуйста, прочитайте ответ и поймите, почему он отличается от связанного вопроса, прежде чем проголосовать за закрытие этого вопроса как обмана.


Я запустил некоторый SQL с ошибкой с сообщением об ошибке: Cannot construct data type datetime, some of the arguments have values which are not valid.

В моем SQL используется DATETIMEFROMPARTS, но эта функция нормально оценивается при выборе — это проблема только при фильтрации выбранного значения.

Он также демонстрирует странное поведение, которое невозможно. другие изменения в запросе.

Мой запрос выглядит примерно так:

WITH FilteredDataWithDate (
    SELECT *, DATETIMEFROMPARTS(...some integer columns representing date data...) AS Date
    FROM Table
    WHERE <unrelated pre-condition filter>
)
SELECT * FROM FilteredDataWithDate
  WHERE Date > '2020-01-01'

Если я запущу этот запрос, он выдаст ошибку invalid data. Но если я пропущу последний фильтр Date >, то он с радостью отобразит каждую запись результата, поэтому очевидно, что ни одно из значений, по которым он фильтрует, не является недопустимым.

Я также вручную проверил содержимое Table WHERE <unrelated pre-condition filter> и убедился, что все является допустимой датой.

У него также есть дикая коллекция других поведений:

  • Если я заменю все ...some integer columns representing date data... жестко запрограммированными числами, тогда все в порядке.
  • Если я заменю некоторые части этих данных жестко заданными значениями, это исправит ситуацию, а другие — нет. Я не нахожу каких-то конкретных закономерностей в том, что помогает, а что нет.
  • Если я удалю большинство столбцов * из Table select. Потом опять начинает хорошо.
    • В частности, он ломается каждый раз, когда я включаю столбец nvarchar(max) в CTE.
  • Если я добавлю в CTE дополнительный фильтр, который ограничивает результаты значениями Id в следующих диапазонах, то результаты будут такими:
    • 130 000 и 140 000. Ошибка.
    • 130 000 и 135 000. Отлично.
    • 135 000 и 140 000. Отлично.!!!!
  • Фильтрация по столбцу Date все ломает... но ORDER BY Date все в порядке. (и подтверждает, что все даты лежат в вполне разумных пределах.)
  • Добавление TOP 1000000 заставляет его работать ... хотя строк всего около 1000.

... ЧТО ТАКОЕ?!

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
83
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

У меня ушло некоторое время на расшифровку, но оказалось, что компилятор SS не обязательно ограничивает выполнение функции только строками, которые имеют или могут иметь отношение к результирующему набору.

В зависимости от полученного плана выполнения функция может быть вызвана для любой записи в Table, даже для той, которая не удовлетворяет WHERE <unrelated pre-condition filter>.

Это было найдено другим пользователем, для другой функции, здесь.

Таким образом, тот факт, что он мог вернуть все результаты без фильтра, на самом деле не доказывал, что все входные данные в функцию были действительными. И действительно, в таблице были некоторые записи, которых не было в результирующем наборе, но все же они содержали неверные данные.

На самом деле это означает, что даже если вы добавите явный фильтр WHERE для исключения строк, содержащих недопустимые данные компонента даты... это на самом деле не гарантирует его исправления, потому что функция все равно может быть вызвана для «исключенных» строк.

Каждая из случайных других вещей, которые я делал, так или иначе влияла на план запроса, что происходило, чтобы исправить/сломать вещи.

Решение, естественно, заключается в исправлении базовых данных таблицы.

Да, это действительно отстойная ошибка SQL Server, которая, скорее всего, никогда не будет исправлена. Другой способ избежать этой проблемы — использовать CASE WHEN, чтобы убедиться, что ваша функция вообще не вызывается, если только все поля не заполнены правильно.

siggemannen 11.02.2023 09:20

Вы можете попробовать OPTION( FORCE ORDER ) подсказку. Это может решить вашу проблему за счет снижения производительности.

Alex 13.02.2023 04:02

ОБНОВЛЕНО: @siggemannen, это не ошибка, а функция. Поскольку SQL Server выбирает план выполнения запроса, нет гарантии, что порядок оценки WHERE фильтров и SELECT функций будет поддерживаться, пока теоретически они возвращают один и тот же результат, например. 2 + 3 + 4 = 4 + 2 + 3. (См. Предупреждение в learn.microsoft.com/en-us/sql/t-sql/queries/… ). Такое поведение было введено в SQL 2005. Обычные CTE не оказывают никакого функционального влияния на запрос. Это просто синтаксический сахар.

Alex 13.02.2023 05:48

Да, я думаю, хотя проблема в том, что некоторые заказы оценки вообще не приводят к результату, что, по моему мнению, должно привести к тому, что SQL Server их полностью отклонит. Но да, это то, что есть, и, вероятно, не изменится.

siggemannen 13.02.2023 09:24

Другие вопросы по теме