Na czym polega zabieg porównywania "jedynek" w zapytaniu SQL

Witam.
Czy jest w stanie mi ktoś wytłumaczyć dlaczego Comarch w swoich zapytaniach robi taki zabieg

WHERE ((((1=1) AND (1=1))))

Jaki to ma sens?

Całe, przykładowe zapytanie

SELECT BRp_BRpId, CASE WHEN BRp_NumerObcy<>'' THEN BRp_NumerObcy ELSE BRp_NumerPelny END AS Numer, CASE WHEN BRp_TS_Export Is Not Null THEN 'E' ELSE '' END AS exprE, BRp_DataDok, BRp_SaldoBO AS exprSaldoBO, BRp_Przychody - BRp_Rozchody AS exprObroty, BRp_Przychody AS exprPrzychody, BRp_Rozchody AS exprRozchody, CASE WHEN BRp_Zamkniety=1 THEN BRp_SaldoBO + BRp_Przychody - BRp_Rozchody ELSE 0 END AS exprSaldo, CASE WHEN BRa_Waluta = '' THEN 'PLN' ELSE BRa_Waluta END AS exprWaluta, BRp_DataZam, BRp_BRaID, BRp_NumerPelny, BRa_Symbol, BRp_DekID, BRp_Zamkniety, BRp_PreDekId 
FROM CDN.BnkRaporty A 
JOIN CDN.BnkRachunki B ON B.BRa_BRaID = A.BRp_BRaID
WHERE ((((1=1) AND (1=1))))
ORDER BY BRp_BRaID, BRp_DataDok

1=1 to jest sposób na zapisanie true SQLu. Czasem przydaje się przy automatycznym generowaniu zapytań. Jednak w tym przypadku wydaje się to nie mieć żadnego sensu. Jeden kopioał od drugiego i tak zostało, jakbym miał strzelać

No ok, ale bez 1=1 wynik będzie taki sam. Jak by musiało wyglądać to automatyczne generowanie zapytań żeby to miało sens? Czy w tym automatycznym generowaniu brak 1=1 powoduje inny wynik?

AdamWox napisał(a):

Czy w tym automatycznym generowaniu brak 1=1 powoduje inny wynik?

Oczywiście nie powoduje innego wyniku. Prościej tylko napisać generator np.

val w = "where 1=1 " + (conditions.map(c => "AND " + c).mkString)

Czyli nie trzeba pamiętać o usuwaniu pierwszego AND przy generowaniu warunków

W tym zapytaniu nie ma to sensu tak jak pisze @KamilAdam.
Jest to warunek pusty. Jest uzywany gdy zapytanie potrzebuje warunku zeby zadzialac na calej tabeli np.

delete from tabela where 1=1

Ja takie rzeczy stosuje w narzędziach gdzie np. w definicji okna czasem jest warunek a czasem go nie ma. I jak jest warunek to podstawiam tu odpowiedni warunek. Jak go nie ma a konstrukcja czegoś wymaga to się wrzucam takie 1=1, które logicznie nie ma żadnego wpływu.

Takie coś może być też zastosowanie w złych intencjach jako SQL Injection ;)

To ma sens np. w Oracle https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

--- zamiast
select * from table t where t.dupa in(1,3,...)
--- piszemy 
select * from table t where (t.dupa, 'a') in(
    (1, 'a'),
    (2, 'a'),
    (3, 'a'),
    (4, 'a'),
---...
)

Działa to na tej zasadzie, że im więcej wartości porównujemy na raz, tym większy mamy limit. Dla jednej wartości jest to 1000, dla dwóch ponad milion (albo jakoś tak). Tak że to - zależnie od bazy danych - może używać efektów ubocznych w celu obejścia limitów itp.

Nie trzeba 1=1, wystarczy 1:

SELECT * FROM my_table WHERE 1 AND name = 'john';

TomRZ napisał(a):

Nie trzeba 1=1, wystarczy 1:

Kopiuj

SELECT * FROM my_table WHERE 1 AND name = 'john';

Jaka baza?
Na Postgresie dostaje błąd: argument of AND must be type boolean, not type integer

KamilAdam napisał(a):

TomRZ napisał(a):

Nie trzeba 1=1, wystarczy 1:

Kopiuj

SELECT * FROM my_table WHERE 1 AND name = 'john';

