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
