On May 2, 2014, at 10:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> >> On May 2, 2014, at 8:54 PM, Richard Hipp <d...@sqlite.org> wrote: > >>> I'm guessing that Mr. Abeille is upset that SQLite ... > >> ... doesn't even bother with SQL syntax and will happily accept any old >> junk as a sorry excuse for a query. > >> select 1 where null; >> select 1 where not null; > > What is the problem? > > The logical value of a NULL is false. > Any operation on a NULL (including NOT) is a NULL and therefore false. ** > special rules for AND and OR The logical value of NULL is NOT False.. it is “NULL.” NULL does not equal False, just as it does not equal True. SQL employs what is called 3VL, or “three valued logic.” It is a logic system that consists of three values… True, False, and NULL. http://en.wikipedia.org/wiki/Three-valued_logic There are not “special rules” for AND and OR, there are just the core rules for 3VL, which considers NULL to be an unknown. There are times when SQL needs a definite yes/no answer. however. In those cases, SQL looks to see if the value is equal to True. For example: sqlite> select 1 where NULL; sqlite> In such cases, it is not that NULL is equal to False (which isn’t True), it is that NULL is not equal to True… just as False is not equal to True. > A numeric value of 0 is false. > Any value that is not 0 is true. > > Strings are, however, handled incorrectly: > sqlite> select 1 where 'A'; > sqlite> select 1 where not 'A'; > 1 > > ^^^ are inverted 'A' should be true, whereas not 'A' should be false; and, For logic operations, strings are converted into numbers. ‘A’ doesn’t translate to a number, so it become 0, or false. Consider: sqlite> select 1 where '1'; 1 sqlite> select 1 where not '1'; sqlite> select CAST( 'A' as integer ); 0 sqlite> select CAST( '' as integer ); 0 sqlite> select CAST( '1' as integer ); 1 > sqlite> select 1 where ''; > sqlite> select 1 where not ''; > 1 > > an empty string should be false, and applying the not operator should result > in true Which it is how it works… and empty string is converted to zero, which is false. A "not false" is true. …just as your examples show. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
