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
