I was not aware of such!!! Thanks! LS 2013/12/5 Hick Gunter <h...@scigames.at>
> CAST supplies an affinity to the operand, which forces the comparison to > be made with affinity. And since the constants transform into each other, > the comparison succeeds. > > explain SELECT CAST(123 AS INT)='123'; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Trace 0 0 0 00 NULL > 1 Goto 0 8 0 00 NULL > 2 Integer 123 2 0 00 NULL > 3 ToInt 2 0 0 00 NULL > 4 String8 0 3 0 123 00 NULL > 5 Eq 3 1 2 74 NULL <== integer > affinity, store result > 6 ResultRow 1 1 0 00 NULL > 7 Halt 0 0 0 00 NULL > 8 Goto 0 2 0 00 NULL > > > explain SELECT 123='123'; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Trace 0 0 0 00 NULL > 1 Goto 0 7 0 00 NULL > 2 Integer 123 2 0 00 NULL > 3 String8 0 3 0 123 00 NULL > 4 Eq 3 1 2 72 NULL <== "none" > affinity, store result > 5 ResultRow 1 1 0 00 NULL > 6 Halt 0 0 0 00 NULL > 7 Goto 0 2 0 00 NULL > > explain select cast(123 as text)=123; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Trace 0 0 0 00 NULL > 1 Goto 0 8 0 00 NULL > 2 Integer 123 2 0 00 NULL > 3 ToText 2 0 0 00 NULL > 4 Integer 123 3 0 00 NULL > 5 Eq 3 1 2 71 NULL <== text > affinity, store result > 6 ResultRow 1 1 0 00 NULL > 7 Halt 0 0 0 00 NULL > 8 Goto 0 2 0 00 NULL > > -----Ursprüngliche Nachricht----- > Von: Luís Simão [mailto:l...@portugalmail.com] > Gesendet: Donnerstag, 05. Dezember 2013 12:34 > An: General Discussion of SQLite Database > Betreff: [sqlite] Strange comparison with CAST behavior > > I found this strange behavior in SQLite: > > While testing whether SQLite performs cast before comparison or just > return 0, I entered following: > > SELECT CAST(123 AS INT)='123'; > > And for my surprise, it returned 1!!! > > However, my previous tests checked that numeric/text comparisons always > returned 0. > Check: > > SELECT 123=123.0; // 1 > SELECT 123='123'; // 0 > SELECT CAST(123 AS TEXT)='123'; // 1 > SELECT CAST(123 AS NUMERIC)='123'; // 1 ??? > > How is this possible? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -------------------------------------------------------------------------- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If > you have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then delete > this message from your system. Please do not copy it or use it for any > purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