Jaka baza?
Na Postgresie dostaje błąd: argument of AND must be type boolean, not type integer

Zgodne ze standardem (powinno działać wszędzie) jest 1=1. Zapisy w stylu where 1 lub where true zadziałają tylko na wybranych implementacjach języka SQL.

SQLserver rzuci

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

z tego co kojarze na mysql zadziala sama jedynka

Panczo napisał(a):

SQLserver rzuci

Kopiuj

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

z tego co kojarze na mysql zadziala sama jedynka

Zadziała tam, gdzie jest zdefiniowanie rzutowanie, że 1 - to true a 0 false. Czyli pewnie wszędzie tam, gdzie jest zdefiniowany niestandardowy typ SQL - boolean.

UglyMan napisał(a):

Zadziała tam, gdzie jest zdefiniowanie rzutowanie, że 1 - to true a 0 false. Czyli pewnie wszędzie tam, gdzie jest zdefiniowany niestandardowy typ SQL - boolean.

Postgres ma typ boolean więc where true i where 1::Boolean działają. Jednak where 1 nie działa bo SQL w Postgresie jest statycznie typowany :D

Nie wiem dlaczego jest tam porównanie podwójnie, może to wyszło z jakiegoś generatora. A WHERE 1=1 czasem jest używane by takie generatory uprościć (dzięki temu nowe elementy do zapytania możesz dodawać w postaci query + " AND " + new_case).

Takie generowanie zapytań = zabójstwo wydajności i niekiedy bezpieczeństwa.

yarel napisał(a):

Takie generowanie zapytań = zabójstwo wydajności i niekiedy bezpieczeństwa.

A dlaczego zabójstwo wydajności?

@UglyMan: Zapytania traktowane są jako "nowe" przez silnik bazodanowy. W efekcie sporo czasu idzie na ich przetwarzanie "od zera", zamiast wykorzystanie wersji, która jest już silnikowi znana. Dla której został opracowany plan zapytania.

Mogę pokusić się o taką przewrotną analogię do przetwarzania kodu źródłowego:

1. Nowe zapytanie -> nowy plik źródłowy -> kompilacja -> uruchomienie.
2. Istniejące zapytanie -> uruchomienie.
   (istniejące = widziane już kiedyś przez silnik bazodanowy, przetworzone, cachowane)

yarel napisał(a):

@UglyMan: Zapytania traktowane są jako "nowe" przez silnik bazodanowy. W efekcie sporo czasu idzie na ich przetwarzanie "od zera", zamiast wykorzystanie wersji, która jest już silnikowi znana. Dla której został opracowany plan zapytania.

Mogę pokusić się o taką przewrotną analogię do przetwarzania kodu źródłowego:

1. Nowe zapytanie -> nowy plik źródłowy -> kompilacja -> uruchomienie.
2. Istniejące zapytanie -> uruchomienie.
   (istniejące = widziane już kiedyś przez silnik bazodanowy, przetworzone, cachowane)

Ale nie wiem jak to się ma do generowanych zapytań - jak zawsze je wygenerujesz tak samo?

@UglyMan: tak, że generujesz na podstawie danych wprowadzanych przez użytkowników. Jeśli Kazio wstawi order=123, a Zosia order=456, to masz 2 różne zapytania. Jeśli takich userów masz więcej i każdy pracuje nad czym innym, to masz odpowiednio więcej unikalnych zapytań. Część obskoczysz przez zmienne wiązane (bind variables), część ogarnie DBA, bo zobaczy, że developer nie użył bind variables. Jeśli dynamicznie będziesz budował bloki wartosc IN (x,y,...,z), to ani DBA ani bind variables nie pomogą za dużo.

Zapytania traktowane są jako "nowe" przez silnik bazodanowy. W efekcie sporo czasu idzie na ich przetwarzanie "od zera", zamiast wykorzystanie wersji, która jest już silnikowi znana. Dla której został opracowany plan zapytania.

Co do zasady sie zgodzę, co do spadku wydajności niekoniecznie. W przypadku SQL Server kazde zapytanie adhoc ma przygotowywany plan wykonania, jednak taki plan jest zapamiętywany w cache.

Mechanizm mniej wiecej taki:

1. Puszczenie zapytania adhoc
2. wyliczenie wartości hasha dla zapytania
3. Jeżeli dla danego hasha jest zrobiony plan to wykorzystaj, jesli nie to stworz i zapamietaj.

