Re: [sqlite] Strange comparison with CAST behavior

2013-12-05 Thread Luís Simão 
I was not aware of such!!! Thanks!
LS

2013/12/5 Hick Gunter 

> 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 p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000  NULL
> 1 Goto   0 8 000  NULL
> 2 Integer123   2 000  NULL
> 3 ToInt  2 0 000  NULL
> 4 String80 3 0 12300  NULL
> 5 Eq 3 1 274  NULL <== integer
> affinity, store result
> 6 ResultRow  1 1 000  NULL
> 7 Halt   0 0 000  NULL
> 8 Goto   0 2 000  NULL
>
>
> explain SELECT 123='123';
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000  NULL
> 1 Goto   0 7 000  NULL
> 2 Integer123   2 000  NULL
> 3 String80 3 0 12300  NULL
> 4 Eq 3 1 272  NULL <== "none"
> affinity, store result
> 5 ResultRow  1 1 000  NULL
> 6 Halt   0 0 000  NULL
> 7 Goto   0 2 000  NULL
>
> explain select cast(123 as text)=123;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000  NULL
> 1 Goto   0 8 000  NULL
> 2 Integer123   2 000  NULL
> 3 ToText 2 0 000  NULL
> 4 Integer123   3 000  NULL
> 5 Eq 3 1 271  NULL <== text
> affinity, store result
> 6 ResultRow  1 1 000  NULL
> 7 Halt   0 0 000  NULL
> 8 Goto   0 2 000  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

Re: [sqlite] Strange comparison with CAST behavior

2013-12-05 Thread Hick Gunter 
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 p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Goto   0 8 000  NULL
2 Integer123   2 000  NULL
3 ToInt  2 0 000  NULL
4 String80 3 0 12300  NULL
5 Eq 3 1 274  NULL <== integer 
affinity, store result
6 ResultRow  1 1 000  NULL
7 Halt   0 0 000  NULL
8 Goto   0 2 000  NULL


explain SELECT 123='123';
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Goto   0 7 000  NULL
2 Integer123   2 000  NULL
3 String80 3 0 12300  NULL
4 Eq 3 1 272  NULL <== "none" 
affinity, store result
5 ResultRow  1 1 000  NULL
6 Halt   0 0 000  NULL
7 Goto   0 2 000  NULL

explain select cast(123 as text)=123;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Goto   0 8 000  NULL
2 Integer123   2 000  NULL
3 ToText 2 0 000  NULL
4 Integer123   3 000  NULL
5 Eq 3 1 271  NULL <== text 
affinity, store result
6 ResultRow  1 1 000  NULL
7 Halt   0 0 000  NULL
8 Goto   0 2 000  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

Re: [sqlite] Strange comparison with CAST behavior

2013-12-05 Thread Clemens Ladisch 
Luís Simão wrote:
> SELECT 123='123'; // 0
> SELECT CAST(123 AS NUMERIC)='123'; // 1 ???
>
> How is this possible?

Plain 123 or '123' has affinity NONE.

CAST(123 AS NUMERIC) has affinity NUMERIC, so the string gets
automatically converted for the comparison.

See .


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange comparison with CAST behavior

2013-12-05 Thread Luís Simão 
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