Re: [sqlite] Problem with CASE in WHERE clause

[Hick Gunter]

>1) SELECT * FROM TEST WHERE posted = 1;
>[(‘inv’, 100, 1)]
>
>2) SELECT * FROM TEST WHERE posted = ‘1’;
>[(‘inv’, 100, 1)]
>
>3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
>[(‘inv’, 100, 1)]
>
>4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
>[]
>
>
>
>I am a little confused though why bullet point 2 doesn't get applied then, and 
>the 1 from the case expression isn't treated as text.

The constant '1' (of storage class TEXT) has no affinity, neither has the 
constant 1 (of storage class INTEGER), nor the result of an expression - with 
documented exceptions, notably CAST( AS ).

Consider:

1 = '1'
---
0

(1=1) = '1'
---
0

cast((1=1) as integer) = '1'

1

cast((1=1) as text) = '1'
-
1

(1=1) = cast('1' as integer)

1
 (1=1) = cast('1' as text)
-
1

The first and last CAST() examples are particularly interesting. The CAST 
doesn't change it's operand's storage class (argument is already in the target 
storage class); it only sets an affinity, so the subsequent comparison is 
prompted to convert the result on the *other* side of the equality operator.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 6 Dec 2016, at 3:36pm, David Raymond  wrote:

> It seems that the CASE expression results in NO AFFINITY as an expression, 
> and does not carry the affinity of "posted" even though it is a direct 
> reference to the field. Therefore the first bullet point in section 4.3 does 
> NOT get applied, and the text '1' does NOT get converted to an integer for 
> the comparison.

Right.

> I am a little confused though why bullet point 2 doesn't get applied then, 
> and the 1 from the case expression isn't treated as text.

Judging by what you’re seeing, the affinity of a column value is used in direct 
comparisons (=, <, etc.) as documented, but is not preserved in the result of a 
CASE statement.  It would be possible to check this by reading the source code, 
but your sample code is a pretty good demonstration of the behaviour.

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

Re: [sqlite] Problem with CASE in WHERE clause

[David Raymond]

http://www.sqlite.org/datatype3.html section 3+
When you declare a field as type "bool" it gets assigned NUMERIC type affinity.
"A column with NUMERIC affinity may contain values using all five storage 
classes. When text data is inserted into a NUMERIC column, the storage class of 
the text is converted to INTEGER or REAL (in order of preference) if such 
conversion is lossless and reversible. For conversions between TEXT and REAL 
storage classes, SQLite considers the conversion to be lossless and reversible 
if the first 15 significant decimal digits of the number are preserved. If the 
lossless conversion of TEXT to INTEGER or REAL is not possible then the value 
is stored using the TEXT storage class. No attempt is made to convert NULL or 
BLOB values."

So with a field declared as "bool" then the text value of '1' gets inserted, it 
is stored as the integer 1

1) SELECT * FROM TEST WHERE posted = 1;
[(‘inv’, 100, 1)]

2) SELECT * FROM TEST WHERE posted = ‘1’;
[(‘inv’, 100, 1)]

3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
[(‘inv’, 100, 1)]

4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
[]

http://www.sqlite.org/datatype3.html section 4.2, 4.3, etc
In your case 2, "posted" refers directly to the NUMERIC field and thus 
maintains NUMERIC affinity, so the text '1' gets converted losslessly to the 
integer 1 and so the result of the comparison is true.

It seems that the CASE expression results in NO AFFINITY as an expression, and 
does not carry the affinity of "posted" even though it is a direct reference to 
the field. Therefore the first bullet point in section 4.3 does NOT get 
applied, and the text '1' does NOT get converted to an integer for the 
comparison.

I am a little confused though why bullet point 2 doesn't get applied then, and 
the 1 from the case expression isn't treated as text.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Olivier Mascia]

> Le 5 déc. 2016 à 12:36, Simon Slavin  a écrit :
> 
> And defining a column with a type of INTEGER is as close to BOOL as SQLite 
> gets.

It is even better when you take into account that SQLite internally has a 
storage type for 'integer value 0' and 'integer value 1' (types 8 and 9). For 
these, there is not even a 'value' stored, merely the type (this is true with 
SQLite > 3.3.0).

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



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

Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

