Actually, the affinity is applied first, however --
c0 has affinity "A" (blob) applied.
c1 has affinity "E" (real) applied, but has the "tryForInteger" set which
results in an integer value
the GLOB function converts its arguments to text, so when it is presented with
the "integer" c1 different results are obtained than when the REINDEX command
presents c1 as an actual "real" value to the same function ... (the integer 1
is cast to text as '1' and the real 1 is cast to text as '1.0')
select '0' GLOB 1;
0
select '1' GLOB 1;
1
select '0' GLOB 1.0;
0
select '1' GLOB 1.0;
0
select cast(1 as text);
1
select cast(1.0 as text);
1.0
select cast(cast(1 as real) as text);
1.0
select cast(cast('1' as real) as text);
1.0
select cast(cast('1' as numeric) as text);
1
I suppose the "best fix" would be to prohibit the pattern (and the escape
character) from being a numeric storage class by returning an error. This also
applies to the builtin LIKE.
The builtin instr function would do the same and should probably return an
error if either the needle or the haystack are numeric storage class.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Manuel Rigger
>Sent: Tuesday, 30 April, 2019 15:26
>To: SQLite mailing list
>Subject: [sqlite] Unique index that uses GLOB does not detect
>duplicate due to REAL conversion
>
>Hi everyone,
>
>It think that I found a bug where I could circumvent a UNIQUE check
>of an
>index. Consider the example below, which causes "Error: UNIQUE
>constraint
>failed" when invoking REINDEX:
>
>CREATE TABLE test (c0, c1 REAL);
>CREATE UNIQUE INDEX index_1 ON test(c0 GLOB c1);
>
>INSERT INTO test(c0, c1) VALUES ('1', '1');
>INSERT INTO test(c0, c1) VALUES ('0', '1');
>REINDEX;
>
>The '1's stored to c1 are converted to REAL due to the column's
>affinity,
>and supposedly because SQLite determines that the conversion is
>lossless.
>However, while '1' GLOB '1' would yield true and '0' GLOB '1' would
>yield
>false, which would be valid, after the conversion both GLOB
>expressions
>yield false, violating the UNIQUE property of the index, which is not
>detected until the REINDEX:
>
>sqlite> SELECT *, typeof(c0), typeof(c1), c0 GLOB c1, c0 GLOB '1'
>from test;
>1|1.0|text|real|0|1
>0|1.0|text|real|0|0
>
>Probably, the index check is performed before the conversion?
>
>Best,
>Manuel
>_______________________________________________
>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
