Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как объяснить программисту как правильно писать запросы  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 763
Прислали мне сегодня код процедуры на проверку, и вижу я нечто вроде такого (имена переменных, полей и таблицы даны для примера):

select * from t1
where (f1 = @v1 or @v1 is not null)
and (f2 = @v2 or @v2 is not null)
and (f3 = @v3 or @v3 is not null)
and (f2 = @v4 or @v4 is not null)
and (f2 = @v5 or @v5 is not null)


Спрашиваю программиста что именно он имел ввиду написав это, получаю ответ - процедура вызывается с пятью параметрами, любой из них может получить значение NULL. Чтобы не проверять какие именно значения получены и не строить динамический SQL, пишут вот такой вот код. Может я чего-то не понимаю, но, как мне кажется, подобный код правильно работать не будет.
Допустим, переменная @v1 получила not null значение и такое значение имеется в поле f1, остальные - null. Тогда получается:
(f1 = @v1 or @v1 is not null) = true
(f2 = @v2 or @v2 is not null) = false
(f3 = @v3 or @v3 is not null) = false
(f2 = @v4 or @v4 is not null) = false
(f2 = @v5 or @v5 is not null) = false

Соответственно, подобный запрос не вернет ничего несмотря на то, что значение первой переменной not null и это значение есть в таблице. Или я ошибаюсь?
18 июн 19, 21:23    [21911027]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
flexgen,

ничего необычного в таком подходе программиста нет (если именно такая бизнес-логика),
строишь таблицу истинности (конъюнктивная {нормальная} форма) и проверяешь,
например если следовать твоему сценарию и {@v2~@v5} = null and {f2~f3} = null
то и все условия после первого f2=@v2 будут null=null , т.е. true
(максимальный индекс у f = 3, даже если ошибка - то не так важно для примера)
18 июн 19, 21:39    [21911033]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
другое дело что именно в таком на T-SQL null=null вовсе не true
18 июн 19, 21:44    [21911036]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
в смысле в таком .. виде (пропустил)
18 июн 19, 21:45    [21911037]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
блин, вот туплю, есть-же ведь: SET ANSI_NULLS OFF
18 июн 19, 21:50    [21911039]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
flexgen
Или я ошибаюсь?
Не ошибаетесь.

Требуемое можно написать так
select * from t1
where
exists(
 select f1, f2, f3, f4, f5
 intersect
 select @v1, @v2, @v3, @v4, @v5
);

При таком способе еще и индекс может быть задействован для поиска.
18 июн 19, 22:01    [21911046]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
invm
flexgen
Соответственно, подобный запрос не вернет ничего несмотря на то, что значение первой переменной not null и это значение есть в таблице. Или я ошибаюсь?
Не ошибаетесь.
не согласен (кроме как про индекс), я с горяча в панике что накосячил - уже несколько раз перепроверил
если через подмену то матемитически получится (при fn {n>1} = null и @vn {n>1} = null)


(1 = 1 or 1)
&(null = null or 0)
&(null = null or 0)
&(null = null or 0)
&(null = null or 0)

что преобразовывается в

(1 = 1 or 1) = true
&(1 or 0) = true
&(1 or 0) = true
&(1 or 0) = true
&(1 or 0) = true

откуда результат = true
т.е. при разных значениях fn (где часть = null) будет работать фильтрация
18 июн 19, 22:11    [21911049]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
vikkiv,

declare @t table (f1 int);
insert into @t values (1), (null);

declare @f1 int;

select @f1 = 1;
select * from @t where f1 = @f1 or @f1 is not null;

select @f1 = null;
select * from @t where f1 = @f1 or @f1 is not null;
18 июн 19, 22:22    [21911052]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
invm,
https://rextester.com/EMHCV18362
set ansi_nulls off
declare 
@v1 char(1)='a'
,@v2 char(1)=null
,@v3 char(1)=null
declare @t1 table(f1 char(1),f2 char(1),f3 char(1))
insert into @t1(f1,f2,f3)values
(null,null,null)
,(null,null,'c')
,(null,'b',null)
,(null,'b','c')
,('a',null,null)
,('a',null,'c')
,('a','b',null)
,('a','b','c')

select * from @t1
where (f1 = @v1 or @v1 is not null)
and (f2 = @v2 or @v2 is not null)
and (f3 = @v3 or @v3 is not null)
18 июн 19, 22:46    [21911065]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2626
в смысле тавтология конечно но: в разных конструкциях в разных местах будет работать по разному с разными настройками
18 июн 19, 22:47    [21911067]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
uaggster
Member

Откуда:
Сообщений: 770
invm
flexgen
Или я ошибаюсь?
Не ошибаетесь.

Требуемое можно написать так
select * from t1
where
exists(
 select f1, f2, f3, f4, f5
 intersect
 select @v1, @v2, @v3, @v4, @v5
);

При таком способе еще и индекс может быть задействован для поиска.

Класс какой!
Интересно, какой план даст. В любом случае, спасибо. Изящно.
19 июн 19, 09:25    [21911221]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
vikkiv
set ansi_nulls off
1. За такое как минимум лишают премии, а как максимум сразу увольняют - в назидание оставшимся.
2. В этом случае проверять переменные на null не требуется.
19 июн 19, 09:28    [21911225]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
flexgen
Соответственно, подобный запрос не вернет ничего несмотря на то, что значение первой переменной not null и это значение есть в таблице. Или я ошибаюсь?
Не ошибаетесь
Но вы неправильно переписали запрос.
На самом деле:
select * from t1
where (f1 = @v1 or @v1 is null)
and (f2 = @v2 or @v2 is null)
and (f3 = @v3 or @v3 is null)
and (f2 = @v4 or @v4 is null)
and (f2 = @v5 or @v5 is null)

