Other thing to point out is that constants/(expressions that aren't coming from a field in a table) have "no affinity", so explicitly typing '25' is a no affinity, not a text affinity, and 25 is no affinity, not integer affinity. (Section 4.2 in the page linked by DRH)
From section 4.3 linked by DRH: -Your first example is covered by bullet point 1 (integer compared to none -> the none becomes and integer) -Your second example is covered by bullet point 3 (none compared to none so no conversion) -Your third example is covered by bullet point 2 (text compared to none -> the none becomes text) (Please correct me if I'm mixed up) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, January 26, 2017 10:50 AM To: SQLite mailing list Subject: Re: [sqlite] text/numeric comparison confusion On 1/26/17, Jeffrey Mattox <j...@me.com> wrote: > When used in a SELECT, I expect this comparison to be true (and it is): > ( cast('25' as INTEGER) = 25 ) <--- true > > But, why is this false: > ( '25' = 25 ) <--- false? > > and this is true: > ( cast(25 as TEXT) = 25 ) <--- true > > So, being that second comparison is false (why?), then why isn't the third > comparison also false? > https://www.sqlite.org/datatype3.html#compaff The third condition applies to your first two examples. The second condition applies on the third example. -- D. Richard Hipp d...@sqlite.org _______________________________________________ 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