Czyli puszczenie po raz kolejny takiego samego zapytania nie wplywa znaczaco na utrate wydajnosci (nie w kontekscie calego proceszu potrzebnego przez silnik na wygenerowaniu planu itd)

Oczywiscie znam pulapki jak i obejscia, ale Dynamiczne zapytania to temat na dluga rozmowe. Jak ktos zainteresowany to dla SQL Servera polecam: https://www.sommarskog.se/dynamic_sql.html

yarel napisał(a):

Jeśli Kazio wstawi order=123, a Zosia order=456, to masz 2 różne zapytania.

Ale takich zapytań się nie generuje :D O wiele częście są to przypadki gdy jeden szuka po cenie, a drugi po nazwie lub innym kolorze.
Widziałem SQLe poskładane w Javie na ifach na cały ekran w zależności od tego które z 20 parametrów do yszukiwania wypełnił użytkownik

Ja kiedyś dawno tego w MySQLu używałem, i generalnie brzydkie to jest.

Teraz po prostu przy generowaniu warunków mam zmienną która określa czy jest już jakiś warunek, czy nie, i jeżeli jeszcze żadnego nie ma to nie dodaje AND przed początkiem całego warunku.

Przy czym w dobie różnego rodzaju ORMów i gotowych generatorów SQL, to robienie tego własnoręcznie coraz bardziej traci sens.

@KamilAdam: nie powinno się takowych generować, ale niestety rzeczywistość pokazuje, że jednak bind variable nie są powszechnie używane i mogą lecieć dwa różne zapytania:
select * from orders where order=123
select * from orders where order=456

W przypadku pól typu wybór wielu wartości, np. Kolor={zielony,czerwony, niebieski} daje to 4 różne zapytania:

• bez IN,
• IN (:a),
• IN (:a,:b),
• IN (:a,:b,:c) przy użyciu bind variables, a bez takowych to nie chce mi się liczyć kombinacji ;)

Problemem nie jest wówczas Twoja porządna aplikacja, która generuje porządne zapytania, tylko aplikacja, które generuje masę śmiecia, które wyrzucają plan Twojego zapytania z cache silnika bazodanowego.

Jeśli lista do IN nie ma określonej długości (bo jest np. wyciągana z jakiejś konfiguracji, bądź rezultatów wyszukiwania), to ilość zapytań, które ominą cache'a rośnie. Co gorsza, inne sesje mogą być zmuszone do oczekiwania, aż aż taki cache zostanie zaktualizowany. Można, żyć, ale to jak jazda za autobusem, który się zatrzymuje co przystanek, ale nie zjeżdża do wysepki. Można się z tym pogodzić ;)

@Panczo: dla takich zapytań (bez bind variable) MS SQL liczy ten sam hasz? Czy jest sprytny i zakłada, że jak zapytanie przyszło od aplikacji, to i tak wartości należy wyciąć i zastąpić zmiennymi?

Poglądowo:
HASZ(select * from orders where order=123)=123
HASZ(select * from orders where order=456)=456

HASZ(select * from orders where order=:order)=42 /* np. silnik uznał, że aplikacja jest słaba i zastąpił 123, 456 przez zmienną */

To dziala bardziej topornie bo hash liczy z tekstu zapytania i tak

--zapytanie 1
select * from orders where order=123

--zapytanie 2
select * from orders Where order=123

Oba beda mialy rozne hashe (zmiana w->W w where), co do twojego przykladu to tu zadziala mechanizm Simple Parameterization gdzie w cachu bedzie:

select * from orders where order=@1

Mozna tez zmusić silnik do tego by każdą wartość parametryzowal (Forced Parameterization)

Nie jestem specem od oracle ale te wspomniane bind values, po krótkim googlaniu, można przyrownac do sp_executesql z podanymi parametrami, ale dziala dokladnie ten sam mechanizm cacheowania jak dla zapytan adhoc => zmiana tekstu w przekazywanym zapytaniu powoduje wygenerowanie nowego planu

@yarel: ale Ty wiesz, że praktycznie wszystkie biblioteki obecnie używają prepared statements. A prepared statements też nie są lekiem na całe zło i czasem jednak lepiej generować plan dla każdego zapytania osobno.

Ja cię! Panowie, ja się tylko zapytałem :D a tu debata poważna