Problem with CASE in WHERE clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Simon Slavin-3

On 4 Dec 2016, at 5:33am, Frank Millman <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 [hidden email] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Simon Slavin-3

On 4 Dec 2016, at 6:55am, Frank Millman <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Jean-Christophe Deschamps-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Simon Slavin-3
In reply to this post by Frank Millman

On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Frank Millman
In reply to this post by 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Simon Slavin-3

On 5 Dec 2016, at 11:18am, Frank Millman <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Jean-Christophe Deschamps-3
In reply to this post by Frank Millman
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Jean-Christophe Deschamps-3
In reply to this post by Simon Slavin-3
Hi Simon,

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

>On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Olivier Mascia
In reply to this post by Simon Slavin-3
> Le 5 déc. 2016 à 12:36, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Simon Slavin-3

On 6 Dec 2016, at 3:36pm, David Raymond <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with CASE in WHERE clause

Hick Gunter
In reply to this post by David Raymond


>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(<value> AS <type>).

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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users