Hi Simon,

At 11:41 05/12/2016, you wrote:

On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps  
wrote:


> The choice of literals representing true and false is merely cosmetic.

You got me interested in that.  I had thought that "TRUE" and "FALSE" 
were reserved words in SQLite.  But I can find nothing to back that up, and


SELECT TRUE

returns an error.  It’s too late to add them now, of course, for 
backward compatibility reasons.  Someone may have a table column 
called "false".


Simon.


I'm as surprised as you about this, but it isn't the point I wanted to 
make.
BTW SQLite generally does a pretty good job at sorting out reserved 
words used as keywords vs. keywords used as schema names, but I always 
recommend that double quotes surround reserved names used as schema names.


I meant that we could call the truth of a boolean expression 
'STAINLESS' or 'RASPBERRY' instead of True and False, or 1 and 0. The 
symbols or literals we use for expressing a boolean value is just a 
convention. I wasn't talking especially about SQLite nor SQL (nor any 
language).


Look at the various incompatible conventions for expressing boolean 
values as "boolean-codepage nightmare" in that it reproduces, in the 
{false, true} domain, exactly the same issues codepages have created in 
character sets.


JcD 


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

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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 5 Dec 2016, at 11:18am, Frank Millman  wrote:

> 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.

Rather than the strings it would be better to use the integers 0 and 1.  This 
is how SQLite handles boolean values internally.  And they take less storage 
space and are faster to handle.

Although other values (e.g. 1.0 or the strings '1' or '1.0') may evaluate to 0 
or 1 under some circumstances, this relies on context, affinities, and other 
accidents of syntax.  But the integers always test as FALSE and TRUE.  And 
defining a column with a type of INTEGER is as close to BOOL as SQLite gets.

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

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

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’/’0’ as valid values.
> >
> >PostgreSQL has a ‘bool’ data type, which supports a variety of 
> >values ­ TRUE, ‘t’, ‘true’, ‘yy’, ‘yes’, ‘on’, 
> >‘1’ for true, and the 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


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

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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps  wrote:

> The choice of literals representing true and false is merely cosmetic.

You got me interested in that.  I had thought that "TRUE" and "FALSE" were 
reserved words in SQLite.  But I can find nothing to back that up, and

SELECT TRUE

returns an error.  It’s too late to add them now, of course, for backward 
compatibility reasons.  Someone may have a table column called "false".

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

Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

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’/’0’ as valid values.


PostgreSQL has a ‘bool’ data type, which supports a variety of 
values ­ TRUE, ‘t’, ‘true’, ‘yy’, ‘yes’, ‘on’, 
‘1’ for true, and the 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.

JcD 


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

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Don V Nielsen 
Sent: Sunday, December 04, 2016 5:15 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

> Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.


Sorry about that. Maybe I over-simplified my example. In practice the case 
statement will never follow the ‘else’ clause, but it will select from a number 
of similar ‘then’ clauses.

Simon has given me enough info to come up with a solution. However, I am still 
a bit surprised at the result.

1) SELECT * FROM TEST WHERE posted = 1;
[(‘inv’, 100, 1)]

2) SELECT * FROM TEST WHERE posted = ‘1’;
[(‘inv’, 100, 1)]

3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
[(‘inv’, 100, 1)]

4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
[]

In 3) and 4), the WHEN clause evaluates to true, so I expected it to treat 1 
and ‘1’ identically, the same way as it does in 1) and 2).

Not important, just curious.

Frank

P.S. Here is the reason for assigning ‘1’ instead of 1.

My app supports sqlite3, SQL Server, and PostgreSQL.

SQL Server has a ‘bit’ data type, which accepts 1/0 and ‘1’/’0’ as valid values.

PostgreSQL has a ‘bool’ data type, which supports a variety of values – TRUE, 
‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’ for true, and the opposites for false, but 
does not allow 1/0.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Don V Nielsen]

Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.

