Re: [sqlite] New SQL functions available for testing only.
Thanks for adding these functions! My test generator/fuzzer is written in Java and uses the JDBC interface. In order to call the C function, I would probably need to modify the driver code or use JNI, which would both be a bit hacky. Is there a compile-time option that can be set to provide internal functions by default? Best, Manuel On Wed, Jan 1, 2020 at 7:26 PM Jens Alfke wrote: > > > On Jan 1, 2020, at 8:03 AM, Richard Hipp wrote: > > > > expr_implies_expr(A,B) > > > > Return non-zero if expression A implies expression B. See > > https://www.sqlite.org/src/artifact/971e442fd5?ln=5277-5299 for > > details. This routine is used to determine if a partial index is > > usable. > > Thanks! This is also likely useful for programs that create their own > synthetic indexes out of secondary tables. (I do this, partly because I > need to index multiple values per row, e.g. indexing the elements of a JSON > array.) If such an ‘index’ is partial, I need to figure out when it’s > applicable to a query. > > —Jens > ___ > 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] 18 minutes 41 seconds
Thanks for all your great work, Richard and Dan! Among all DBMS that we have been testing, we have put most of our effort and energy into testing SQLite. The reason for that is that you were by far the most responsive to our bug reports, and typically address bugs immediately after we find them! It's great that you take all bug reports seriously. In other widely-used DBMS that we have been testing, bugs take weeks, months, or longer until getting fixed. Looking forward to another fruitful year of cooperating in making SQLite even more robust! Best, Manuel On Tue, Dec 31, 2019 at 7:26 AM Michael Falconer < michael.j.falco...@gmail.com> wrote: > > > > There is no "year 0" between 1 BC and 1 AD. This is perhaps the most > > common fencepost problem in existance. The "great renaming" of AD to CE > > and doing away with BC by replacing them with "off by one" numbers less > > than 1 does not change the fact that there was, in fact, no year 0. > > Obviously the character(s) responsible for dates etc were NOT C > programmers! > > On Tue, 31 Dec 2019 at 14:45, Richard Damon > wrote: > > > On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote: > > > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf > > wrote: > > >> > > >> On Monday, 30 December, 2019 19:29, Michael Falconer < > > michael.j.falco...@gmail.com> wrote: > > >> > > >>> As we approach the end of yet another year ( and indeed decade ). > > >> Technically, every year is the end of a decade, if one means the > > immediately preceding ten years. > > >> > > >> However, if you mean the end of the second decade of the 21st century, > > you will have to wait another year for that. January 1st, 0001 AD was > the > > first day of the year 1. The first decade ended at the end of December > > 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic > > Gregorian calendar). > > > Languages don't work like this. > > > > > > https://www.collinsdictionary.com/dictionary/english/decade > > > > > > Cheers > > > > Its a difference between ordinals and numerals. The 20th century was > > from the beginning of 1901 to the end of 2000. We also have the century > > called the 1900's which went from 1900 to the end of 1999. > > > > Decade would work the same way, the 202st decade goes from 2011 to end > > of 2020, but the 2010s go from 2010 to end of 2019. > > > > -- > > Richard Damon > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Regards, > Michael.j.Falconer. > ___ > 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] Assertion Failed In sqlite3
Hi Yongheng and Rui, This might be a duplicate to a bug that I reported, since both test cases trigger the same assertion error. See https://sqlite.org/src/tktview?name=37823501c6. Best, Manuel On Fri, Dec 27, 2019 at 6:09 PM Yongheng Chen wrote: > Hi, > > We found an assertion violation bug in sqlite. Here’s the PoC: > — > CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; INSERT INTO v0 VALUES ( 10 ) > ; > SELECT '29' , count () OVER( ORDER BY v1 ) AS m FROM v0 ORDER BY v1 > ( > SELECT m ) ; > — > > The bug exists in the latest development code and release code. > > Yongheng & Rui > ___ > 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] Determining column collating functions
Hi everyone, Is there a simple way to determine the collating function of a column? PRAGMA table_info does not seem to provide this information. The information could be extracted from sqlite_master, which contains the SQL statements used to create the table or view. While parsing the SQL string is rather straightforward for tables, it would involve more effort to determine the collating functions for views, which can again reference other views or tables. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?
It could be surprising in the example below:
SELECT CAST('-0.0' AS NUMERIC); -- 0.0
SELECT CAST('0.0' AS NUMERIC); -- 0
SELECT CAST('+0.0' AS NUMERIC); -- 0
Best,
Manuel
On Wed, Jun 12, 2019 at 3:57 PM John McKown
wrote:
> On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp wrote:
>
> > IEEE754 floating point numbers have separate representations for +0.0
> > and -0.0. As currently implemented, SQLite always display both
> > quantities as just "0.0".
> >
> > Question: Should SQLite be enhanced to show -0.0 as "-0.0"? Or,
> > would that create unnecessary confusion?
> >
>
> Is there any case where the display makes a difference? I cannot think of
> any case where it is mathematically important. Actually the "0.0" is more
> mathematically correct because zero is neither positive nor negative.
>
> The IBM "mainframe" zSeries processors implement three floating points
> formats: HFP (historic "hexadecimal Floating Point"), BFP (Binary Floating
> Point -- IEEE754) and DFP (Decimal Floating Point -- IEEE754-2008). I am
> not aware of any other architecture which does this.
>
>
>
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
>
> --
> This is clearly another case of too many mad scientists, and not enough
> hunchbacks.
>
>
> Maranatha! <><
> John McKown
> ___
> 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] Affinity conversions and BLOB values
Hi Keith, I don't understand completely. So we agree that +c0 has no affinity. However, you argue that c0 has BLOB affinity, if I understood correctly. Why is that? I'd assume that it has TEXT affinity, since the table column is declared as TEXT. Since applying TEXT affinity seems to be lossless, I would expect it to be performed. Best, Manuel On Sun, Jun 9, 2019 at 3:51 PM Keith Medcalf wrote: > > On Sunday, 9 June, 2019 05:20, Manuel Rigger > wrote: > > >CREATE TABLE t0(c0 TEXT); > >INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT > >SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 > > Note also that the only place where + is different from > by itself generally speaking is in an ORDER BY clause. This is because > although the "value" is unchanged, + is an expression whereas > is a reference to a column. Thus the optimizer can use the bare > reference to a column () during index selection but will not use an > expression (+). > > --- > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Affinity conversions and BLOB values
Hi everyone, Consider the following example: CREATE TABLE t0(c0 TEXT); INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 I would expect that a row with a value 0 is returned. I suspect that this is a misunderstanding on my side, rather than a bug, which is why I didn't directly open a bug report. Here is my reasoning: The storage class of c0 is TEXT, and since x'41' is a BLOB value, the value is not converted to TEXT, since BLOB values are never converted when storing them. (+ c0) has no type affinity, while c0 has a type affinity of TEXT. The documentation states that if "one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand." Thus, I would expect that TEXT affinity is applied to (+ c0). Now, it is not very clear to me whether an affinity conversion should be performed. I would expect that x'41' is converted to 'A', since a conversion back to the binary value would be possible (i.e., lossless and reversible). In that case, a TEXT value would be compared with a BLOB value, and the documentation states that "A TEXT value is less than a BLOB value", so this expression should yield 0. One explanation for the actual behavior could be that conversions of BLOB values are always considered lossy, which would be somehow counter-intuitive (and not documented). An alternative explanation that I could think of is that if one operand has TEXT affinity, and the other has no affinity, then TEXT affinity is applied to *both* the operands, which would imply that the documentation is incorrect for this case. This btw also applies to NUMERIC storage types and BLOB values. Looking forward to your thoughts/an explanation! Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA reverse_unordered_selects=true results in row not being fetched
Hi Donald, You are right. Sorry for not mentioning that. I was working on trunk and using Linux. The bug has already been fixed: https://www.sqlite.org/src/info/bc7d2c1656396bb4 Best, Manuel On Wed, May 15, 2019 at 3:13 PM Donald Griggs wrote: > On Tue, May 14, 2019 at 10:18 AM Manuel Rigger > wrote: > > > ... > > > > The query does not fetch a row. However, without the PRAGMA statement one > > row is fetched. > > > > It probably helps the devs to specify the version you were testing. > For me, your example returns a single row of "1" in each case. > I'm using 3.28.0, windows CLI: > > C:\Users\c058905\webauto>sqlite3 > > *SQLite version 3.28.0* 2019-04-16 19:49:53 > > Enter ".help" for usage hints. > > Connected to a transient in-memory database. > > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> PRAGMA reverse_unordered_selects; > > 0 > > sqlite> CREATE TABLE t1 (c0, c1); > > sqlite> CREATE TABLE t2 (c0 INT UNIQUE); > > sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL); > > sqlite> INSERT INTO t2(c0) VALUES (1); > > sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM > t1, t2 > >...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC > LIMIT 100); > > 1| > > > C:\Users\c058905\webauto>sqlite3 > SQLite version 3.28.0 2019-04-16 19:49:53 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> PRAGMA reverse_unordered_selects=1; > sqlite> PRAGMA reverse_unordered_selects; > 1 > sqlite> CREATE TABLE t1 (c0, c1); > sqlite> CREATE TABLE t2 (c0 INT UNIQUE); > sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL); > sqlite> INSERT INTO t2(c0) VALUES (1); > sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM > t1, t2 >...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC > LIMIT 100); > 1| > sqlite> > > > Regards, > Donald > ___ > 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] Problem with REAL PRIMARY KEY
ULL; > Init subquery result > 11 Integer1 5 000 r[5]=1; LIMIT > counter > 12 OpenRead 1 2 0 2 00 root=2 iDb=0; > t1 > 13 ColumnsUsed1 0 0 2 00 > 14 Explain140 0 SCAN TABLE t1 (~1048576 rows) 00 > 15 Noop 0 0 000 Begin > WHERE-loop0: t1 > 16 Rewind 1 23000 > 17Noop 0 0 000 Begin > WHERE-core > 18Column 1 1 400 r[4]=t1.c1 > 19RealAffinity 4 0 000 > 20DecrJumpZero 5 24000 if > (--r[5])==0 goto 24 > 21Noop 0 0 000 End > WHERE-core > 22 Next 1 17001 > 23 Noop 0 0 000 End > WHERE-loop0: t1 > 24 Return 3 0 000 > 25 Ne 4 321 (BINARY) 53 if r[1]!=r[4] > goto 32 > 26 Noop 0 0 000 Begin > WHERE-core > 27 Column 0 0 600 r[6]=t1.c0 > 28 Column 0 1 700 r[7]=t1.c1 > 29 RealAffinity 7 0 000 > 30 ResultRow 6 2 000 output=r[6..7] > 31 Noop 0 0 000 End WHERE-core > 32Next 0 6 001 > 33Noop 0 0 000 End WHERE-loop0: > t1 > 34Halt 0 0 000 > 35Transaction0 0 1 0 01 usesStmtJournal=0 > 36Goto 0 1 000 > |5.76460752303424e+17 > sqlite> > > sqlite> SELECT * FROM t1 WHERE c1 == (SELECT c1 FROM t1); > QUERY PLAN > |--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 > rows) > `--SCALAR SUBQUERY 1 >`--SCAN TABLE t1 (~1048576 rows) > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 35000 Start at 35 > 1 OpenRead 2 3 0 k(3,,,)02 root=3 iDb=0; > sqlite_autoindex_t1_1 > 2 ColumnsUsed2 0 0 3 00 > 3 Explain3 0 0 SEARCH TABLE t1 USING COVERING > INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows) 00 > 4 Noop 0 0 000 Begin > WHERE-loop0: t1 > 5 Integer212 000 r[2]=21; return > address > 6 Once 0 21000 > 7 Null 0 3 300 r[3..3]=NULL; > Init subquery result > 8 Integer1 4 000 r[4]=1; LIMIT > counter > 9 OpenRead 1 2 0 2 00 root=2 iDb=0; t1 > 10ColumnsUsed1 0 0 2 00 > 11Explain110 0 SCAN TABLE t1 (~1048576 rows) 00 > 12Noop 0 0 000 Begin > WHERE-loop0: t1 > 13Rewind 1 20000 > 14 Noop 0 0 000 Begin > WHERE-core > 15 Column 1 1 300 r[3]=t1.c1 > 16 RealAffinity 3 0 000 > 17 DecrJumpZero 4 21000 if (--r[4])==0 > goto 21 > 18 Noop 0 0 000 End WHERE-core > 19Next 1 14001 > 20Noop 0 0 000 End WHERE-loop0: > t1 > 21Return 2 0 000 > 22IsNull 3 33000 if r[3]==NULL > goto 33 > 23Affinity 3 1 0 E 00 affinity(r[3]) > 24SeekGE 2 333 1 00 key=r[3] > 25 IdxGT 2 333 1 00 key=r[3] > 26 Noop 0 0 000 Begin > WHERE-core > 27 Column 2 1 500 r[5]=t1.c0 > 28 Column 2 0 600 r[6]=t1.c1 > 29 RealAffinity 6 0 000 > 30 ResultRow 5 2 000 output=r[5..6] > 31 Noop 0 0
[sqlite] PRAGMA reverse_unordered_selects=true results in row not being fetched
Hi everyone, I found a curious bug, which I could reproduce only with a very specific statement sequence: PRAGMA reverse_unordered_selects=true; CREATE TABLE t1 (c0, c1); CREATE TABLE t2 (c0 INT UNIQUE); INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL); INSERT INTO t2(c0) VALUES (1); SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM t1, t2 WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC LIMIT 100); -- no row is fetched The query does not fetch a row. However, without the PRAGMA statement one row is fetched. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Hi Ryan, Okay, thanks for the clarification! Your explanation makes sense! Best, Manuel On Mon, May 13, 2019 at 8:25 PM R Smith wrote: > On 2019/05/13 11:42 AM, Manuel Rigger wrote: > > Hi Ryan, > > > > I hope my question did not offend you. I didn't expect that the answer to > > this question would be considered to be that obvious. > > Goodness, I was not offended and apologies if my reply read in that way > - it was just an honest account - and - if I were to stoop so low as to > "take offense" at a question, I would never answer it publicly. > > Please know that it is obvious to most Database people (er... what is a > good word...? shall we say "Afficionados"?), but that in no way means > that you *should* have known or that I (or most others here) would scoff > at anyone not knowing this. I once did not know this, and now to me it > was nothing but an opportunity to teach/relay that which I have been > taught. > > As to the question, let me see if I could entice your mind to see the > obviousness with us: Try to imagine how you would program a database > engine upon which constraints could be placed, the underlying methods or > values of which may persist in separate files/schemata/tables which may > all be transacted upon from alternate connections while they are not in > view, or not accessible to the engine itself, but the engine is still > expected to uphold the constraints. > > I am hoping that after some thinking on the matter, either the > obviousness would materialize for you, or possibly you will come up with > a method that could change the face of RDBMS capabilities forever! > > I'm hoping for the latter, but will take the former as a second prize. :) > > > Cheers! > > Ryan > > > ___ > 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] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC
Hi everyone, Consider the following test case: CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID; INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); REINDEX; SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; -- returns 5| I would expect that the SELECT statement fetches the five rows, however, only one is fetched. Only when omitting the REINDEX are all five rows fetched. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Hi Ryan, I hope my question did not offend you. I didn't expect that the answer to this question would be considered to be that obvious. Best, Manuel On Mon, May 13, 2019 at 9:57 AM R Smith wrote: > On 2019/05/13 12:56 AM, Manuel Rigger wrote: > > Hi everyone, > > > > It seems that a table created in the temp database cannot have a parent > > table that is created in the main database and vice versa: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 PRIMARY KEY); > > CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFERENCES t0(c0)); > > INSERT INTO t1(c0) VALUES (1); -- no such table: temp.t0 > > > > Is this intended? It somehow seems so, since the parent table can also > not > > be prefixed by "temp." when declaring the foreign key in the above > example. > > However, I did not find a note in the documentation that this is not > > supported. Would it make sense to describe this in the limitations at > > https://www.sqlite.org/foreignkeys.html? > > > While the "why" of it has been handled by others, the question of "need > it be documented" still remains, to which I can comment: Documenting the > fact that FK relationships cannot be maintained across different > database entities, trespasses on two documentation conventions: > > A - Do not document the obvious (i.e. no point documenting the fact that > your car needs all 4 wheels for correct operation)[1], and > B - Do not document the negative (i.e. Say what you CAN do [short list] > rather than what you CAN'T [infinite list]) - unless it is an expected > yet omitted behaviour (such as things that can normally be done in other > databases, but not in this one)[2]. > > > > Cheers, > Ryan > > > [1] - Rant: There seems to be a senseless move to document exactly such > silliness for fear of litigation these days, making modern documentation > more and more a self-indemnification checklist by the manufacturer > rather than a helpful description of the operation and functionality of > the item. It used to be that the number 1 source of information about > the vehicle/device you purchased was its manual (written by Engineers), > now it's more TLDR; (edited by PR/Legal people) and for real information > you simply pray there is a youtube video on the issue by another > enthusiast. > > [2] - I know MSSQL "allows" temp tables to have foreign keys specified, > but it doesn't enforce the constraint, so it's nothing more than no-op > syntactic sugar, perhaps in an effort to not have it choke on > copy-pasted schemata. > > > > ___ > 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] Mixing main and temp databases in foreign keys is not supported
Okay, thanks for the clarification! Best, Manuel On Mon, May 13, 2019 at 1:38 AM J. King wrote: > On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger > wrote: > > >As far as I understood, the main and temp databases are always loaded > >and > >cannot be detached. But, as you pointed out, I can understand that this > >makes sense in the general case. > > Note that the temp database is specific to a given connection while the > main one is not. Creating foreign relations to the temp database would > corrupt another connection's view of the same database > -- > J. King > ___ > 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] Mixing main and temp databases in foreign keys is not supported
Thanks for your quick response! On Mon, May 13, 2019 at 1:17 AM Simon Slavin wrote: > On 12 May 2019, at 11:56pm, Manuel Rigger wrote: > > > Is this intended? > > Yes. Because the temp database disappears when you close your connection. > So you would open a database and find that either a parent or a child table > had disappeared. Which would mean the database was corrupt. > I would not consider the database to be corrupt though, since the behavior for a missing parent table is documented. For example, it is supported to drop a parent table that is still referenced. > > Actually it's not limited to temp. All FOREIGN KEY relationships must be > between two tables in the same database. Because if they were in different > databases you might load one database and not the other. > As far as I understood, the main and temp databases are always loaded and cannot be detached. But, as you pointed out, I can understand that this makes sense in the general case. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mixing main and temp databases in foreign keys is not supported
Hi everyone, It seems that a table created in the temp database cannot have a parent table that is created in the main database and vice versa: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 PRIMARY KEY); CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFERENCES t0(c0)); INSERT INTO t1(c0) VALUES (1); -- no such table: temp.t0 Is this intended? It somehow seems so, since the parent table can also not be prefixed by "temp." when declaring the foreign key in the above example. However, I did not find a note in the documentation that this is not supported. Would it make sense to describe this in the limitations at https://www.sqlite.org/foreignkeys.html? Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial NOT NULL index malfunctions with IS NOT/!=
Great, thanks, Dan! Best, Manuel On Sat, May 11, 2019 at 4:05 PM Dan Kennedy wrote: > > On 11/5/62 16:54, Manuel Rigger wrote: > > Hi everyone, > > > > I found another test case that demonstrates a malfunctioning index: > > > > CREATE TABLE IF NOT EXISTS t0 (c0); > > CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL; > > INSERT INTO t0(c0) VALUES(NULL); > > SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row > > > Thanks for this. Should be fixed now. Ticket here: > >https://sqlite.org/src/tktview/80256748471a01 > > Dan. > > > > > > If the index is created, no rows are fetched. Without the index, the NULL > > row is returned. > > > > I think that this looks like a rather general pattern that could be used > in > > practice. > > > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Partial NOT NULL index malfunctions with IS NOT/!=
Hi everyone, I found another test case that demonstrates a malfunctioning index: CREATE TABLE IF NOT EXISTS t0 (c0); CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL; INSERT INTO t0(c0) VALUES(NULL); SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row If the index is created, no rows are fetched. Without the index, the NULL row is returned. I think that this looks like a rather general pattern that could be used in practice. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding handling of infinity values in ROUND
I noticed that this has been fixed now ( https://www.sqlite.org/src/info/db9acef14d492121). Thanks! Best, Manuel On Fri, May 10, 2019 at 12:09 PM Manuel Rigger wrote: > Hi everyone, > > I was surprised by how the ROUND function handles "Inf" REAL values. > > A REAL infinity value can be created by using an overly-large number: > > SELECT 1e5000; -- Inf > > When casting infinity to an integer value, the largest integer is > returned, which seems intuitive to me: > > SELECT CAST(1e5000 AS INT); -- 9223372036854775807 (2^63 - 1) > > However, the ROUND function always converts Inf to zero, which is somehow > surprising, since a >= b does not imply ROUND(a) >= ROUND(b) (which > contrasts the behavior of a cast): > > SELECT 1e500 >= 1, CAST(1e500 AS INT) >= CAST(1 AS INT), ROUND(1e500) >= > ROUND(1); -- 1|1|0 > > Is this deliberate? Would it make sense to let ROUND(Inf) = Inf (and > ROUND(-Inf) = -Inf))? > > Best, > Manuel > > > > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question regarding handling of infinity values in ROUND
Hi everyone, I was surprised by how the ROUND function handles "Inf" REAL values. A REAL infinity value can be created by using an overly-large number: SELECT 1e5000; -- Inf When casting infinity to an integer value, the largest integer is returned, which seems intuitive to me: SELECT CAST(1e5000 AS INT); -- 9223372036854775807 (2^63 - 1) However, the ROUND function always converts Inf to zero, which is somehow surprising, since a >= b does not imply ROUND(a) >= ROUND(b) (which contrasts the behavior of a cast): SELECT 1e500 >= 1, CAST(1e500 AS INT) >= CAST(1 AS INT), ROUND(1e500) >= ROUND(1); -- 1|1|0 Is this deliberate? Would it make sense to let ROUND(Inf) = Inf (and ROUND(-Inf) = -Inf))? Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
Thanks for the fix! Best, Manuel On Thu, May 9, 2019 at 7:12 PM Richard Hipp wrote: > On 5/9/19, Manuel Rigger wrote: > > > > I discovered a sequence of statements that results in a malformed > database > > disk image: > > > > Should be fixed now. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Series of statements results in a malformed database disk image
Hi, I discovered a sequence of statements that results in a malformed database disk image: CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); UPDATE t1 SET c0 = NULL; UPDATE OR REPLACE t1 SET c1 = 1; SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); The last statement returns the following: |1.0 Error: near line 5: database disk image is malformed Unlike some of my previous test cases, this actually looks like something that could happen in practice, or what do you think? Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row is not fetched with PRAGMA reverse_unordered_selects=true
Hi E. Pasma, Thanks for your interest in this! The test case failed on trunk, after the previous bug that you are referring to was fixed. It seems that the bug I newly reported has been fixed in https://www.sqlite.org/src/info/ebe4845cd0ffb96b. In order to trigger it, this more complex (and admittedly obscure) test case was necessary. Best, Manuel On Thu, May 9, 2019 at 10:54 AM E.Pasma wrote: > > > Op 9 mei 2019, om 00:07 heeft Manuel Rigger > het volgende geschreven: > > Hi, > > > > I discovered another bug that is triggered when "PRAGMA > > reverse_unordered_selects=true" is used. It's similar to a previous bug > > that I reported [1], but the statement triggering the bug has a compound > > expression that should always be true (for values that are not NULL) in > the > > WHERE clause: > > > > CREATE TABLE t0(c0 INTEGER PRIMARY KEY); > > INSERT INTO t0(c0) VALUES (1); > > PRAGMA reverse_unordered_selects=true; > > SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no > row > > SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1 > > > > Best, > > Manuel > > > > > > [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51 > > Hi Mauel, > > This appears to have been fixed with the previous bug that you referred > to. The example in the referred ticket is even more simple: > CREATE TABLE t14(x INTEGER PRIMARY KEY); > INSERT INTO t14(x) VALUES (100); > SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC; > SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC; > Hope I'm not mistaken, > > E. Pasma > ___ > 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] Row is not fetched with PRAGMA reverse_unordered_selects=true
Hi, I discovered another bug that is triggered when "PRAGMA reverse_unordered_selects=true" is used. It's similar to a previous bug that I reported [1], but the statement triggering the bug has a compound expression that should always be true (for values that are not NULL) in the WHERE clause: CREATE TABLE t0(c0 INTEGER PRIMARY KEY); INSERT INTO t0(c0) VALUES (1); PRAGMA reverse_unordered_selects=true; SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no row SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1 Best, Manuel [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE does not fetch row when used on INT UNIQUE COLLATE NOCASE column
Hi,
I found an issue where a row is not fetched when using a LIKE operator on
an INT UNIQUE COLLATE NOCASE column:
CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('./');
SELECT * FROM t0 WHERE t0.c0 LIKE './'; -- fetches no rows
The following query returns TRUE:
SELECT t0.c0 LIKE './' FROM t0; -- returns 1
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR FAIL inserts row although it violates a table constraint
Great, thanks! In which commits were they addressed? I saw two commits that address https://www.sqlite.org/src/info/e63cbcfd3378afe6980d6. Was this issue derived from the test case? Best, Manuel On Tue, May 7, 2019 at 10:08 PM Richard Hipp wrote: > Two separate problems, both now fixed on trunk. > > On 5/6/19, Manuel Rigger wrote: > > Hi everyone, > > > > consider the following example: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES > > (0, 1), > > (0, 2); > > SELECT * FROM t0; -- returns 0|1 > > > > I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY > > constraints are violated. However, the (0, 1) row is inserted, as the > > result of the SELECT query above demonstrates. When splitting up the > INSERT > > into two INSERTS, no row is inserted, as expected: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); > > INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); > > SELECT * FROM t0; -- returns no row > > > > I found this bug because a WHERE clause did not fetch the row after a > > REINDEX: > > > > PRAGMA foreign_keys=true; > > CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) > REFERENCES > > t0(c1)); > > INSERT OR FAIL INTO t0(c0, c1) VALUES > > (0, 1), > > (0, 2); > > REINDEX;; > > SELECT * FROM t0; -- returns 0|1 > > SELECT * FROM t0 WHERE c1=1; -- returns nothing > > > > Best, > > Manuel > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] PRAGMA reverse_unordered_selects=true results into row not being fetched
Hi everyone,
I discovered what I think is a bug, as demonstrated below:
CREATE TABLE t0(c0);
CREATE TABLE t1(c0 INTEGER PRIMARY KEY);
PRAGMA reverse_unordered_selects=true;
INSERT INTO t1(c0) VALUES (0);
INSERT INTO t0(c0) VALUES ('a');
SELECT * FROM t1, t0 WHERE t1.c0 < t0.c0;
I would expect (0, 'a') to be fetched, which is not the case. The
comparison should be true, as demonstrated by this query:
SELECT t1.c0 < t0.c0 FROM t1, t0; -- 1
The bug is only triggered when setting the PRAGMA and when c0 is an INTEGER
PRIMARY KEY.
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR FAIL inserts row although it violates a table constraint
Hi everyone, consider the following example: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); SELECT * FROM t0; -- returns 0|1 I expect the INSERT to fail, since both the UNIQUE and the FOREIGN KEY constraints are violated. However, the (0, 1) row is inserted, as the result of the SELECT query above demonstrates. When splitting up the INSERT into two INSERTS, no row is inserted, as expected: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 2); SELECT * FROM t0; -- returns no row I found this bug because a WHERE clause did not fetch the row after a REINDEX: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 INT UNIQUE, c1 TEXT UNIQUE, FOREIGN KEY(c0) REFERENCES t0(c1)); INSERT OR FAIL INTO t0(c0, c1) VALUES (0, 1), (0, 2); REINDEX;; SELECT * FROM t0; -- returns 0|1 SELECT * FROM t0 WHERE c1=1; -- returns nothing Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table
Great, thanks a lot, Dan! Best, Manuel On Mon, May 6, 2019 at 6:18 PM Dan Kennedy wrote: > > On 6/5/62 16:42, Manuel Rigger wrote: > > Hi everyone, > > > > the following example fails with an error "no such column: c0": > > > > CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID; > > ALTER TABLE t0 RENAME COLUMN c0 TO c1; > > Thanks again for the bug reports. This one is now fixed here: > > https://sqlite.org/src/info/91f701d39852ef1ddb29 > > Dan. > > > > > > > However, specifying c0 as the PRIMARY KEY in the column definition rather > > than in a table constraint seems to work: > > > > CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID; > > ALTER TABLE t0 RENAME COLUMN c0 TO c1; > > > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table
Hi everyone, the following example fails with an error "no such column: c0": CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID; ALTER TABLE t0 RENAME COLUMN c0 TO c1; However, specifying c0 as the PRIMARY KEY in the column definition rather than in a table constraint seems to work: CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID; ALTER TABLE t0 RENAME COLUMN c0 TO c1; Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
Thanks for the explanation and the quick fix! Best, Manuel On Sat, May 4, 2019 at 7:41 PM Richard Hipp wrote: > Here is another case: > > CREATE TABLE t1(a,b,c); > INSERT INTO t1 VALUES(NULL,8,'yes'); > CREATE INDEX t1b ON t1(b) WHERE a IS NOT NULL; > SELECT c FROM t1 WHERE b=8 AND (a OR 1); > > The problem was in the theorem prover that determines when a partial > index can be used. The problem goes all the way back to the initial > introduction of partial indexes in SQLite version 3.8.0 (2013-08-26). > The theorem prover was (incorrectly) assuming that if the expression > "a OR 1" is true, then "a IS NOT NULL" must also be true. And that > assumption is correct for most binary operators - just not for OR. > Fixed now. > > On 5/4/19, Manuel Rigger wrote: > > This similar test case, that I just found now, demonstrates that this > could > > be a pattern that is used in practice (TRUE can also be computed): > > > > CREATE TABLE t0 (c0); > > CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; > > INSERT INTO t0(c0) VALUES (NULL); > > SELECT * FROM t0 WHERE (c0 OR TRUE); > > > > Also here, the row is not fetched. > > > > Best, > > Manuel > > > > On Sat, May 4, 2019 at 3:45 PM Manuel Rigger > > wrote: > > > >> Hi, > >> > >> I discovered a bug, which is demonstrated through the following test > case: > >> > >> CREATE TABLE t0(c0); > >> CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; > >> INSERT INTO t0(c0) VALUES (NULL); > >> SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); > >> > >> No row is fetched, although the WHERE clause is always TRUE. I could > >> reproduce this bug only when creating a partial index, and when using > >> either LIKELY or UNLIKELY. The datatype of the c0 column seems to > >> irrelevant. PRAGMA integrity_check; and REINDEX could not detect this > >> error. > >> > >> Best, > >> Manuel > >> > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Problem with REAL PRIMARY KEY
Sorry, I should have anticipated that we get slightly different values. Shouldn't the query "SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);" return a result though? Best, Manuel On Sat, May 4, 2019 at 8:17 PM Keith Medcalf wrote: > > Ooopsie ... that should have been 1e17, and it appears to be fine, except > that: > > SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1); > > does not work ever though the value returned from the subselect should be > exactly the value in the index ... > > A table scan does however work correctly ... > > sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from t1); > |5.76460752303423e+17 > > > --- > 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 Keith Medcalf > >Sent: Saturday, 4 May, 2019 12:09 > >To: SQLite mailing list > >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY > > > > > >There is, however, something weird: > > > >SQLite version 3.29.0 2019-05-04 17:32:07 > >Enter ".help" for usage hints. > >Connected to a transient in-memory database. > >Use ".open FILENAME" to reopen on a persistent database. > >sqlite> .version > >SQLite 3.29.0 2019-05-04 17:32:07 > >c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2 > >zlib version 1.2.11 > >gcc-8.1.0 > >sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); > >sqlite> INSERT INTO t1(c1) VALUES (0X7ff);; > >sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17; > >sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1); > >sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1); > >sqlite> select c1 from t1; > >5.76460752303423e+17 > >sqlite> select c1 - 1 from t1; > >5.76460752303423e+17 > >sqlite> > > > > > >--- > >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 Richard Hipp > >>Sent: Saturday, 4 May, 2019 11:49 > >>To: SQLite mailing list > >>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY > >> > >>On 5/4/19, Manuel Rigger wrote: > >>> Hi everyone, > >>> > >>> Consider the following example: > >>> > >>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); > >>> INSERT INTO t1(c1) VALUES (0X7ff);; > >>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17; > >>> > >>> I would expect the row to be fetched, which is not the case. > >> > >>But 0x7ff != 5.76460752303423e+17. Try it: > >> > >> SELECT 0x7ff != 5.76460752303423e+17; > >> > >>You should get back 0. > >> > >>The rule of thumb is to never expect the == operator to give a > >>meaningful answer for floating-point numbers. Only use <, <=, >, > >and > >>>=. > >> > >>-- > >>D. Richard Hipp > >>d...@sqlite.org > >>___ > >>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-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] Problem with REAL PRIMARY KEY
Hi everyone, Consider the following example: CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0)); INSERT INTO t1(c1) VALUES (0X7ff);; SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17; I would expect the row to be fetched, which is not the case. I confirmed that the real value stored is indeed equal to the constant: sqlite> SELECT *, typeof(c1) FROM t1; |5.76460752303423e+17|real This is not only the case when using a literal, but also when querying the value stored in the row in a sub query. So the following expression also does not fetch the row: SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1); It seems that the PRIMARY KEY causes the bug. When it is removed (or one of the columns), the row is returned as expected. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
This similar test case, that I just found now, demonstrates that this could be a pattern that is used in practice (TRUE can also be computed): CREATE TABLE t0 (c0); CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; INSERT INTO t0(c0) VALUES (NULL); SELECT * FROM t0 WHERE (c0 OR TRUE); Also here, the row is not fetched. Best, Manuel On Sat, May 4, 2019 at 3:45 PM Manuel Rigger wrote: > Hi, > > I discovered a bug, which is demonstrated through the following test case: > > CREATE TABLE t0(c0); > CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; > INSERT INTO t0(c0) VALUES (NULL); > SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); > > No row is fetched, although the WHERE clause is always TRUE. I could > reproduce this bug only when creating a partial index, and when using > either LIKELY or UNLIKELY. The datatype of the c0 column seems to > irrelevant. PRAGMA integrity_check; and REINDEX could not detect this error. > > Best, > Manuel > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
Hi, I discovered a bug, which is demonstrated through the following test case: CREATE TABLE t0(c0); CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; INSERT INTO t0(c0) VALUES (NULL); SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); No row is fetched, although the WHERE clause is always TRUE. I could reproduce this bug only when creating a partial index, and when using either LIKELY or UNLIKELY. The datatype of the c0 column seems to irrelevant. PRAGMA integrity_check; and REINDEX could not detect this error. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on REAL malfunctions in combination with the LENGTH function
Unfortunately, I deleted some other similar cases that I found before reading your reply. I'll gather any new cases (I assume you are talking about cases that the int-real branch should address). Best, Manuel On Fri, May 3, 2019 at 2:34 PM Richard Hipp wrote: > On 5/3/19, Manuel Rigger wrote: > > I just tried the examples on the int-real branch, > > and it seems that they do not cause any errors. > > That is good to hear. > > It would be helpful if you could gather together all of your test > cases and send them to us, so that we could add them to the SQLite > regression tests. > > -- > D. Richard Hipp > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on REAL malfunctions in combination with the LENGTH function
Thanks for the feedback! I just tried the examples on the int-real branch,
and it seems that they do not cause any errors.
So I guess these two examples can be considered to be duplicate bug reports
of my "Index on REAL column malfunctions when multiplying with a string"
email.
Best,
Manuel
On Fri, May 3, 2019 at 1:49 PM Richard Hipp wrote:
> On 5/3/19, Manuel Rigger wrote:
> > I found another similar example with GLOB:
> >
> > CREATE TABLE test (c0 REAL);
> > CREATE UNIQUE INDEX index_0 ON test(('0' GLOB -c0));
> > INSERT INTO test(c0) VALUES (1.0), (0.0);
> > REINDEX;
> >
> > Is the issue related to the implementation of the unary minus operator
> > (i.e., do both examples trigger the same bug)?
>
> That seems likely. Did you try this using the int-real branch
> (https://www.sqlite.org/src/timeline?r=int-real)? That was my latest
> effort to address this corner case. We are working on some more
> pressing concerns right this moment. I will return to that branch,
> finish testing it, and perhaps merge it to trunk, when I get a chance.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on REAL malfunctions in combination with the LENGTH function
I found another similar example with GLOB:
CREATE TABLE test (c0 REAL);
CREATE UNIQUE INDEX index_0 ON test(('0' GLOB -c0));
INSERT INTO test(c0) VALUES (1.0), (0.0);
REINDEX;
Is the issue related to the implementation of the unary minus operator
(i.e., do both examples trigger the same bug)?
Best,
Manuel
On Fri, May 3, 2019 at 1:22 PM Manuel Rigger
wrote:
> Hi,
>
> I found another very specific corner case for which an index fails
> ("UNIQUE constraint failed: index 'index_0'"):
>
> CREATE TABLE test (c0 REAL);
> CREATE UNIQUE INDEX index_0 ON test(LENGTH(-c0));
> INSERT INTO test(c0) VALUES (0.0), ('10:');
> REINDEX;
>
>
> Best,
> Manuel
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on REAL malfunctions in combination with the LENGTH function
Hi,
I found another very specific corner case for which an index fails ("UNIQUE
constraint failed: index 'index_0'"):
CREATE TABLE test (c0 REAL);
CREATE UNIQUE INDEX index_0 ON test(LENGTH(-c0));
INSERT INTO test(c0) VALUES (0.0), ('10:');
REINDEX;
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on REAL column malfunctions when multiplying with a string
Hi everyone,
I found another corner case where I could break an index on a REAL column
(UNIQUE constraint failed: index 'index_0').
CREATE TABLE test (c0 REAL);
CREATE UNIQUE INDEX index_0 ON test(TRIM(('' * c0)));
INSERT INTO test(c0) VALUES (0.0), (0.1);
REINDEX;
As with the previous examples, a REAL column is combined with some string
manipulations. The multiplication operator seems necessary, but TRIM can be
replaced with another string function.
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TYPEOF index on REAL column malfunctions
Thanks a lot! Best, Manuel On Thu, May 2, 2019 at 7:52 PM Richard Hipp wrote: > On 5/2/19, Manuel Rigger wrote: > > Hi everyone, > > > > I think that I found another issue related to type affinity on real > columns: > > The typeof() function corner-case you identified has been fixed in > check-in https://www.sqlite.org/src/timeline?c=48889530a9de22fe > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] UPSERT clause does not work with "NOT NULL" constraint
Great, thanks! Best, Manuel On Thu, May 2, 2019 at 6:25 PM Richard Hipp wrote: > Documentation has been updated in an attempt to clarify when UPSERT > applies and when it does not. > > On 5/2/19, Manuel Rigger wrote: > > Okay, thanks for the clarification! > > > > I think that this part of the documentation is ambiguous. The part of the > > documentation that you quoted mentions a "conflict target", but there is > no > > conflict target in the example that I provided. The documentation > > continues by stating that a conflict target is not necessary and that "A > DO > > NOTHING upsert without a conflict target works the same as an INSERT OR > > IGNORE." Would it maybe be helpful to update the documentation to > > explicitly state that UPSERT does not apply to NOT NULL constraints, and > > that apart from this case DO NOTHING works in the same way as INSERT OR > > IGNORE? > > > > Best, > > Manuel > > > > On Thu, May 2, 2019 at 5:38 PM Richard Hipp wrote: > > > >> On 5/2/19, Manuel Rigger wrote: > >> > Hi everyone, > >> > > >> > It seems that upsert does not take into account "NOT NULL" > constraints. > >> In > >> > the example below, I get an error "NOT NULL constraint failed: > >> > test.c0": > >> > > >> > CREATE TABLE test (c0 NOT NULL); > >> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; > >> > > >> > I would have expected that the second statement has the same effect as > >> the > >> > following statement, which would also be confirmed by the docs [1]: > >> > > >> > INSERT OR IGNORE INTO test(c0) VALUES (NULL); > >> > > >> > The example seems to work for the UNIQUE and PRIMARY KEY constraints. > >> > >> UPSERT is not standard SQL - it is a PostgreSQL extension that we have > >> attempted to replicate. PostgreSQL behaves the same way in this test > >> (a fact that I have just now verified on sqlfiddle.com). The ON > >> CONFLICT clause is only triggered by uniqueness constraints, not NOT > >> NULL constraints. > >> > >> The UPSERT documentation says "The conflict target specifies a > >> specific uniqueness constraint that will trigger the upsert." So it > >> does not explicitly say that UPSERT does not work for NOT NULL > >> constraints, but that is the implication. > >> > >> > >> -- > >> D. Richard Hipp > >> d...@sqlite.org > >> > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TYPEOF index on REAL column malfunctions
Hi everyone, I think that I found another issue related to type affinity on real columns: CREATE TABLE test (c0 REAL); CREATE UNIQUE INDEX index_0 ON test(TYPEOF(c0)); INSERT OR IGNORE INTO test(c0) VALUES (0.1); INSERT OR IGNORE INTO test(c0) VALUES (FALSE); REINDEX; In this example, the REINDEX fails with "UNIQUE constraint failed: index 'index_0'". FALSE is converted to 0.0, so two REALs are stored. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
Okay, thanks for the clarification! I think that this part of the documentation is ambiguous. The part of the documentation that you quoted mentions a "conflict target", but there is no conflict target in the example that I provided. The documentation continues by stating that a conflict target is not necessary and that "A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE." Would it maybe be helpful to update the documentation to explicitly state that UPSERT does not apply to NOT NULL constraints, and that apart from this case DO NOTHING works in the same way as INSERT OR IGNORE? Best, Manuel On Thu, May 2, 2019 at 5:38 PM Richard Hipp wrote: > On 5/2/19, Manuel Rigger wrote: > > Hi everyone, > > > > It seems that upsert does not take into account "NOT NULL" constraints. > In > > the example below, I get an error "NOT NULL constraint failed: test.c0": > > > > CREATE TABLE test (c0 NOT NULL); > > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; > > > > I would have expected that the second statement has the same effect as > the > > following statement, which would also be confirmed by the docs [1]: > > > > INSERT OR IGNORE INTO test(c0) VALUES (NULL); > > > > The example seems to work for the UNIQUE and PRIMARY KEY constraints. > > UPSERT is not standard SQL - it is a PostgreSQL extension that we have > attempted to replicate. PostgreSQL behaves the same way in this test > (a fact that I have just now verified on sqlfiddle.com). The ON > CONFLICT clause is only triggered by uniqueness constraints, not NOT > NULL constraints. > > The UPSERT documentation says "The conflict target specifies a > specific uniqueness constraint that will trigger the upsert." So it > does not explicitly say that UPSERT does not work for NOT NULL > constraints, but that is the implication. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT clause does not work with "NOT NULL" constraint
Hi everyone, It seems that upsert does not take into account "NOT NULL" constraints. In the example below, I get an error "NOT NULL constraint failed: test.c0": CREATE TABLE test (c0 NOT NULL); INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; I would have expected that the second statement has the same effect as the following statement, which would also be confirmed by the docs [1]: INSERT OR IGNORE INTO test(c0) VALUES (NULL); The example seems to work for the UNIQUE and PRIMARY KEY constraints. Best, Manuel [1] "A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE." at https://sqlite.org/lang_UPSERT.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COLLATE NOCASE index on REAL column malfunctions
@Warren: I'm building a tool to test DBMS by automatically generating
queries and checking their results. Since the statement sequence was
generated automatically, it looks like artificial.
@Keith Thanks again for the explanation!
Best,
Manuel
On Thu, May 2, 2019 at 1:24 AM Keith Medcalf wrote:
>
> Again, I think this is a problem with applying affinity when the index is
> created. The result of applying real affinity to the string '+/' should
> probably be the string '+/' not the real value 0. On the gripping hand,
> '+/' looks like a number with "crud" at the end of the string. I believe
> this is documented somewhere for the application of numeric affinity to a
> string that looks like a number but with crud at the end.
>
> sqlite> select cast('27 dollars' as real);
> 27.0
> sqlite> select cast('+/' as real);
> 0.0
>
> Although c0 is stored in the table as TEXT, the application of real
> affinity to the string '+/' when building the index results in a real(0).
>
> The COLLATE NOCASE simply allows the LIKE operator to use the index (since
> using an index for LIKE in the default case insensitive mode requires an
> index with COLLATE NOCASE). Since the index is incorrect (the index key is
> real(0) not text('+/') the index lookup fails.
>
> ---
> 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: Wednesday, 1 May, 2019 15:31
> >To: SQLite mailing list
> >Subject: Re: [sqlite] COLLATE NOCASE index on REAL column
> >malfunctions
> >
> >I'm very sorry, after finding the issue using the latest stable Linux
> >version, I accidentally used an outdated version (3.24.0) to produce
> >a
> >minimal failing case. Here is a reduced example that triggers the bug
> >on
> >the latest stable [1] and snapshot [2] versions:
> >
> >CREATE TABLE test (c0 REAL);
> >CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
> >INSERT INTO test(c0) VALUES ('+/');
> >SELECT * FROM test WHERE (c0 LIKE '+/');
> >
> >Best,
> >Manuel
> >
> >
> >[1] 3.28.0 2019-04-16 19:49:53
> >884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
> >[2] 3.29.0 2019-04-27 20:30:19
> >50fe48458942fa7a6bcc76316c6321f95b23dc34f2f8e0a483826483b2fb16f6
> >
> >On Wed, May 1, 2019 at 9:55 PM Warren Young
> >wrote:
> >
> >> On May 1, 2019, at 1:18 PM, Richard Hipp wrote:
> >> >
> >> > I am unable to reproduce the observed behavior.
> >>
> >> Nor I, on 3.28.0 release with our custom build.
> >>
> >> Thank you for providing a simple test case, Manuel: it helps
> >greatly!
> >>
> >> > What version of
> >> > SQLite are you testing with? Are you compiling it yourself? If
> >so,
> >> > what compile-time options do you use?
> >>
> >> Also, what is your locale setting? The fact that COLLATE NOCASE
> >affects
> >> it makes this smell like a locale/i18n issue, to me.
> >> ___
> >> 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-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] COLLATE NOCASE index on REAL column malfunctions
I'm very sorry, after finding the issue using the latest stable Linux
version, I accidentally used an outdated version (3.24.0) to produce a
minimal failing case. Here is a reduced example that triggers the bug on
the latest stable [1] and snapshot [2] versions:
CREATE TABLE test (c0 REAL);
CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
INSERT INTO test(c0) VALUES ('+/');
SELECT * FROM test WHERE (c0 LIKE '+/');
Best,
Manuel
[1] 3.28.0 2019-04-16 19:49:53
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
[2] 3.29.0 2019-04-27 20:30:19
50fe48458942fa7a6bcc76316c6321f95b23dc34f2f8e0a483826483b2fb16f6
On Wed, May 1, 2019 at 9:55 PM Warren Young wrote:
> On May 1, 2019, at 1:18 PM, Richard Hipp wrote:
> >
> > I am unable to reproduce the observed behavior.
>
> Nor I, on 3.28.0 release with our custom build.
>
> Thank you for providing a simple test case, Manuel: it helps greatly!
>
> > What version of
> > SQLite are you testing with? Are you compiling it yourself? If so,
> > what compile-time options do you use?
>
> Also, what is your locale setting? The fact that COLLATE NOCASE affects
> it makes this smell like a locale/i18n issue, to me.
> ___
> 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] COLLATE NOCASE index on REAL column malfunctions
Hi everyone,
Consider the example below:
CREATE TABLE test (c0 REAL);
CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
INSERT INTO test(c0) VALUES ('/');
SELECT * FROM test WHERE (c0 LIKE '/');
Unexpectedly, the SELECT statement does not return a result. If the
statement for the creation of the index is omitted, the row that is
inserted is returned.
It seems that the bug is triggered only when the column is of type INT,
REAL, or NUMERIC. I could also reproduce this only for a COLLATE NOCASE
index and the slash character.
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint fails when setting legacy_file_format=true
Hi everyone,
I think that I found a bug that occurs when setting legacy_file_format to
true and calling REINDEX, which then results in "Error: UNIQUE constraint
failed: index 'index_0'" in the specific example below:
CREATE TABLE test (c0, c1 TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(c1 == FALSE);
CREATE INDEX IF NOT EXISTS index_1 ON test(c0 || FALSE) WHERE c1;
INSERT OR IGNORE INTO test(c0, c1) VALUES ('a', TRUE);
INSERT OR IGNORE INTO test(c0, c1) VALUES ('a', FALSE);
PRAGMA legacy_file_format=true;
REINDEX;
I would not expect the UNIQUE constraint to fail, since there is only one
value that equals to FALSE in c1:
sqlite> SELECT * FROM test WHERE c1 == FALSE;
a|0
I could not reduce the example further. I originally found this issue with
a slightly more complex example and VACUUM instead of REINDEX:
CREATE TABLE test (c0, c1 TEXT);
CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON test(FALSE >= c1 DESC);
CREATE UNIQUE INDEX IF NOT EXISTS index_1 ON test((c0 || FALSE) ASC) WHERE
c1;
INSERT OR IGNORE INTO test(c0, c1) VALUES ('', '1');
INSERT OR IGNORE INTO test(c0, c1) VALUES ('', FALSE);
SELECT * FROM test;
PRAGMA legacy_file_format=true;
VACUUM;
Best,
Manuel
___
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] 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
[sqlite] PRAGMA case_sensitive_like conflicts with LIKE operator when creating an index
Hi everyone, 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. For example, the example below results in "Error: non-deterministic functions prohibited in index expressions". PRAGMA case_sensitive_like=false; CREATE TABLE test (c0); CREATE INDEX IF NOT EXISTS index_1 ON test(c0 LIKE "a"); Is this a bug or intended? From the documentation, I would guess that when registering the LIKE and GLOB functions, they are not registered as being deterministic. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result when using "without rowid" and creating a "collate nocase" index
Great, thanks for opening the bug report and for the attribution!
Best,
Manuel
Am So., 28. Apr. 2019 um 17:07 Uhr schrieb Richard Hipp :
> On 4/28/19, Manuel Rigger wrote:
> >
> > CREATE TABLE test (c1 TEXT PRIMARY KEY) WITHOUT ROWID;
> > CREATE INDEX index_0 ON test(c1 COLLATE NOCASE);
> > INSERT INTO test(c1) VALUES ('A');
> > INSERT INTO test(c1) VALUES ('a');
> > SELECT * FROM test;
> >
> > Is this indeed a bug, or a misunderstanding on my side?
>
> https://www.sqlite.org/src/tktview/3182d3879020ef3b2e6db56be2470a0266d3c773
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Unexpected result when using "without rowid" and creating a "collate nocase" index
Hi everyone,
I found what could be a bug. Consider the example below:
CREATE TABLE test (c1 TEXT PRIMARY KEY) WITHOUT ROWID;
CREATE INDEX index_0 ON test(c1 COLLATE NOCASE);
INSERT INTO test(c1) VALUES ('A');
INSERT INTO test(c1) VALUES ('a');
SELECT * FROM test;
The query returns only one result, namely ('a'), while I would have
expected both rows to be fetched. It seems that this bug only exists when
(1) a COLLATE NOCASE index is created and (2) WITHOUT ROWID is used. When
using COUNT(*), the expected number of 2 is returned.
Is this indeed a bug, or a misunderstanding on my side?
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] String interpreted as a column name when creating an index
@Simon: Thanks for pointing that out! The difference in the effects of using single vs. double quotes in the select query is still the same though. @Richard: Thanks for taking time to explain this to me and for pointing me to the comment in the source code! I've found another case where, depending on the context, a string in the ORDER BY clause is either interpreted as a column name or a string (see below). CREATE TABLE test (c0); SELECT * FROM test ORDER BY 'asdf'; SELECT (0) INTERSECT SELECT * FROM test ORDER BY 'asdf' In the first query, ORDER BY 'asdf' is interpreted as a string and does not cause any error, while in the second query it causes "1st ORDER BY term does not match any column in the result set". As you pointed out, there is no point in having a constant expression in such contexts, and I assume that this case is also related to backward compatibility. Best, Manuel Am Sa., 27. Apr. 2019 um 22:14 Uhr schrieb Richard Hipp : > On 4/27/19, Manuel Rigger wrote: > > Thanks for your quick and helpful reply! So if I understood correctly, > > there is no way to ensure that a string is not interpreted as a column in > > an arbitrary expression, right? > > String literal is always just a string literal in an arbitrary > expression. String literals can only be interpreted as an identifier > in a context where only an identifier is allowed, such as after > "CREATE TABLE", for example. > > The one exception to the above is that if the argument to CREATE INDEX > is an expression that is comprised of a single string literal, then > that string literal is interpreted as a column name rather than as an > expression. This is done for historical compatibility, and because > there is no point in having an index on a constant expression. See > the implementation at > https://www.sqlite.org/src/artifact/61655dad911a?ln=1319-1341 > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] String interpreted as a column name when creating an index
Thanks for your quick and helpful reply! So if I understood correctly,
there is no way to ensure that a string is not interpreted as a column in
an arbitrary expression, right? In another example, it was the other way
around and I had to use single quotes rather than double quotes to prevent
the string from being interpreted as a column name (see below). Using
double quotes for c1 (i.e., c0 = "c1") results in no rows being returned
(since c1 is interpreted as a column name), while using single quotes
results in the row being fetched.
CREATE TABLE test (c0, c1);
INSERT INTO test(c0, c1) VALUES ("c1", 0);
SELECT * FROM test WHERE c0 = 'c1';
Best,
Manuel
Am Sa., 27. Apr. 2019 um 21:14 Uhr schrieb Richard Hipp :
> On 4/27/19, Manuel Rigger wrote:
> >
> > when executing the example below, I get "Error: no such column: asdf".
> This
> > behavior is surprising to me, as I would have expected "asdf" to be
> > interpreted as a string and not as a column name.
> >
> > CREATE TABLE test (c0);
> > CREATE INDEX index_1 ON test('asdf');
> >
> > Could this be a bug or an unnoticed inconsistency?
>
> This is intentional, though undocumented (or at least I don't recall
> documenting it). You can, in fact, use a string literal for the name
> of a table or column. For example:
>
> CREATE TABLE 'test'('c0');
>
> Which is logically equivalent to your original:
>
> CREATE TABLE test(c0);
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] String interpreted as a column name when creating an index
Hi everyone,
when executing the example below, I get "Error: no such column: asdf". This
behavior is surprising to me, as I would have expected "asdf" to be
interpreted as a string and not as a column name.
CREATE TABLE test (c0);
CREATE INDEX index_1 ON test('asdf');
According to the docs, single quotes are reserved for strings: "A string
constant is formed by enclosing the string in single quotes (')." [1]
Although creating an index on a (string) literal does not provide any
value, the documentation seems to allow any expression (with some
restrictions). Thus, indexes on literals of other data types and on
computed strings do not result in an error (see examples below).
CREATE INDEX index_2 ON test(3);
CREATE INDEX index_3 ON test(3.3);
CREATE INDEX index_4 ON test('as'||'df');
Enclosing the string in double quotes does not result in an error either
(see below).
CREATE INDEX index_5 ON test("asdf");
Could this be a bug or an unnoticed inconsistency? I stumbled upon it while
randomly creating SQL statements, and addressing this would allow me to
consistently use single quotes for strings.
Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
