| Subject | Re: [firebird-support] Is there any logical difference? |
|---|---|
| Author | setysvar |
| Post date | 2019-08-09T17:15:59Z |
Well, Karol, I would say that logically
speaking they may be thought of as different. Though on this
computer, running an old 2.5 Firebird version, I'm not capable of
making two such statements differ in results and a very
superficial internet search indicates that EXISTS always return
true or false.
By logically speaking, I mean in the
case of <null>. Comparing an unknown value to anything
basically is unknown. And not unknown is still unknown, hence I
wouldn't be surprised if case 1 may not return values if it is
unknown if things exist or not. In case 2, you explicitly return 0
if unknown and hence more records could be returned.
As I said, I'm not capable of forcing
such a difference in an old Fb 2.5 version, but if you're on a
different version, then maybe Firebird doesn't always return true
or false when using EXISTS, but rather true, false or unknown?
So, do the 211 rows returned only in
case 2 have null in any of the columns compared in the exists?
Set
Den 09.08.2019 11:04, skrev
liviuslivius liviuslivius@... [firebird-support]:
HiI have the query where this make a difference and i do not know why...Is there any logical difference between this two statements? Or some sql feature difference?1. -------------------------NOT(EXISTS()OREXISTS())2. -------------------------CASE WHEN EXISTS() THEN 1 ELSE 0 END+CASE WHEN EXISTS() THEN 1 ELSE 0 END= 0----------------------------------------the query plans are exactly the same.First query return nothing, second one return 211 records.regardsKarol BeieniaszewskiPS. I resend my email as previous from Thu, 08 Aug 2019 15:00:25 +0200 is still not visible on the group