My recommendation is this. Make [posted] a varchar(1) with only two valid
values: 'y' and 'n'. Then rewrite your logic to work with 'y' and 'n' and
see if that works across every database. It is much more explicit, cleaner,
and does not rely on any underlying interpretations.

Just my opinion. Merry Christmas.
dvn

On Sun, Dec 4, 2016 at 2:46 AM, Frank Millman <fr...@chagford.com> wrote:

>
> From: Simon Slavin
> Sent: Sunday, December 04, 2016 10:26 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Problem with CASE in WHERE clause
>
>
> > On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote:
> >
> > > If a column contains a ‘1’, I would expect sqlite3 to return true when
> testing for ‘1’, but in my example it returns false.
> >
> > I think I’ve found the problem ...
> >
>
> Thank you very much for your explanation, Simon.
>
> My live situation is a bit more complex than my example, so I will have to
> experiment to find the ideal solution.
>
> But you have given me the information I need to move forward – much
> appreciated.
>
> Frank
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Simon Slavin 
Sent: Sunday, December 04, 2016 10:26 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause


> On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote:
> 
> > If a column contains a ‘1’, I would expect sqlite3 to return true when 
> > testing for ‘1’, but in my example it returns false.
> 
> I think I’ve found the problem ...
> 

Thank you very much for your explanation, Simon.

My live situation is a bit more complex than my example, so I will have to 
experiment to find the ideal solution.

But you have given me the information I need to move forward – much appreciated.

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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 4 Dec 2016, at 6:55am, Frank Millman  wrote:

> If a column contains a ‘1’, I would expect sqlite3 to return true when 
> testing for ‘1’, but in my example it returns false.

I think I’ve found the problem.  Here is your syntax:

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = '1'

SQLite does not have a BOOL type.  Instead it uses the integers 0 and 1 to 
indicate boolean values.  So it interprets your "posted" column as wanting to 
store integers. So this command returns …

SELECT posted,typeof(posted),posted='1' FROM test_1; <— [1, integer, 1]

So the result of your CASE expression can be 1, but not '1'.  And in SQLite …

SELECT 1='1'; <— [0]

You might like to use the following syntax instead:

SELECT * FROM test_1 WHERE tran_type = 'inv' AND posted;

This should work in both SQLite and PostgreSQL, and be extremely fast if you 
have an index on (tran_type,posted).

If you absolutely must let the SELECT stay as it is, declare the "posted" 
column as TEXT, not BOOL.  Then your original SELECT statement should work as 
intended.

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

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Simon Slavin 
Sent: Sunday, December 04, 2016 8:42 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause


On 4 Dec 2016, at 8:42am, Simon Slavin slav...@bigfraud.org wrote:

> What are we allowed to change ?  Are you asking us to find a "SELECT" command 
> which works both in SQLite and PostgreSQL ?  Can we change the table 
> definition too ?


Er, sorry, I was not asking you to change anything.

If a column contains a ‘1’, I would expect sqlite3 to return true when testing 
for ‘1’, but in my example it returns false.

If this is a bug, then I am happy to wait for a fix.

If it is just the way sqlite3 works, a bit of an explanation would help, as 
that could assist me in finding a workaround.

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

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 4 Dec 2016, at 5:33am, Frank Millman  wrote:

> I really need this to work

What are we allowed to change ?  Are you asking us to find a "SELECT" command 
which works both in SQLite and PostgreSQL ?  Can we change the table definition 
too ?

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

[sqlite] Problem with CASE in WHERE clause

[Frank Millman]

Hi all

I have a problem using a CASE statement in a WHERE clause.

On testing a column which contains a ‘1’, WHERE returns true if I test for 1 
(without the quotes), but false if I test for ‘1’ (with quotes).

CREATE TABLE test_1 (tran_type TEXT, amount INTEGER, posted BOOL)

INSERT INTO test_1 (tran_type, amount, posted) values ('inv', 100.00, '1')

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = 1
[(‘inv’, 100, 1)]

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = ‘1’
[]

I really need this to work, as my app is cross-platform and cross-database, and 
PostgreSQL does not accept an integer for a BOOL column.

I am using version 3.14.2 on Windows 10.

Any suggestions?

Thanks

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