Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 проблема с cte (with)  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
есть запрос:
;with cte as
(
// тут формируется CTE, ничего интересного, есть функции substring
)
select [SysTime],[Param],[Value] from cte
where [Param] is not null

Он работал корректно около года.
с недавних пор стала появляться ошибка Invalid length parameter passed to the substring.
Начал разбираться, выяснилась непонятная особенность: если убрать where [Param] is not null, запрос возвращает результат без ошибок (пустая таблица CTE - без строк, это нормально), а если добавить условие, то ошибка. Почему?
20 янв 16, 09:41    [18704228]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
waszkiewicz
Member

Откуда:
Сообщений: 1087
=Сергей=,

появились записи, не удовлетворяющие условию
20 янв 16, 09:42    [18704231]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
Glory
Member

Откуда:
Сообщений: 104760
=Сергей=
Почему?

Потому, что в передаете в функцию substring неправильное значение для параметра length

select substring('aaaa', 1, -1)
20 янв 16, 09:46    [18704240]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Если я запускаю внутреннюю часть CTE, то она отрабатывает БЕЗ ОШИБОК - возвращает 0 строк.
почему, когда я накладываю условия на CTE, возникает ошибка?
20 янв 16, 09:48    [18704249]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
Glory
Member

Откуда:
Сообщений: 104760
=Сергей=
Если я запускаю внутреннюю часть CTE, то она отрабатывает БЕЗ ОШИБОК - возвращает 0 строк.
почему, когда я накладываю условия на CTE, возникает ошибка?

Еще раз
Ошибка появляется потому, что ваш код (именно ваш) передает в функцию substring неправильное значение для параметра length
Что вам непонятно ?
Садитесь и правите свой код так, чтобы он передавал в функцию правильные параметры
20 янв 16, 09:54    [18704273]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
=Сергей=,

Вы, наверно, полагаете, что сначала записи фильтруются в WHERE,
а поля SELECTа в CTE вычисляются потом?
Огорчу вас - это не так!
Оптимизатор запросто меняет физический порядок выполнения так, как ему удобно.
Так что вычисления могут выполняться до WHERE, а, следовательно,
при этом в функции могут передаваться некорректные параметры.
Обычно это обходится применением функции CASE
20 янв 16, 10:04    [18704320]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
То есть, убираете из запроса WHERE - план выбирается другой,
порядок вычисления другой, - ошибки нет.
20 янв 16, 10:06    [18704325]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
человек_ниоткуда
Guest
=Сергей=
// тут формируется CTE, ничего интересного, есть функции substring


Выходит что-то интересное там всётаки есть.
А пока что вопрос ваш выглядит так: "Ребята у меня чёрный ящик есть, ну что внутри это не интересно. Так вот он около года работал якорем, а сейчас что-то перестал тонуть. В чём проблема, как думаете?", - ответ очевиден, - "Проблема в ящике!". Что собственно в своей оскарбительной поэтичной манере пытается сказать Глоря.
20 янв 16, 10:08    [18704335]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Видимо, я ошибочно считал, что запрос
;with cte as (
select
    [A]
   ,[B]=substring([B],1,100)
   ,[C]
from [Table])
select * from cte where [A] is not null

возвращает такой же набор как
select
    [A]
   ,[B]=substring([B],1,100)
   ,[C]
into #T
from [Table]
select * from #T where [A] is not null
20 янв 16, 10:11    [18704350]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
Glory
Member

Откуда:
Сообщений: 104760
=Сергей=
Видимо, я ошибочно считал, что запрос

Вы хотите уверить, что в вашем запросе substring всегда вызывается с константами 1 и 100 ?
20 янв 16, 10:15    [18704369]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
нет, я просто недоумеваю, почему, когда я выполняю внутренности CTE, он отрабатывает без ошибок, когда я убираю условие отбора из CTE, тоже без ошибок, а когда я добавляю условие отбора из отработанной CTE, возникает ошибка.
20 янв 16, 10:25    [18704409]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
Glory
Member

Откуда:
Сообщений: 104760
=Сергей=
нет, я просто недоумеваю, почему, когда я выполняю внутренности CTE, он отрабатывает без ошибок, когда я убираю условие отбора из CTE, тоже без ошибок, а когда я добавляю условие отбора из отработанной CTE, возникает ошибка.

Потому, что вы написали багованный код.
20 янв 16, 10:26    [18704415]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Вот так приблизительно выглядит ваш багованнй код

select substring('abaa', 1, PATINDEX('%c%', 'abaa')-1)


а вот так он должен выглядеть

select substring('abaa', 1, CASE WHEN PATINDEX('%c%', 'abaa')>0 THEN PATINDEX('%c%', 'abaa') ELSE NULL END-1)
20 янв 16, 10:32    [18704431]     Ответить | Цитировать Сообщить модератору
 Re: проблема с cte (with)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
=Сергей=
нет, я просто недоумеваю, почему, когда я выполняю внутренности CTE, он отрабатывает без ошибок, когда я убираю условие отбора из CTE, тоже без ошибок, а когда я добавляю условие отбора из отработанной CTE, возникает ошибка.
Потому что в разных запросах разные планы выполнения.
При выполнении внутренности CTE не затрагиваются записи, которые приводят к ошибкам.
При добавлении условий отбора план меняется, и затрагиваются записи, которые приводят к ошибкам.
=Сергей=
Видимо, я ошибочно считал, что запрос
...
возвращает такой же набор как
Да, ошибочно считали.
20 янв 16, 14:25    [18706088]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить