Re: [sqlite] Unique index that uses GLOB does not detect duplicate due to REAL conversion
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
[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] Minor Source Code Comment Typo in date.c lines 104 & 105 (No code change)
In date.c at line 83: /* ** Convert zDate into one or more integers according to the conversion ** specifier zFormat. ** ** zFormat[] contains 4 characters for each integer converted, except for ** the last integer which is specified by three characters. The meaning ** of a four-character format specifiers ABCD is: ** **A: number of digits to convert. Always "2" or "4". **B: minimum value. Always "0" or "1". **C: maximum value, decoded as: ** a: 12 ** b: 14 ** c: 24 ** d: 31 ** e: 59 ** f: **D: the separator character, or \000 to indicate this is the ** last number to convert. ** ** Example: To translate an ISO-8601 date -MM-DD, the format would ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates ** the 2-digit day which is the last integer in the set. ** ** The function returns the number of successful conversions. */ on lines 104 and 105 the "20c" should be "21d", that is: ** Example: To translate an ISO-8601 date -MM-DD, the format would ** be "40f-21a-21d". The "40f-" indicates the 4-digit year followed by "-". ** The "21a-" indicates the 2-digit month followed by "-". The "21d" indicates ** the 2-digit day which is the last integer in the set. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Malformed database schema when using VACUUM
Hi everyone, the following sequence of SQL statements results in "Error: malformed database schema (index_0) - non-deterministic functions prohibited in index expressions". CREATE TABLE test (c0); CREATE INDEX index_0 ON test(c0 LIKE ''); PRAGMA case_sensitive_like=false; VACUUM; SELECT * from test; This error relates to the statement sequence in my previous email "PRAGMA case_sensitive_like conflicts with LIKE operator when creating an index", in that both statement sequences set "case_sensitive_like" and use "like" in an index. However, in this case, the issue seems to be detected only in a query subsequent to the VACUUM statement that seems to corrupt the schema. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA case_sensitive_like conflicts with LIKE operator when creating an index
Okay, thanks! Best, Manuel Am So., 28. Apr. 2019 um 21:02 Uhr schrieb Simon Slavin < slav...@bigfraud.org>: > On 28 Apr 2019, at 7:58pm, Manuel Rigger wrote: > > > It seems that setting "PRAGMA case_sensitive_like" to either false (the > default behavior) or true results in no longer being able to use a LIKE or > GLOB clause when creating an index. > > Correct. Because you cannot depend that every connection that opens the > database has PRAGMA case_sensitive_like set the same way. And that might > lead to an inconsistent index. > ___ > 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
Re: [sqlite] Making blob as a sqlite database.
Sorry to bother, Mohd. What is your use case? I mentioned this to developers around me and they are intrigued. You are storing a database file as blob in a database? We are curious as to the application. I am assuming the database being stored is a collection of sensor or event data? On Fri, Apr 26, 2019 at 5:48 PM Mohd Radzi Ibrahim wrote: > Hi, is there a vfs that could be used to open a blob column as a database? > > > thanks. > > Radzi. > ___ > 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
Re: [sqlite] [EXTERNAL] Re: Does SQLITE ever optimize index creation based on another index?
AFAIK it is considered good practice to group fields used in indices at the
beginning of the table definition (because they tend to get referenced most)
and BLOB fields at the end (because acessing fields behind a BLOB - which is
"large" by definition - tends to take more effort).
So the optimization would not benefit a "properly designed" schema anyway.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von R Smith
Gesendet: Dienstag, 30. April 2019 13:54
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Does SQLITE ever optimize index creation based
on another index?
On 2019/04/30 2:10 AM, Deon Brewis wrote:
> Given the SQL below, FooX is a covered index for x on Foo.
>
> I want to create FooXB as a second index on x in Foo. Since 'x' is covered on
> FooX it should be cheaper to build FooXB from index FooX, than from table
> Foo. However, as far as I can tell from the from the opcodes of the index
> creation it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my
> understanding correct?
Not quite. This is a good example of something that "feels" like it should be
better, just isn't.
Unless Foo(x) is a partial Index and the new index can somehow indicate that it
has the same partiality as the original index (which it can't unless it's
exactly equal, in which case, it's useless), there can be no advantage.
Keep in mind that, in SQLite, a table is nothing less than a covering Index
itself with the row_id as the indexer (or the actual PK in the case of WITHOUT
ROWID tables). There is no reason why it itself (being an Index) should be any
slower to "walk" than any other Index, in fact a lookup via any other index
will include an extra step (the lookup
itself) that you don't have when walking the table index itself (aka doing a
"table scan"). It's just better for anything where you access any field that
are not in the existing index, and not worse for those that are.
There might be a small but real advantage if the field (that was indexed
on) appeared at the end of very long list of fields or very large fields, i.e.
hidden at the back end of the column list with really large
(long-to-read) columns preceding it - meaning the existing Index would already
have singled out that bit of data - but it's a very small likelihood and
use-case though. (Meaning that it's unlikely for people to make multiple
Indexes on the same field(s), so investing the effort and code-bloat in
catering for the optimization it, which would only ever benefit it in the case
where the column IS at the back of big other columns, would be of dubious
benefit).
> And if my understanding is correct, is there any scenarios in which I can
> coerce SQLITE to build a new index based on data in an existing index?
>
>
> drop table Foo;
> create table Foo(x text, y text, z text);
>
> insert into Foo(x) values("elephant"); insert into Foo(x)
> values("cat"); insert into Foo(x) values("giraffe"); insert into
> Foo(x) values("dog"); insert into Foo(x) values("zebra"); insert into
> Foo(x) values("lion"); insert into Foo(x) values("panther");
>
> create index FooX on Foo(x);
> create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
As an aside - if your INSERTs above was a "find the odd one out" puzzle, I vote
that the answer would be "panther". :)
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLITE ever optimize index creation based on another index?
On 2019/04/30 2:10 AM, Deon Brewis wrote:
Given the SQL below, FooX is a covered index for x on Foo.
I want to create FooXB as a second index on x in Foo. Since 'x' is covered on
FooX it should be cheaper to build FooXB from index FooX, than from table Foo.
However, as far as I can tell from the from the opcodes of the index creation
it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my understanding
correct?
Not quite. This is a good example of something that "feels" like it
should be better, just isn't.
Unless Foo(x) is a partial Index and the new index can somehow indicate
that it has the same partiality as the original index (which it can't
unless it's exactly equal, in which case, it's useless), there can be no
advantage.
Keep in mind that, in SQLite, a table is nothing less than a covering
Index itself with the row_id as the indexer (or the actual PK in the
case of WITHOUT ROWID tables). There is no reason why it itself (being
an Index) should be any slower to "walk" than any other Index, in fact a
lookup via any other index will include an extra step (the lookup
itself) that you don't have when walking the table index itself (aka
doing a "table scan"). It's just better for anything where you access
any field that are not in the existing index, and not worse for those
that are.
There might be a small but real advantage if the field (that was indexed
on) appeared at the end of very long list of fields or very large
fields, i.e. hidden at the back end of the column list with really large
(long-to-read) columns preceding it - meaning the existing Index would
already have singled out that bit of data - but it's a very small
likelihood and use-case though. (Meaning that it's unlikely for people
to make multiple Indexes on the same field(s), so investing the effort
and code-bloat in catering for the optimization it, which would only
ever benefit it in the case where the column IS at the back of big other
columns, would be of dubious benefit).
And if my understanding is correct, is there any scenarios in which I can
coerce SQLITE to build a new index based on data in an existing index?
drop table Foo;
create table Foo(x text, y text, z text);
insert into Foo(x) values("elephant");
insert into Foo(x) values("cat");
insert into Foo(x) values("giraffe");
insert into Foo(x) values("dog");
insert into Foo(x) values("zebra");
insert into Foo(x) values("lion");
insert into Foo(x) values("panther");
create index FooX on Foo(x);
create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
As an aside - if your INSERTs above was a "find the odd one out" puzzle,
I vote that the answer would be "panther". :)
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does SQLITE ever optimize index creation based on another index?
Given the SQL below, FooX is a covered index for x on Foo.
I want to create FooXB as a second index on x in Foo. Since 'x' is covered on
FooX it should be cheaper to build FooXB from index FooX, than from table Foo.
However, as far as I can tell from the from the opcodes of the index creation
it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my understanding
correct?
And if my understanding is correct, is there any scenarios in which I can
coerce SQLITE to build a new index based on data in an existing index?
drop table Foo;
create table Foo(x text, y text, z text);
insert into Foo(x) values("elephant");
insert into Foo(x) values("cat");
insert into Foo(x) values("giraffe");
insert into Foo(x) values("dog");
insert into Foo(x) values("zebra");
insert into Foo(x) values("lion");
insert into Foo(x) values("panther");
create index FooX on Foo(x);
create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
select * from sqlite_master;
RecNo type name tbl_name rootpage sql
- -
1 table Foo Foo 2 CREATE TABLE Foo(x text, y text, z text)
2 index FooX Foo 3 CREATE INDEX FooX on Foo(x)
explain create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
RecNo addr opcode p1 p2 p3 p4
p5 comment
- -- -- --
--
---
1 0Init 0 37 0
00 (null)
2 1Noop 0 36 0
00 (null)
3 2CreateBtree 0 1 2
00 (null)
4 3OpenWrite0 1 0 5
00 (null)
5 4NewRowid 0 2 0
00 (null)
6 5String8 0 3 0 index
00 (null)
7 6String8 0 4 0 FooXB
00 (null)
8 7String8 0 5 0 Foo
00 (null)
9 8Copy 1 6 0
00 (null)
10 9String8 0 7 0 CREATE INDEX FooXB on Foo(substr(x,2,1)) where
substr(x,2,1) > 'e' (more...) 00 (null)
11 10 MakeRecord 3 5 8 BBBDB
00 (null)
12 11 Insert 0 8 2
18 (null)
13 12 SorterOpen 3 0 1 k(2,,)
00 (null)
14 13 OpenRead 1 2 0 3
00 (null)
15 14 Rewind 1 24 0
00 (null)
16 15 Column 1 0 11
00 (null)
17 16 PureFunc06 11 10 substr(3)
03 (null)
18 17 Le 14 23 10
51 (null)
19 18 Column 1 0 17
00 (null)
20 19 PureFunc06 17 15 substr(3)
03 (null)
21 20 Rowid1 16 0
00 (null)
22 21 MakeRecord 15 2 9
00 (null)
23 22 SorterInsert 3 9 0
00 (null)
24 23 Next 1 15 0
00 (null)
25 24 OpenWrite2 1 0 k(2,,)
11 (null)
26 25 SorterSort 3 30 0
00 (null)
27 26 SorterData 3 9 2
00 (null)
28 27 SeekEnd 2 0 0
00 (null)
29 28 IdxInsert2 9 0