PS Удивительно, как компании случайным образом расставляют проверяющих и проверяемых :-)
19 июн 19, 09:52    [21911238]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
alexeyvg
Но вы неправильно переписали запрос.
Не факт.
Не сказано, что null в параметре означает исключение соответствующего столбца из фильтра.
19 июн 19, 10:06    [21911250]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2375
flexgen,

в общем, как уже сказали вариант "f1 = @v1 or @v1 is not null" неправильный,
должен быть "f1 = @v1 or @v1 is null"
19 июн 19, 10:30    [21911273]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1871
StarikNavy
flexgen,

в общем, как уже сказали вариант "f1 = @v1 or @v1 is not null" неправильный,
должен быть "f1 = @v1 or @v1 is null"

и в конце запроса еще и option (recompile) :)
19 июн 19, 11:18    [21911325]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
iiyama
Member

Откуда:
Сообщений: 642
alexeyvg,

+1 к is null
иначе запрос будет возвращать все содержимое таблицы если все исходные параметры не NULL => нет смысла проверки на равенство

invm, изящно, спасибо
exists(
 select f1, f2, f3, f4, f5
 intersect
 select @v1, @v2, @v3, @v4, @v5
19 июн 19, 12:26    [21911409]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
aleks222
Member

Откуда:
Сообщений: 855
vikkiv
(f1 = @v1 or @v1 is not null)


Я один не въезжаю в сакральный смысл этой конструкции?
19 июн 19, 12:28    [21911411]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm
flexgen
Или я ошибаюсь?
Не ошибаетесь.

Требуемое можно написать так
select * from t1
where
exists(
 select f1, f2, f3, f4, f5
 intersect
 select @v1, @v2, @v3, @v4, @v5
);

При таком способе еще и индекс может быть задействован для поиска.

это вообще к чему? Речь идёт о том что передано 2 из 5ти переменных и надо вернуть по двум условиям.
19 июн 19, 12:43    [21911435]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
iiyama
Member

Откуда:
Сообщений: 642
aleks222,
в рамках set ansi_nulls off задача имеет смысл
[b]set ansi_nulls off[/b]

declare @t table(f1 int, f2 int, f3 int, f4 int, f5 int)
declare @v1 int, @v2 int=99, @v3 int=99, @v4 int=99, @v5 int=99

insert into @t values (1,2,3,4,5),(null,3,4,5,6)

select * from @t t1
where (f1 = @v1 or @v1 is not null)
and (f2 = @v2 or @v2 is not null)
and (f3 = @v3 or @v3 is not null)
and (f2 = @v4 or @v4 is not null)
and (f2 = @v5 or @v5 is not null)
19 июн 19, 12:43    [21911436]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iiyama,

автор
в рамках set ansi_nulls off задача имеет смысл

в рамках психбольницы в которую попадут те кто будет искать какого же хрена....
19 июн 19, 12:46    [21911439]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4101
select * from t1
where (f1 = @v1 or @v1 is not null)
and (f2 = @v2 or @v2 is not null)
and (f3 = @v3 or @v3 is not null)
and (f2 = @v4 or @v4 is not null)
and (f2 = @v5 or @v5 is not null)

Чушь какая-то...
Если нужно исключить параметры, которые NULL из критерия запросов, то я использую схему
select * from t1
where ISNULL(@v1, f1) = f1
  and ISNULL(@v2, f2) = f2
...

Косяк будет если в таблице в этих полях будет тоже NULL
Тогда чуть допилим запрос
select * from t1
where COALESCE(@v1, f1, 1) = COALESCE(f1, 1)
  and COALESCE(@v2, f2, 1) = COALESCE(f2, 1)
...


P.S. Сравнение NULL = NULL лучше не включать никогда.
Можно поймать огромных проблем.
19 июн 19, 13:00    [21911461]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4101
TaPaK
iiyama,

автор
в рамках set ansi_nulls off задача имеет смысл

в рамках психбольницы в которую попадут те кто будет искать какого же хрена....

+100500
19 июн 19, 13:01    [21911463]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
msLex
Member

Откуда:
Сообщений: 7735
SQL2008
select * from t1
where (f1 = @v1 or @v1 is not null)
and (f2 = @v2 or @v2 is not null)
and (f3 = @v3 or @v3 is not null)
and (f2 = @v4 or @v4 is not null)
and (f2 = @v5 or @v5 is not null)


Чушь какая-то...
Если нужно исключить параметры, которые NULL из критерия запросов, то я использую схему
select * from t1
where ISNULL(@v1, f1) = f1
  and ISNULL(@v2, f2) = f2
...


Косяк будет если в таблице в этих полях будет тоже NULL
Тогда чуть допилим запрос
select * from t1
where COALESCE(@v1, f1, 1) = COALESCE(f1, 1)
  and COALESCE(@v2, f2, 1) = COALESCE(f2, 1)
...



P.S. Сравнение NULL = NULL лучше не включать никогда.
Можно поймать огромных проблем.


и не оставить шансов оптимизатору использовать индекс
19 июн 19, 13:11    [21911474]     Ответить | Цитировать Сообщить модератору
 Re: Как объяснить программисту как правильно писать запросы  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4101
msLex
и не оставить шансов оптимизатору использовать индекс

Ну значит изначально делать правильную архитектуру.
Сейчас работаю с базой, где числовые и временнЫе данные забиты в текстовые поля...
Какой прок от оптимизатора в таких случаях?
19 июн 19, 13:22    [21911486]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить