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