Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

At 12:18 05/12/2016, you wrote:

From: Jean-Christophe Deschamps
Sent: Monday, December 05, 2016 9:48 AM
To: SQLite mailing list
Subject: Re: [sqlite] Problem with CASE in WHERE clause
>
> At 06:29 05/12/2016, you wrote:
> >My app supports sqlite3, SQL Server, and PostgreSQL.
> >
> >SQL Server has a ‘bitâ’ data type, which accepts 1/0 and
> >‘1â€Ëœ1’/’0’ as valid values.
> >
> >PostgreSQL stgreSQL has a ‘bool’ data type, which supports a 
var a variety of
> >values ­ TRUE, ‘t’, ‘true€˜true’, ‘yy’, 
‘yes’, ‘oes’, ‘on’,
> >‘1’ for true, and the opposithe opposites for false, but 
does not allow 1/0.
>
> All [three] engines should support (1=1) and (1=0) for true and false,
> respectively, as well as bare columnname as a boolean assertion, like
> Simon said: select ... where columnC and not columnF ...
>
> The choice of literals representing true and false is merely cosmetic.
>

So if I understand correctly, it makes sense to use ‘1’/’0’ to 
*set* the boolean value in a cross-database manner, but there are a 
variety of ways to test for it.
Frank

That's not how I see that. I'm no expert in SQL standards (note the 
plural!) but AFAICT the only sure and guaranteed portable way to SET a 
boolean value in SQL --regardless of whether a particular engine offers 
a BOOLEAN datatype and the values it consider valid to represent the 
logic valuations of what we call True and False-- is the result of a 
known true or known false expression, like (3=3) and (2=5).

As you've found, some engines accept something in
'TRUE', 'True', 'true', 't', 'T', 'Y, 'y', '1', 1, TRUE, True, true, ...
'FALSE', 'False', 'false', 'f', 'F', 'N', 'n', '0', 0, FALSE, False, 
false, ...

It may make sense to use '1' and '0' in your precise use case but I'm 
unsure of the portability. An expression yielding a known boolean 
result is forcibly valid and correctly interpreted.

And yes,
select ... where columnname = (1=1)
is mouthful for
select ... where columnname

I don't believe any engine would interpret the last statement as
select ... where columnname is not null

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users