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