Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
I just want to point something out that might help the original poster. On Saturday, March 7, 2020, 7:00:21 AM EST, sqlite-users-requ...@mailinglists.sqlite.org wrote: > > 1. NULL is NULL = Yes, True, > 2. NULL is FALSE = Nope, False. > 3. NULL is TRUE = Nope, False. > 4. NULL is NOT NULL = Nope, False, > 5. NULL is NOT FALSE = Yep, True. > 6. NULL is NOT TRUE = Yep, True. > 7. TRUE is FALSE = Nope, False. > 8. TRUE is NOT FALSE = Yep, True. > 9. FALSE is NOT TRUE = Yep, True. This explanation 100% correct and probably 80% confusing without the following, especially because "is" is not capitalized:IS and IS NOT are logical operators in SQL. NOT is not a unary operator when preceded by IS. In most non-relational languages "NULL IS NOT TRUE" is parsed as: value(NULL) operator(IS) (operator(NOT) value(TRUE)). That is NOT how SQL works. In SQL, it is: value(NULL) operator(IS NOT) value(TRUE). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 2020/03/07 03:52, Xinyue Chen wrote: Hi, If I change IS NOT FALSE to IS TRUE, the results will be different. I assume they should perform in the same way? if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. Then why doesn't that query also return 4 rows? Some excellent answers were already given, but in case you still wonder... In Boolean logic, a value can only be TRUE or FALSE. But in SQL (which has to model the real World Algebraically) there is also the possibility that the state is simply NOT KNOWN (or indeed that a variable/placeholder/identifier can represent no value at all). The expression (Null = Null) is always NULL - it's like asking if an unknown person is exactly the same person as another unknown person? The answer is obviously "We don't know. It might be, so we cannot say definitively it ISN'T the case, but it might also NOT be the same person, so the only correct answer is: We don't know". Further, "We don't know" in logic terms is undefined, which in SQL we write as "NULL". While (Null = Null) in mathematical terms is always unknown, we can however test if two values are of the same kind with "is", and more specifically, test if they are both unknown, so the expression (NULL is NULL) correctly returns True. This whole "Three possible states" thing is no longer simply Boolean logic, but indeed Trivalent logic with the possible values being NULL/TRUE/FALSE. Writing the matrix of states of (x IS [NOT] y) down and numbering them we get 9 symantically distinct evaluations (there are more, like "FALSE is TRUE", but they can be rearranged as one of these): 1. NULL is NULL = Yes, True, 2. NULL is FALSE = Nope, False. 3. NULL is TRUE = Nope, False. 4. NULL is NOT NULL = Nope, False, 5. NULL is NOT FALSE = Yep, True. 6. NULL is NOT TRUE = Yep, True. 7. TRUE is FALSE = Nope, False. 8. TRUE is NOT FALSE = Yep, True. 9. FALSE is NOT TRUE = Yep, True. Thus when you ask: "I assume they should perform in the same way? if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. Then why doesn't that query also return 4 rows?" You assume that state 5 (NULL IS NOT FALSE) and state 3 (NULL IS TRUE) would mean the same thing, but as you can see from the list, in Trivalent logic it clealy doesn't - one is False and the other is True. The stuff of nightmares to a purist, I know. In the real World though, some stuff simply isn't known and therefore cannot fit into the simple Boolean logic of TRUE and FALSE. Best of luck! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/20 9:54 PM, Keith Medcalf wrote: On Friday, 6 March, 2020 19:25, Richard Damon wrote: It is sort of like NaN, where a Nan is neither less than, greater than or equal to any value, including itself. NULL (as in SQL NULL) means "missing value" or "unknown". NULL represents any value within the domain, we simply do not know what that value is. That is, the value "NULL" for colour of a car means that we do not know the colour -- however, it still has one. NaN, on the other hand, means that the value is outside the domain and that there is no possible value of the domain which well render the proposition true. For example, the state of Schroedingers Cat is NULL. It has a state, either dead or alive. That state is merely unknown until one looks in the box. However, if when you looked in the box there was no cat, then the cat would be a NaN since its state was outside of the domain of states for a cat in a box with a time release poison after the release of the poison, that is, the non-existance of a cat in the box precludes the possibility of the state of the cat in the box being either either dead or alive. It may have a different meaning, but similar effects on logic. As an aside, for the quantum effect Schrodinger's Cat is designed to demonstrate, the cat ISN'T just one of dead or alive but not know which, but exists as a probability wave between the two states. This is why the photon which goes through one of two slits generates an interference pattern unless you detect which slit it goes through, if you measure the slit it went through, you get a different pattern of light, as the lack of knowledge allows it to be less precise in its position and the probability of going through the left slit interferes with the probability of that same particle going through the right slit, so the pattern implies it sort of went through both at once. Detecting the state of Schrodinger's Cat actually changes its state, collapsing the wave into one of the definitive states. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On Friday, 6 March, 2020 19:25, Richard Damon wrote: >It is sort of like NaN, where a Nan is neither less than, greater than >or equal to any value, including itself. NULL (as in SQL NULL) means "missing value" or "unknown". NULL represents any value within the domain, we simply do not know what that value is. That is, the value "NULL" for colour of a car means that we do not know the colour -- however, it still has one. NaN, on the other hand, means that the value is outside the domain and that there is no possible value of the domain which well render the proposition true. For example, the state of Schroedingers Cat is NULL. It has a state, either dead or alive. That state is merely unknown until one looks in the box. However, if when you looked in the box there was no cat, then the cat would be a NaN since its state was outside of the domain of states for a cat in a box with a time release poison after the release of the poison, that is, the non-existance of a cat in the box precludes the possibility of the state of the cat in the box being either either dead or alive. -- 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
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/20 9:00 PM, Simon Slavin wrote: On 7 Mar 2020, at 1:52am, Xinyue Chen wrote: If I change IS NOT FALSE to IS TRUE, the results will be different. I assume they should perform in the same way? No. Because NULL is not TRUE and is not FALSE. SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT NULL IS TRUE; 0 sqlite> SELECT NULL IS FALSE; 0 sqlite> SELECT NULL IS NOT TRUE; 1 sqlite> SELECT NULL IS NOT FALSE; 1 Once you can have NULL values, you have to know the rules very well when you apply logic. Other values make sense, but NULL is not logical. It is sort of like NaN, where a Nan is neither less than, greater than or equal to any value, including itself. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/2020 8:52 PM, Xinyue Chen wrote: If I change IS NOT FALSE to IS TRUE, the results will be different. NULL IS TRUE is always false. NULL IS NOT FALSE is always true. So it's not surprising that the results are different. SQL uses trivalent logic. NULL is neither FALSE nor TRUE. I assume they should perform in the same way? You assume incorrectly. if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. False. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 7 Mar 2020, at 1:52am, Xinyue Chen wrote: > If I change IS NOT FALSE to IS TRUE, the results will be different. I > assume they should perform in the same way? No. Because NULL is not TRUE and is not FALSE. SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT NULL IS TRUE; 0 sqlite> SELECT NULL IS FALSE; 0 sqlite> SELECT NULL IS NOT TRUE; 1 sqlite> SELECT NULL IS NOT FALSE; 1 Once you can have NULL values, you have to know the rules very well when you apply logic. Other values make sense, but NULL is not logical. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
> If I change IS NOT FALSE to IS TRUE, the results will be different. I > assume they should perform in the same way? > if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should > also be always true. "NULL IS NOT FALSE" is true because NULL is not a value therefor it is not FALSE, because FALSE is a value. "NULL IS TRUE" is false because NULL is not a value so it's not TRUE. You can't compare NULL with anything. All you can do is tell if it "IS NULL" or "IS NOT NULL". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
Hi, If I change IS NOT FALSE to IS TRUE, the results will be different. I assume they should perform in the same way? if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. Then why doesn't that query also return 4 rows? Thanks! On Fri, Mar 6, 2020 at 5:45 PM Peter da Silva wrote: > Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no > value, you have to check for it explicitly. > > ___ > 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] Bug in SQLite version 3.31.1 and 3.32?
On Friday, 6 March, 2020 17:48 Xinyue Chen wrote: ... >select t1.textid a, i.intid b > from t t1, > i i > where (t1.textid = i.intid and t1.textid in (12) and t1.textid = i.intid) >or (t1.textid = null IS NOT FALSE) >group by i.intid, t1.textid; I got rid of all the extra brackets to make this easier to read. The where clause wants either (a bunch of and joined conditions) to be true OR (another condition to be true). We will ignore the first set of AND joined conditions since they appear to do what you want and instead deal with the handling of NULL values and tri-state logic from the second OR joined condition, which appears to be the one causing problems. The expression (t1.textid = null) is always null (it is neither True nor False), no matter what the value of t1.textid because any value compared to NULL is NULL. If you want to know whether t1.textid is null then you write "t1.textid is null" or (conversely) "t1.textid is not null" NULL is FALSE -> False (NULL is not False) NULL is TRUE -> False (NULL is not True either) NULL is not FALSE -> True (it is True that NULL is not FALSE) NULL is not TRUE -> True (it is True that NULL is not TRUE) Since the condition that you have specified (t1.textid = NULL) IS NOT FALSE will always be true, then the logic value of condition on the "other side" of the OR is irrelevant -- the WHERE clause will always be TRUE. This condition holds for any not null value you use in place of TRUE or FALSE. That is: NULL is 1 -> False (NULL is not 1) NULL is 0 -> False (NULL is not 0 either) NULL is not 1 -> True (NULL is indeed not 1) NULL is not 2 -> True (NULL is indeed not 2) This result will be the same if you change the IS NOT FALSE to IS NOT TRUE. However, if you specify IS TRUE or IS FALSE then this expression will always be FALSE and the value of the WHERE clause will depend on the result of the first set of AND joined conditions. So your original query must and always devolves to: select t1.textid a, i.intid b from t t1, i i group by i.intid, t1.textid; for which the correct results are produced. -- 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
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, you have to check for it explicitly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/20, Xinyue Chen wrote: > -- Buggy query > select t1.textid a, i.intid b > from t t1, > i i > where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid = > i.intid)) or ((t1.textid = null) IS NOT FALSE)) > group by i.intid, t1.textid; (1) The expression "t1.textid=null" is always NULL. (2) The expression "NULL IS NOT FALSE" is always true. (3) The WHERE clause expression "... OR true" is always true. Hence, the query above simplifies to just "SELECT * FROM t, i;". That query should return 4 rows, just as you show. I think it is working correctly. -- 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] Bug in SQLite version 3.31.1 and 3.32?
Hi, I am not sure if I found a bug in SQLite version 3.31.1 and 3.32. Could you please check? CREATE TABLE t ( textid TEXT ); INSERT INTO t VALUES ('12'); INSERT INTO t VALUES ('34'); CREATE TABLE i ( intid INTEGER PRIMARY KEY ); INSERT INTO i VALUES (12); INSERT INTO i VALUES (34); CREATE TABLE e ( x INTEGER PRIMARY KEY NOT NULL, y TEXTNOT NULL ); -- Original query select t1.textid a, i.intid b from t t1, i i where ((t1.textid = i.intid) and (t1.textid = 12)); -- Buggy query select t1.textid a, i.intid b from t t1, i i where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid = i.intid)) or ((t1.textid = null) IS NOT FALSE)) group by i.intid, t1.textid; The result for the original query is 12|12 but the result for the buggy one is 12|12, 34|12, 12|34, 34|34. If I change the IS NOT FALSE to IS TRUE, the result will be 12|12, same to the original query. Thanks! Best, Xinyue Chen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)
Thank you for the report. The problem is now fixed on trunk. Ticket: https://www.sqlite.org/src/info/c41afac34f15781f Fix: https://www.sqlite.org/src/info/523b42371122d9e1 On 5/29/19, Marco Foit wrote: > Dear SQLite Developers, > > I just noticed the following bug in SQLite version 3.28.0: > > > > create table t AS values (1), (2); > > .print "select with correct output ..." > select * from ( select * from t limit 1 ) > union all > select * from t > ; > > .print "same select leads to incorrect result when used inside view ..." > create view v as > select * from ( select * from t limit 1 ) > union all > select * from t > ; > > select * from v; > > > > > * How to reproduce: > Run the attached SQL code from a shell with: > > sqlite3 < sqlite-bug.sql > > > * Expected result: > Both queries should yield the following output: > 1 > 1 > 2 > > * What did go wrong: > The second query yields to the output: > 1 > > > It seems that the limit clause in the compound select when used inside a > view is used for the overall result set and not for the subquery. > > > In the hope that this might help others. > Thank you very much for your hard work! > > > Cheers, > Marco > > > ___ > 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] Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)
Dear SQLite Developers, I just noticed the following bug in SQLite version 3.28.0: create table t AS values (1), (2); .print "select with correct output ..." select * from ( select * from t limit 1 ) union all select * from t ; .print "same select leads to incorrect result when used inside view ..." create view v as select * from ( select * from t limit 1 ) union all select * from t ; select * from v; * How to reproduce: Run the attached SQL code from a shell with: sqlite3 < sqlite-bug.sql * Expected result: Both queries should yield the following output: 1 1 2 * What did go wrong: The second query yields to the output: 1 It seems that the limit clause in the compound select when used inside a view is used for the overall result set and not for the subquery. In the hope that this might help others. Thank you very much for your hard work! Cheers, Marco ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in sqlite when
On 8/24/16, Sergey Shamshyn wrote: > - *PRAGMA journal_mode=OFF* (when not execute this pragma, all is ok). With journal_mode=OFF, the database may go corrupt if: (1) The program is ever terminated (ex: using SIGKILL) while the database is open. (2) If a COMMIT statement ever fails due to an I/O or out-of-memory error. (3) Failure of a constraint during an UPDATE (depending on the schema). (4) Calling sqlite3_close() while a transaction is active. In other words, without a journal, there are many ways to corrupt the database file that do not involve power failures or I/O errors. -- 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] bug in sqlite when
Hi, Sergey, On Wed, Aug 24, 2016 at 8:55 AM, Sergey Shamshyn wrote: > Hi. > I have a big trouble using SQLite: got an error SQLITE_CORRUPT (11), I think > this is a 100% SQLite bug, because: > - only ONE THREAD of my process is writing periodically to db file > - onlt ONE ANOTHER THREAD of my another process reads from this db file > - version 3.14.1 > - PRAGMA synchronous=OFF > - *PRAGMA journal_mode=OFF* (when not execute this pragma, all is ok). > Looks like bug in PRAGMA journal_mode=OFF, because *only one thread is > writing to DB*! > - No power crashes, or chkdsk errors on this HDD. > - In attachment example of corrupted DB This list does not accept attachments. Can you put the DB somewhere on the file sharing service and put the link here? Thank you. > > > > ___ > 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] bug in sqlite when
Hi. I have a big trouble using SQLite: got an error SQLITE_CORRUPT (11), I think this is a 100% SQLite bug, because: - only ONE THREAD of my process is writing periodically to db file - onlt ONE ANOTHER THREAD of my another process reads from this db file - version 3.14.1 - PRAGMA synchronous=OFF - *PRAGMA journal_mode=OFF* (when not execute this pragma, all is ok). Looks like bug in PRAGMA journal_mode=OFF, because *only one thread is writing to DB*! - No power crashes, or chkdsk errors on this HDD. - In attachment example of corrupted DB ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite 3.8.11.1 source code
Hello ! This is a real simple bug fix but it seems that no one is caring about it !!! Cheers ! > Sat Sep 12 2015 8:52:04 pm CEST CEST from "chris0e3" >Subject: [sqlite] Bug in SQLite 3.8.11.1 source code > > Hello, > > I was just looking at updating to SQLite 3.8.11.1 when I spotted what >appears to be an error. > Here?s a patch to fix it: > > --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 >+0100 > +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 > @@ -92265,7 +92265,7 @@ > } > pParse->checkSchema = 1; > } > -#if SQLITE_USER_AUTHENICATION > +#if SQLITE_USER_AUTHENTICATION > else if( pParse->db->auth.authLevel sqlite3ErrorMsg(pParse, "user not authenticated"); > p = 0; > > > I think it?s pretty self explanatory. > > Regards, > > CHRIS > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Bug in SQLite 3.8.11.1 source code
On Sep 14, 2015 6:48 AM, "Domingo Alvarez Duarte" wrote: > > Hello ! > > This is a real simple bug fix but it seems that no one is caring about it !!! It is Monday morning. The report is two days old and thus came in on a weekend. I think your overreaction may be premature. I'm sure someone will address it Real Soon Now if they haven't already. > > > Cheers ! > > Sat Sep 12 2015 8:52:04 pm CEST CEST from "chris0e3" < chris0e3 at gmail.com> > >Subject: [sqlite] Bug in SQLite 3.8.11.1 source code > > > > Hello, > > > > I was just looking at updating to SQLite 3.8.11.1 when I spotted what > >appears to be an error. > > Here?s a patch to fix it: > > > > --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 > >+0100 > > +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 > > @@ -92265,7 +92265,7 @@ > > } > > pParse->checkSchema = 1; > > } > > -#if SQLITE_USER_AUTHENICATION > > +#if SQLITE_USER_AUTHENTICATION > > else if( pParse->db->auth.authLevel > sqlite3ErrorMsg(pParse, "user not authenticated"); > > p = 0; > > > > > > I think it?s pretty self explanatory. > > > > Regards, > > > > CHRIS > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite 3.8.11.1 source code
Hello, I was just looking at updating to SQLite 3.8.11.1 when I spotted what appears to be an error. Here?s a patch to fix it: --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 +0100 +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 @@ -92265,7 +92265,7 @@ } pParse->checkSchema = 1; } -#if SQLITE_USER_AUTHENICATION +#if SQLITE_USER_AUTHENTICATION else if( pParse->db->auth.authLevel
Re: [sqlite] Bug in SQLite FLOAT values
A double precision floating point value contains about 14.5 to 16 digits of precision WHEN CONVERTED FROM BINARY TO DECIMAL TEXT. However, the double precision number is merely the closest binary approximation of a value as can be encoded in BINARY (base 2) format. Exact DEMAL (base 10) values cannot be represented in binary (base 2) floating point. When you "round off" a floating point value to contain, as you put it, merely 6 digits of precision, what you are doing is throwing away some approximation data and making the value a less precise approximation of your diddled value -- you make it impossible to access the original value or to repeat the rerounding and achieve the same result. You are converting a value which is the best approximation of the value to a worse approximation of a value. That you only want to see 6 digits of displayed resolution is a display problem, not a value storage and retrieval problem. You are doing the equivalent of taking a beautiful 4096x2048 32-bit CMYK image and compressing it to a 16 KByte JPEG of postage stamp size (which it does by throwing away information such that it becomes permanently inaccessible and permanently irretrievable). When you then "blow up" the JPEG back to the original size you are greeted by pixelated eye-bleed causing crap that bears no resemblance whatsoever to the original data. The same applies to non-lossless audio compression formats as well (nay, all digital audio formats, truth be told). Compressing an analogue signal into a 128kbit mpeg stream (or any other non-lossless format) permanently destroys data content, making it impossible to retrieve the original data (and in the case of audio, leaving only crap that a tone deaf moron using "cheap tinkle" audio equipment could stand -- it makes the rest of us bleed out our ears). So too is "diddling" with double precision floating point numbers causing irretrievable and irreperable damage to the data values contained in them. You are confusing the "data value" (in this case the floating point value) with the display representation for biots (text strings in base-10 decimal representation). Once converted, you are throwing away information which you will never be able to recover ever again. Store and work with ONLY the double value that is provided to you without any tinkering. If you BIOTs require displaying only six digits of precision, then display it to them that way. Do not confuse BIOT display and BIOT input requirements with the internal representation of the data inside a digital computer system. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Abdul Aziz >Sent: Saturday, 7 February, 2015 07:22 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Bug in SQLite FLOAT values > >OK, understood, thanks but can anyone explain me I was creating db of >different sensors, I used same methods, but in fields which were FLOAT >were >filling with junk values (after 6 decimal places, see in SENSOR_1) why? >even I was cutting it to 6 decimal places (as shown previously, was then >again converting into FLOAT before insertion), when I used VARCHAR(TEXT), >in different table with same methods, then was getting correct >(formatted, >upto 6 decimal places, see in latitude, longitude) values in SENSOR_99, >please have a look... > >Thank you very much :) > >On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik >wrote: > >> On 2/7/2015 8:47 AM, Abdul Aziz wrote: >> >>> Thanks for replybut I am now using VARCHARS, then how this is >working? >>> not generating any errors? >>> >> >> When Tim said "Read this", he meant it. http://www.sqlite.org/ >> datatype3.html answers your questions (but only if you read it). >> -- >> Igor Tandetnik >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 7 Feb 2015, at 2:22pm, Abdul Aziz wrote: > but in fields which were FLOAT were > filling with junk values (after 6 decimal places, see in SENSOR_1) why? Take a look at what happens when you try to write 1/13th in decimal: 0.076923076923076923076923076923076923076923076923076923. What about 7/9ths: 0.77. And PI: 3.1415926535897932384626443323279502. (I forget the rest) There's no way to write those numbers precisely in decimal. The first repeats after the sixth place. The second repeats after the first place. The third never repeats, but also never stops. Similarly there's no way to write some numbers precisely in binary. So if I tell you you had to write the number using just digits and a decimal point, you can't do it. Similarly, if you convert some numbers into binary format and back (just 0 and 1 and a decimal point) you don't get back precisely the number you put in. Fields defined as FLOAT (in your Android API) have their values stored in binary format. So storing a number in that field involves converting it to binary. If, instead, you define your fields as text no conversion takes place because there's no need to turn the number into binary format. (Above explanation simplified with respect to affinities and deep maths for simplicity.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
OK, understood, thanks but can anyone explain me I was creating db of different sensors, I used same methods, but in fields which were FLOAT were filling with junk values (after 6 decimal places, see in SENSOR_1) why? even I was cutting it to 6 decimal places (as shown previously, was then again converting into FLOAT before insertion), when I used VARCHAR(TEXT), in different table with same methods, then was getting correct (formatted, upto 6 decimal places, see in latitude, longitude) values in SENSOR_99, please have a look... Thank you very much :) On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik wrote: > On 2/7/2015 8:47 AM, Abdul Aziz wrote: > >> Thanks for replybut I am now using VARCHARS, then how this is working? >> not generating any errors? >> > > When Tim said "Read this", he meant it. http://www.sqlite.org/ > datatype3.html answers your questions (but only if you read it). > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
Why would an application need to use the SQLite printf function to convert doubles to formatted text? The application ought to store and retrieve the raw doubles completely unadulterated (with no diddling, using the value_double and bind_double interfaces), and "format the value for display" when it is displayed. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Kees Nuyt >Sent: Saturday, 7 February, 2015 18:14 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Bug in SQLite FLOAT values > >On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz >wrote: > >> Hi there! >> I hope you are well! >> >> Recently I was working on project based on Android Sensors and >encountered >> a bug in sqlite db, situation was this: >> I was setting there three values x,y,z as FLOAT, android inbuilt >sensors >> were receiving values as float upto 8 decimal places, but I wanted to >store >> value only upto 6 decimal place, so in android this is the way that >first >> you will have to convert that value into String , as* String sLongitude >= >> String.format("%.6f", x);* > >As others have said, you shouldn't confuse the storage >format (how a value is stored in the database) with >the presentation (how data is displayed on output). > >Luckily, recently sqlite got a printf() function. > >Demo: > >$ sqlite3 test.db >SQLite version 3.8.8 2015-01-30 20:59:27 >Enter ".help" for usage hints. >sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL); >sqlite> insert into t3 (id,lat,lon) VALUES >(1,1.234567890123,5.6789012345678); >sqlite> select printf('id:%3d, latitude: %9.6f, longitude: >%9.6f',id,lat,lon) from t3; >id: 1, latitude: 1.234568, longitude: 5.678901 >sqlite> > >Hope this helps. > >-- >Regards, > >Kees Nuyt > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz wrote: > Hi there! > I hope you are well! > > Recently I was working on project based on Android Sensors and encountered > a bug in sqlite db, situation was this: > I was setting there three values x,y,z as FLOAT, android inbuilt sensors > were receiving values as float upto 8 decimal places, but I wanted to store > value only upto 6 decimal place, so in android this is the way that first > you will have to convert that value into String , as* String sLongitude = > String.format("%.6f", x);* As others have said, you shouldn't confuse the storage format (how a value is stored in the database) with the presentation (how data is displayed on output). Luckily, recently sqlite got a printf() function. Demo: $ sqlite3 test.db SQLite version 3.8.8 2015-01-30 20:59:27 Enter ".help" for usage hints. sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL); sqlite> insert into t3 (id,lat,lon) VALUES (1,1.234567890123,5.6789012345678); sqlite> select printf('id:%3d, latitude: %9.6f, longitude: %9.6f',id,lat,lon) from t3; id: 1, latitude: 1.234568, longitude: 5.678901 sqlite> Hope this helps. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 7 Feb 2015, at 5:57am, Abdul Aziz wrote: > *again I converted back to float this string formatted value, and printed > into Log, I was clearly seeing values upto 6 decimal places , but after > insertion into sqlite db, when after generation of sqlite db file, was > getting values upto 11-12 decimal places!* SQLite itself would have converted your text (to six digits) and stored the resulting number. When you asked to retrieve your value, SQLite would have retrieved that number -- still at six digits. However if you ask for the retrieved figure as a number (as opposed to a string) the programming language you use has to put the resulting number into a 'float' variable. And in doing this it would have to turn the number back into float format, which would introduce the extra 'garbage' digits. So yes, you can argue that there is a bug somewhere, but if there is one it's in the Android interface to SQLite, not in SQLite itself. You can avoid this by asking for the retrieved figure as a string, not a number. Or by storing the value as a TEXT column instead of a REAL column, which is the solution you came up with. So I'm glad you found a solution. It might be worth asking yourself why you are trimming your values to six digits and then saving the result as a number. It would make more sense to trim your values and then handle the number as a string from then onwards. Or to handle all the digits you have and to convert to text as six digits just before you put the number on the display. Both of these would be more mathematically 'correct' than what you are doing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 2015/02/07 15:47, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? Please elaborate, my query to create DB is: mSQLiteDatabase.execSQL("CREATE TABLE " + tableName + " ( " + EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, " + ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z + " VARCHAR );"); It translates Varchar to Text, that's why no errors are generated, it understands what you mean by Varchar, which is really just some text, so it translates it to the internal type TEXT. As for your question about decimal points and floats, no float in any language stores values up to a certain length... lengths are the domain of Strings and text, not Floating point numbers. any floating point number is an approximate number with a representation as close as is possible to the actual number. That representation includes many significant digits in the significand and an exponent. You may need to read up on floats some more to see how it works - my point is just that it doesn't store numbers up to a certain length, for that you need a formatter. Many DB engines offer formatted types, such as Decimal (in PG, Oracle, MSSQL, etc) where you can say you need the number with so many decimals after the point. In SQLite you can format the output (much like your C solution) by doing SELECT printf('%.6f',somevalue); etc. Read the pages offered by the other posters and maye check out the Wikipedia pages on floating point storage and representation to understand WHY all the above happens, but to solve your immediate problem, use the output formatting or store as strings - there is no way to tell a true floating number to keep itself short. Other interesting things you can see about this floating point problem (it's a mathematical problem too), look on youtube for "Why is 0.9... equal to 1?" or "How do we know two numbers are distinct?" - The Numberphile videos in general do a good job of explaining it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 2/7/2015 8:47 AM, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? When Tim said "Read this", he meant it. http://www.sqlite.org/datatype3.html answers your questions (but only if you read it). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? Please elaborate, my query to create DB is: mSQLiteDatabase.execSQL("CREATE TABLE " + tableName + " ( " + EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, " + ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z + " VARCHAR );"); On Sat, Feb 7, 2015 at 7:08 PM, Tim Streater wrote: > On 07 Feb 2015 at 05:57, Abdul Aziz wrote: > > > *I think this is a bug, this means float values in sqlite will always be > > filled upto 11-12 decimal places, in any case, you will have to fill it, > or > > sqlite will fill it itself with junk values, **this may create lot of > > consumption of memory while working on larger projects...* > > Read this: > > http://www.sqlite.org/datatype3.html > > Note that: > > 1) There are no varchars in SQLite > > 2) Floats always occupy 8 bytes > > -- > Cheers -- Tim > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite FLOAT values
On 07 Feb 2015 at 05:57, Abdul Aziz wrote: > *I think this is a bug, this means float values in sqlite will always be > filled upto 11-12 decimal places, in any case, you will have to fill it, or > sqlite will fill it itself with junk values, **this may create lot of > consumption of memory while working on larger projects...* Read this: http://www.sqlite.org/datatype3.html Note that: 1) There are no varchars in SQLite 2) Floats always occupy 8 bytes -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite FLOAT values
Hi there! I hope you are well! Recently I was working on project based on Android Sensors and encountered a bug in sqlite db, situation was this: I was setting there three values x,y,z as FLOAT, android inbuilt sensors were receiving values as float upto 8 decimal places, but I wanted to store value only upto 6 decimal place, so in android this is the way that first you will have to convert that value into String , as* String sLongitude = String.format("%.6f", x);* *again I converted back to float this string formatted value, and printed into Log, I was clearly seeing values upto 6 decimal places , but after insertion into sqlite db, when after generation of sqlite db file, was getting values upto 11-12 decimal places!* *upto 6 decimal places were matching with my values and rest were garbage values...* *Then to avoid this I converted from FLOAT to VARCHAR into DB, and inserted that formatted values (**String sLongitude = String.format("%.6f", x)**)* *now I was getting correct formatted values from sqlite db file,* *I think this is a bug, this means float values in sqlite will always be filled upto 11-12 decimal places, in any case, you will have to fill it, or sqlite will fill it itself with junk values, **this may create lot of consumption of memory while working on larger projects...* *Thanks, waiting for a reply* *Abdul Aziz Ansari* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite FLOAT values
Hi there! I hope you are well! Recently I was working on project based on Android Sensors and encountered a bug in sqlite db, situation was this: I was setting there three values x,y,z as FLOAT, android inbuilt sensors were receiving values as float upto 8 decimal places, but I wanted to store value only upto 6 decimal place, so in android this is the way that first you will have to convert that value into String , as* String sLongitude = String.format("%.6f", x);* *again I converted back to float this string formatted value, and printed into Log, I was clearly seeing values upto 6 decimal places , but after insertion into sqlite db, when after generation of sqlite db file, was getting values upto 11-12 decimal places!* *upto 6 decimal places were matching with my values and rest were garbage values...* *Then to avoid this I converted from FLOAT to VARCHAR into DB, and inserted that formatted values (**String sLongitude = String.format("%.6f", x)**)* *now I was getting correct formatted values from sqlite db file,* *I think this is a bug, this means float values in sqlite will always be filled upto 11-12 decimal places, in any case, you will have to fill it, or sqlite will fill it itself with junk values, **this may create lot of consumption of memory while working on larger projects...* *Thanks, waiting for a reply* *Abdul Aziz Ansari* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite? Can't read tables just after creating them
On 11 Mar 2014, at 11:31am, Fabrice Triboix wrote: > The problem is actually elsewhere. I changed the filesystem and it works > fine. > > So the problem is not with sqlite but with our special filesystem. Make sure your code tests the results returned by all API calls to see that they are returning SQLITE_OK where appropriate. Many times, the call which you realise is returning the wrong thing is after the call which created the problem. But I'm glad to see you have it working. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite? Can't read tables just after creating them
Hi Richard, The problem is actually elsewhere. I changed the filesystem and it works fine. So the problem is not with sqlite but with our special filesystem. Sorry for having raised the alarm too quickly! Best regards, Fabrice -Original Message- From: Richard Hipp Sender: sqlite-users-bounces@sqlite.orgDate: Tue, 11 Mar 2014 07:27:37 To: General Discussion of SQLite Database Reply-To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug in sqlite? Can't read tables just after creating them On Tue, Mar 11, 2014 at 6:35 AM, wrote: > Hi, > > I found a problem in sqlite. > > In essence, here is what my code does: > 1 - It opens a database file > 2 - If it doesn't find certain tables, it assumes this is a new one and > creates the necessary tables and add a few entries in one of them (let's > call it mytable) > 3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;' > > When I run this code on Debian, I don't have any problem. > > When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such > table: mytable'. I found out that closing and then re-opening the database > file works. So I do steps 1 and 2, and close and re-open the database > file, and now step 3 works fine! > > For the time being, I have this workaround of closing/re-opening the > database file, but that's really a kludge. > > Any idea about from where this could come from? > No ideas. Please enable the error and warning log ( http://www.sqlite.org/errlog.html) and see if that provides any further information. > > Many thanks for any ideas! > > Fabrice > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite? Can't read tables just after creating them
On Tue, Mar 11, 2014 at 6:35 AM, wrote: > Hi, > > I found a problem in sqlite. > > In essence, here is what my code does: > 1 - It opens a database file > 2 - If it doesn't find certain tables, it assumes this is a new one and > creates the necessary tables and add a few entries in one of them (let's > call it mytable) > 3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;' > > When I run this code on Debian, I don't have any problem. > > When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such > table: mytable'. I found out that closing and then re-opening the database > file works. So I do steps 1 and 2, and close and re-open the database > file, and now step 3 works fine! > > For the time being, I have this workaround of closing/re-opening the > database file, but that's really a kludge. > > Any idea about from where this could come from? > No ideas. Please enable the error and warning log ( http://www.sqlite.org/errlog.html) and see if that provides any further information. > > Many thanks for any ideas! > > Fabrice > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite? Can't read tables just after creating them
Hi, I found a problem in sqlite. In essence, here is what my code does: 1 - It opens a database file 2 - If it doesn't find certain tables, it assumes this is a new one and creates the necessary tables and add a few entries in one of them (let's call it mytable) 3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;' When I run this code on Debian, I don't have any problem. When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such table: mytable'. I found out that closing and then re-opening the database file works. So I do steps 1 and 2, and close and re-open the database file, and now step 3 works fine! For the time being, I have this workaround of closing/re-opening the database file, but that's really a kludge. Any idea about from where this could come from? Many thanks for any ideas! Fabrice ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.8.2
Thanks for the bug report. Ticket: http://www.sqlite.org/src/info/c34d0557f740c45070 Fixed here: http://www.sqlite.org/src/info/5d01426ddf On Wed, Feb 12, 2014 at 5:31 AM, Paweł Salawa wrote: > Hi, > > The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions. > > *Preconditions:* > > - 2 databases: A and B. > > - database A has table "test": > CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID > > - database B has table "test2": > CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER) > > > *To reproduce bug:* > > - open database A and attach database B: > ATTACH 'database_b.db' AS 'attached'; > > - execute query: > select test.*, t2.ROWID from attached.test2 t2, test > > SQLite says: *no such column: t2.ROWID* > > Weird thing is that when you switch test2 and test table positions with > each other, the same query will work: > select test.*, t2.ROWID from test, attached.test2 t2 > > *^^^ this works just fine.* > > Problem occurres only if following conditions are met: > - table in local database is WITHOUT ROWID > - table in attached database is a regular table with ROWID > - query selects ROWID from the regular table > - both tables must be mentioned in the FROM clause > - the WITHOUT ROWID table must be mentioned as the second one > > Regards, > -- > Paweł Salawa > pawelsal...@gmail.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.8.2
On Wed, 12 Feb 2014 11:31:05 +0100, Pawe? Salawa wrote: > - open database A and attach database B: > ATTACH 'database_b.db' AS 'attached'; This is not the main cause, but that should be: ATTACH 'database_b.db' AS attached; (attached should not be a literal but an identifier, just like table names and column names.) -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite 3.8.2
Hi, The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions. *Preconditions:* - 2 databases: A and B. - database A has table "test": CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID - database B has table "test2": CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER) *To reproduce bug:* - open database A and attach database B: ATTACH 'database_b.db' AS 'attached'; - execute query: select test.*, t2.ROWID from attached.test2 t2, test SQLite says: *no such column: t2.ROWID* Weird thing is that when you switch test2 and test table positions with each other, the same query will work: select test.*, t2.ROWID from test, attached.test2 t2 *^^^ this works just fine.* Problem occurres only if following conditions are met: - table in local database is WITHOUT ROWID - table in attached database is a regular table with ROWID - query selects ROWID from the regular table - both tables must be mentioned in the FROM clause - the WITHOUT ROWID table must be mentioned as the second one Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
Hi Simon, no its OK -- did not take anything personal. I think I stay with my SQL-statements... Thanks again for your hint, Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
On 25 Dec 2013, at 8:54am, Tobias Steinmann wrote: > thanks for your hint. I needed some time to figure out, how to use this > mailing-list and I hope, you will get my answer. > > I tried the same im MySQL and it worked -- I know, that is no reason I just > wanted to say. > > I also tried, to rename one of the depth's to depth1 -- and then it also > worked in SQLite so thanks for the hint. No problem. An easy mistake to make. And since you got your answer, you are using this mailing list well. > > Argh. An example where doing the work in your software rather > > than a huge SQL statement might be more understandable, > > both in your debugging and if anyone else ever has to read > > your code. Another way to simplify things might be to > > define your sub-select as a VIEW. > > This comment of your I did not understand what you mean, maybe because of my > bad english? Sorry. If I got you right, you suggested not to use such big > SQL-Statements? I see that they are not ideally to debug but on the other > hand I also dont understand, why I should make thinks simpler just to have > afterwards some things to do in my program? I thought, the SQL-System might > be faster in data-filtering than my application...? Sorry. It was nothing personal. I have made that comment many times to many people. Your SELECT was extremely complicated because you had to phrase your requirements in SQL. Sometimes doing the same work in your programming language leads to simpler code -- code which is easier to debug and sometimes even runs faster. Because a programming language can do things differently. But sometimes it doesn't, and the SQL phrasing is as good as you can get. Don't worry about it. Just something that annoys me personally. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
Hi Simon, thanks for your hint. I needed some time to figure out, how to use this mailing-list and I hope, you will get my answer. I tried the same im MySQL and it worked -- I know, that is no reason I just wanted to say. I also tried, to rename one of the depth's to depth1 -- and then it also worked in SQLite so thanks for the hint. > Argh. An example where doing the work in your software rather > than a huge SQL statement might be more understandable, > both in your debugging and if anyone else ever has to read > your code. Another way to simplify things might be to > define your sub-select as a VIEW. This comment of your I did not understand what you mean, maybe because of my bad english? Sorry. If I got you right, you suggested not to use such big SQL-Statements? I see that they are not ideally to debug but on the other hand I also dont understand, why I should make thinks simpler just to have afterwards some things to do in my program? I thought, the SQL-System might be faster in data-filtering than my application...? Best Regards, Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
On Mon, 23 Dec 2013 23:50:30 +0100 "E.Pasma" wrote: > > . See if you can make the simplest possible SELECT that comes up > > with unexpected results. > > select 0 as depth > from(select 1 as depth) > group by null > having depth < 1 > ; > This returns no rows. Thus the HAVING clause refers to the depth > from the FROM part, not that in the SELECT part. May this be what is > causing the unexpected result? Another good use for strict = ON! Column name aliases in the SELECT clause are properly unavailable to the rest of the query, including the GROUP BY clause. More simply, select 0 as depth from (select 1 as foo) as T where depth < 1; should be a syntax error but sqlite> select 0 as depth from (select 1 as foo) as T where depth < 1; depth -- 0 --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
Op 23 dec 2013, om 14:32 heeft Simon Slavin het volgende geschreven: General note: when making up a name for a calculation like 'depth', try to make sure it's not the name of any of the columns in the tables mentioned in your SELECT. This avoids ambiguity. . See if you can make the simplest possible SELECT that comes up with unexpected results. select 0 as depth from(select 1 as depth) group by null having depth < 1 ; This returns no rows. Thus the HAVING clause refers to the depth from the FROM part, not that in the SELECT part. May this be what is causing the unexpected result? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE regarding HAVING?
On 22 Dec 2013, at 11:12pm, Tobias Steinmann wrote: > SELECT node.id,node.lft, node.rgt, (COUNT(parent.id) - (sub_tree.depth + 1)) > AS depth FROM target_directory AS node, target_directory AS parent, > target_directory AS sub_parent, (SELECT node.id, (COUNT(parent.id) - 1) AS > depth FROM target_directory AS node, target_directory AS parent WHERE > node.lft BETWEEN parent.lft AND parent.rgt AND node.id = 1 GROUP BY node.id > ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND > parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND > sub_parent.id = sub_tree.id GROUP BY node.id HAVING depth***<=* 1 ORDER BY > node.lft ; Argh. An example where doing the work in your software rather than a huge SQL statement might be more understandable, both in your debugging and if anyone else ever has to read your code. Another way to simplify things might be to define your sub-select as a VIEW. General note: when making up a name for a calculation like 'depth', try to make sure it's not the name of any of the columns in the tables mentioned in your SELECT. This avoids ambiguity. > See the pic1_working.PNG for the result with the given Database. Sorry, attachments don't work on this list. We don't want everyone sending us their homework. > Problem: The result contains the parent-node also. So I changed last > statement "HAVING depth <= 1" to "HAVING depth<1" and the result of the query > is now empty (see pic2_not_working.png) -- expected would be one result set. Nothing obvious but try these: (A) remove the ORDER BY clauses and everything else you can think of and see whether you still get weird results. See if you can make the simplest possible SELECT that comes up with unexpected results. See if you can get weird results by substituting a specific value for the sub-SELECT. The smaller the SELECT the easier it is to see what's wrong. (B) check to see if you have NULLs anywhere. They tend to make things look weird. Hope some of that helps. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLITE regarding HAVING?
Hi, I have a strange behaviour of SQLITE with a HAVING-Clause and I don't find the problem. Possibly a bug in SQLITE? Following Query regarding a nested tree set to get all children of a node: SELECT node.id,node.lft, node.rgt, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth FROM target_directory AS node, target_directory AS parent, target_directory AS sub_parent, (SELECT node.id, (COUNT(parent.id) - 1) AS depth FROM target_directory AS node, target_directory AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = 1 GROUP BY node.id ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.id = sub_tree.id GROUP BY node.id HAVING depth***<=* 1 ORDER BY node.lft ; See the pic1_working.PNG for the result with the given Database. Problem: The result contains the parent-node also. So I changed last statement "HAVING depth <= 1" to "HAVING depth<1" and the result of the query is now empty (see pic2_not_working.png) -- expected would be one result set.I Sorry to ask that silly question: problem "sitting in front of keyboard" or in SQLITE? Thanks and Best Regards, Tobias SQLITE-Version is 3.7.17 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index
Thanks very much for the prompt action! One other simple workaround, if performance is not a concern, SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A || " " || T2.B || " " || T1.D || " " || T1.E || " " || T1.F || " " || T1.G || " " || T1.H; Performance is one of our big concerns so we will evaluate NGQP as soon as it comes out. Thanks again for the great work! Mi Chen mi.c...@echostar.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, June 05, 2013 6:21 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index On Tue, Jun 4, 2013 at 2:42 PM, Chen, Mi wrote: > All, I encountered a likely bug during development with latest SQL > versions (3.7.17)... It appears to be affecting the result of queries with > GROUP BY clause with partial join over two primary keys. > Your test case has been added here: http://www.sqlite.org/src/info/96afe50866 Your test works with the next-generation query planner (NGQP) but (as you observe) fails in 3.7.17. Probably this is due to one of the existing bug reports written against the ORDER BY optimizer in 3.7.17. The problem should be fixed when we cut over to the NGQP. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index
On Tue, Jun 4, 2013 at 2:42 PM, Chen, Mi wrote: > All, I encountered a likely bug during development with latest SQL > versions (3.7.17)... It appears to be affecting the result of queries with > GROUP BY clause with partial join over two primary keys. > Your test case has been added here: http://www.sqlite.org/src/info/96afe50866 Your test works with the next-generation query planner (NGQP) but (as you observe) fails in 3.7.17. Probably this is due to one of the existing bug reports written against the ORDER BY optimizer in 3.7.17. The problem should be fixed when we cut over to the NGQP. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index
All, I encountered a likely bug during development with latest SQL versions (3.7.17)... It appears to be affecting the result of queries with GROUP BY clause with partial join over two primary keys. SQL Version 3.7.14 does not have this behavior, and SQL Version 3.7.15 - 3.7.17 are all affected. This may have been fixed since 2013-05-30 builds... On the timeline builds, This version shows still having the bug http://www.sqlite.org/src/info/aebe1f2603 But this version and above does not: http://www.sqlite.org/src/info/001539df4b To recreate the scenario, run the following statements in the P.S. Section. I'm not familiar with the active development of NGQP, but it seems to be very exciting and I hope maybe this can be helpful for the test procedure before the release. Thanks in advance for identifying and fixing the bug. P.S.: Script to recreate the scenario (please see "--This Fails" section for detailed error output). CREATE TABLE T1 (B INTEGER NOT NULL, C INTEGER NOT NULL, D INTEGER NOT NULL, E INTEGER NOT NULL, F INTEGER NOT NULL, G INTEGER NOT NULL, H INTEGER NOT NULL, PRIMARY KEY (B, C, D)); CREATE TABLE T2 (A INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER NOT NULL, PRIMARY KEY (A, B, C)); INSERT INTO T2(A, B, C) VALUES(702118,16183,15527); INSERT INTO T2(A, B, C) VALUES(702118,16183,15560); INSERT INTO T2(A, B, C) VALUES(702118,16183,15561); INSERT INTO T2(A, B, C) VALUES(702118,16183,15563); INSERT INTO T2(A, B, C) VALUES(702118,16183,15564); INSERT INTO T2(A, B, C) VALUES(702118,16183,15566); INSERT INTO T2(A, B, C) VALUES(702118,16183,15567); INSERT INTO T2(A, B, C) VALUES(702118,16183,15569); INSERT INTO T2(A, B, C) VALUES(702118,16183,15612); INSERT INTO T2(A, B, C) VALUES(702118,16183,15613); INSERT INTO T2(A, B, C) VALUES(702118,16183,15638); INSERT INTO T2(A, B, C) VALUES(702118,16183,15681); INSERT INTO T2(A, B, C) VALUES(702118,16183,15682); INSERT INTO T1(B, C, D, E, F, G, H) INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15527,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15560,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15561,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15563,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15564,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15566,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15567,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15569,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15612,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15613,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15638,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15681,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15682,6,0,5,2,0); -- This fails as it does not give me unique results grouped by the criteria SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H; -- Results: -- 702118|16183|6|0|5|2|0|15569 -- 702118|16183|6|0|5|5|0|15612 -- 702118|16183|6|0|5|2|0|15638 -- 702118|16183|6|0|5|5|0|15681 -- 702118|16183|6|0|5|2|0|15682 -- Somehow this always Succeeds, I changed the group-by order so it is more sensitive to the data... SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A, T2.B, T1.F, T1.D, T1.E, T1.G, T1.H; -- Results: -- 702118|16183|6|0|5|2|0|15682 -- 702118|16183|6|0|5|5|0|15681 Mi Chen mi.c...@echostar.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQlite .NET Version 1.0.82.0
Michael Endres wrote: > > After performing Database Actions, we used > SQLiteConnection Close() to close the database connection. > > Afterwards, I use File.ReadAllBytes() on the Database File, which lead to a > Access Violation because the file was still opened in a different process. > When we switched back to Version 1.0.80.0,and it worked again. > > It seems like the filestream is not closed, when using SqliteConnection Close(). > As of version 1.0.82.0, the underlying native database connection is not completely closed until *ALL* outstanding statements are finalized and all backups are finished. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQlite .NET Version 1.0.82.0
Dear Community, I found a bug/different behavior in your current Sqlite .NET Version 1.0.82.0: After performing Database Actions, we used SQLiteConnection Close() to close the database connection. Afterwards, I use File.ReadAllBytes() on the Database File, which lead to a Access Violation because the file was still opened in a different process. When we switched back to Version 1.0.80.0,and it worked again. It seems like the filestream is not closed, when using SqliteConnection Close(). Is there maybe a new function to perform this action, or is there some bug in it? I tried this with the following Sqlite Version: Version: 1.0.82.0 Platform: x86 .NET Version: 3.5 SP1 Greetings, Michael Endres ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG in SQLite? Still the rowid question
Am 30.08.2009 um 17:56 schrieb Kit: > 2009/8/29 Wanadoo Hartwig : >> The largest and last row id is 4. Why is SQLite returning 5? I think >> it has to do with the FTS3 module but still the trigger statement >> should shield the row ids from the trigger statement, or? >> Hartwig > > CREATE TABLE Simple (ID integer primary key, Name text); > CREATE TABLE SimpleFTS (Name); > CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW > BEGIN > DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); > END; > CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW > BEGIN > INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); > END; > INSERT INTO Simple (Name) VALUES('one'); > INSERT INTO Simple (Name) VALUES('two'); > DELETE FROM Simple WHERE (ID = 1); > INSERT INTO Simple (Name) VALUES('three'); > SELECT * FROM Simple; > 2|two > 3|three > SELECT last_insert_rowid() FROM Simple; > 3 > 3 > > Perfect. > I know. Therefore, you have to use FTS3. BTW: RTree seems to work. > sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name); > SQL error: no such module: FTS3 > > What's FTS3? http://dotnetperls.com/sqlite-fts3 ? > FTS3 is the built-in full-text search engine. You have to compile SQLite with SQLITE_ENABLE_FTS3. > Virtual tables are a new feature in SQLite (currently still only > available from the development version on CVS) > -- > Kit > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG in SQLite? Still the rowid question
2009/8/29 Wanadoo Hartwig : > The largest and last row id is 4. Why is SQLite returning 5? I think > it has to do with the FTS3 module but still the trigger statement > should shield the row ids from the trigger statement, or? > Hartwig CREATE TABLE Simple (ID integer primary key, Name text); CREATE TABLE SimpleFTS (Name); CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END; CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END; INSERT INTO Simple (Name) VALUES('one'); INSERT INTO Simple (Name) VALUES('two'); DELETE FROM Simple WHERE (ID = 1); INSERT INTO Simple (Name) VALUES('three'); SELECT * FROM Simple; 2|two 3|three SELECT last_insert_rowid() FROM Simple; 3 3 Perfect. sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name); SQL error: no such module: FTS3 What's FTS3? http://dotnetperls.com/sqlite-fts3 ? Virtual tables are a new feature in SQLite (currently still only available from the development version on CVS) -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG in SQLite? Still the rowid question
Hi, last time I have posted this bug(?) using an C-API. Here is the same bug(?) using only SQL statements: CREATE TABLE Simple (ID integer primary key, Name text); CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name); CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END; CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END; INSERT INTO Simple (Name) VALUES('one'); INSERT INTO Simple (Name) VALUES('two'); DELETE FROM Simple WHERE (ID = 1); INSERT INTO Simple (Name) VALUES('three'); SELECT * FROM Simple; SELECT last_insert_rowid() FROM Simple; The output is: 2|two 3|three 4|four 5 <-- BUG?! 5 5 The largest and last row id is 4. Why is SQLite returning 5? I think it has to do with the FTS3 module but still the trigger statement should shield the row ids from the trigger statement, or? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned
Hi Simon, I am not complaining about the autoincrement's result but that sqlite3_insert_rowid returns the wrong rowid. Hartwig Am 29.06.2009 um 23:09 schrieb Simon Slavin: > > On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote: > >> I have posted a while ago a bug (at least I think that it is a bug) >> but only in a very abstract form. Now, I have written a C-program >> showing the bug. > > I assume that if you type those commands into the sqlite3 command-line > tool, you get the same result. As to the use of rowid, do your > results agree with > > http://www.sqlite.org/autoinc.html > > ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned
On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote: > I have posted a while ago a bug (at least I think that it is a bug) > but only in a very abstract form. Now, I have written a C-program > showing the bug. I assume that if you type those commands into the sqlite3 command-line tool, you get the same result. As to the use of rowid, do your results agree with http://www.sqlite.org/autoinc.html ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned
Hi, actually, I also tested it with 3.6.16 and the bug still exists. Hartwig Am 29.06.2009 um 21:54 schrieb Wanadoo Hartwig: > Hello, > > I have posted a while ago a bug (at least I think that it is a bug) > but only in a very abstract form. Now, I have written a C-program > showing the bug. > > Brief description: > > An FTS related trigger combination leads to a wrong determination of > the last inserted row id. Triggers that are not related to FTS are > working fine. > > > #include > #include > > #include "sqlite3.h" > > void OnErrorExit(sqlite3* database, int rc, char* errorMessage) > { > if (rc != SQLITE_OK) > { > if (errorMessage != NULL) > { > fprintf(stderr,"SQL error: %s\n",errorMessage); > sqlite3_free(errorMessage); > } /* if */ > sqlite3_close(database); > exit(1); > } /* if */ > } > > void ShowDatabaseContents(sqlite3* database) > { > char* errorMessage; > char** result; > char* sqlStatement; > > int noOfColumns, noOfRows; > int rc; > > sqlite3_int64 lastInsertedID; > > > lastInsertedID = sqlite3_last_insert_rowid(database); > printf("Last inserted ID: %d\n",(int) lastInsertedID); > sqlStatement = "SELECT * FROM Simple;"; > rc = > sqlite3_get_table > (database,sqlStatement,&result,&noOfRows,&noOfColumns,&errorMessage); > OnErrorExit(database,rc,errorMessage); > > printf("Number of rows: %d\n",noOfRows); > printf("Number of columns: %d\n",noOfColumns); > for (int i=0; i { > printf("Row: %2d ",i); > for (int j=0; j printf(" Column[%d]: %s",j,result[i*noOfColumns+j]); > printf("\n"); > } /* for */ > } > > int main (int argc, const char * argv[]) > { > char* errorMessage; > char* sqlStatement; > > int rc; > > sqlite3* database; > > > // prepare database > rc = sqlite3_open(NULL,&database); > if (rc != SQLITE_OK) > { > fprintf(stderr,"Can't open database in memory: %s > \n",sqlite3_errmsg(database)); > exit(1); > } /* if */ > sqlStatement = "CREATE TABLE Simple (ID integer primary key, Name > text);"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > sqlStatement = "CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > sqlStatement = "CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple > FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > sqlStatement = "CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple > FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES > (NEW.ID,NEW.Name); END;"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > // insert and delete items > sqlStatement = "INSERT INTO Simple (Name) VALUES('one');"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > sqlStatement = "INSERT INTO Simple (Name) VALUES('two');"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > ShowDatabaseContents(database); > sqlStatement = "DELETE FROM Simple WHERE (ID = 1);"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > ShowDatabaseContents(database); > sqlStatement = "INSERT INTO Simple (Name) VALUES('one');"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > ShowDatabaseContents(database); > sqlStatement = "INSERT INTO Simple (Name) VALUES('one');"; > rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage); > OnErrorExit(database,rc,errorMessage); > ShowDatabaseContents(database); > > sqlite3_close(database); > > printf("Done!\n"); > return 0; > } > > This is the output after the last insert: > > Last inserted ID: 5 > Number of rows: 3 > Number of columns: 2 > Row: 0 Column[0]: ID Column[1]: Name > Row: 1 Column[0]: 2 Column[1]: two > Row: 2 Column[0]: 3 Column[1]: one > Row: 3 Column[0]: 4 Column[1]: one > > Actually, I would expect this: > > Last inserted ID: 4 > Number of rows: 3 > Number of columns: 2 > Row: 0 Column[0]: ID Column[1]: Name > Row: 1 Column[0]: 2 Column[1]: two > Row: 2 Column[0]: 3 Column[1]: one > Row: 3 Column[0]: 4 Column[1]: one > > Interestingly the output is like a like to have it but then I have to > use any trigger but not an FTS related trigger! > > Hartwig > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-
[sqlite] Bug in SQLite 3.6.14 / wrong rowid returned
Hello, I have posted a while ago a bug (at least I think that it is a bug) but only in a very abstract form. Now, I have written a C-program showing the bug. Brief description: An FTS related trigger combination leads to a wrong determination of the last inserted row id. Triggers that are not related to FTS are working fine. #include #include #include "sqlite3.h" void OnErrorExit(sqlite3* database, int rc, char* errorMessage) { if (rc != SQLITE_OK) { if (errorMessage != NULL) { fprintf(stderr,"SQL error: %s\n",errorMessage); sqlite3_free(errorMessage); } /* if */ sqlite3_close(database); exit(1); } /* if */ } void ShowDatabaseContents(sqlite3* database) { char* errorMessage; char** result; char* sqlStatement; int noOfColumns, noOfRows; int rc; sqlite3_int64 lastInsertedID; lastInsertedID = sqlite3_last_insert_rowid(database); printf("Last inserted ID: %d\n",(int) lastInsertedID); sqlStatement = "SELECT * FROM Simple;"; rc = sqlite3_get_table (database,sqlStatement,&result,&noOfRows,&noOfColumns,&errorMessage); OnErrorExit(database,rc,errorMessage); printf("Number of rows: %d\n",noOfRows); printf("Number of columns: %d\n",noOfColumns); for (int i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE? "Joins + Order By" Changing row count!?!
Create an sql dump using the ".dump" command of the sqlite3 shell tool: $ echo .dump | sqlite3 database_file.db > dump.sql Or just put the database file up for download somewhere. Or if you prefer, send it to me by email. If this bug is present in current versions, we need to fix it. But it's much more difficult to figure out if it is still present or not without a database to run your queries against. Thanks, Dan. On Mar 4, 2009, at 9:17 PM, Jonathon wrote: > Thanks Dan for the reply, > > How would I go about creating a sql dump? > > As for the ORDER BY clause, I do this: > > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; > > Although a.some_other_value is not displayed in the tableA below, I > forgot > to add it. So, I add the ORDER BY clause on a.some_other_value and it > returns a ton of records (mostly duplicates). I am pretty sure this > is a > bug because if I wrap this query inside of a subquery and perform > the ORDER > BY on the subquery, everything works. > > This is what WORKS: > > SELECT * FROM ( > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; > > So for now, I am just leaving my original query inside the subquery > to fix > it. However, I wouldn't mind posting some debugging information if > it will > help fix the bug. > > Thanks, > J > > > On Tue, Mar 3, 2009 at 8:23 PM, Dan wrote: > >> >> On Mar 4, 2009, at 4:41 AM, Jonathon wrote: >> >>> Hello, >>> >>> I am executing a query such as: >> >> Can you post a database (or sql dump thereof) to run your queries >> against? Also say exactly what ORDER BY clause you are adding to >> the end of the query that causes it to return incorrect results? >> >> Thanks, >> Dan. >> >> >> >>> 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c >>> INNER >>> JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id >>> AND >>> a.d_id = d.id AND c.e_id = e.id; >>> 2. >>> 3. CREATE TABLE tableA ( >>> 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 5. b_idINTEGER NOT NULL, >>> 6. c_idINTEGER NOT NULL, >>> 7. d_idINTEGER NOT NULL >>> 8. ) >>> 9. >>> 10. CREATE TABLE tableB ( >>> 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 12. ); >>> 13. >>> 14. CREATE TABLE tableC ( >>> 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 16. e_idINTEGER NOT NULL >>> 17. ); >>> 18. >>> 19. CREATE TABLE tableD ( >>> 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 21. ); >>> 22. >>> 23. CREATE TABLE tableE ( >>> 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 25. ); >>> >>> >>> This seems to be returning the correct records. However, when I >>> append an >>> ORDER BY at the end of the query, it seems to be changing the number >>> of >>> records that are returned back to me. From my understanding, an >>> ORDER BY >>> can not change the number of records correct? If I do not use the >>> ORDER BY, >>> I get around 150 records. If I do an ORDER BY on any column that >>> is not in >>> tableA, the number of records blows up (~4k) and there are >>> duplicates. >>> >>> Any ideas? >>> >>> I thought that it was because of a buggy parser, so I wrote the >>> query again >>> as: >>> >>> >>> 1. SELECT * FROM tableA a >>> 2. INNER JOIN tableB b >>> 3. ON a.b_id = b.id >>> 4. INNER JOIN tableD d >>> 5. ON a.d_id = d.id >>> 6. INNER JOIN tableC c >>> 7. LEFT JOIN tableE e ON c.e_id = e.id >>> 8. ON ON a.c_id = c.id >>> >>> and it still gave me the same results... >>> >>> Thanks, >>> J >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE? "Joins + Order By" Changing row count!?!
Thanks Dan for the reply, How would I go about creating a sql dump? As for the ORDER BY clause, I do this: SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; Although a.some_other_value is not displayed in the tableA below, I forgot to add it. So, I add the ORDER BY clause on a.some_other_value and it returns a ton of records (mostly duplicates). I am pretty sure this is a bug because if I wrap this query inside of a subquery and perform the ORDER BY on the subquery, everything works. This is what WORKS: SELECT * FROM ( SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; So for now, I am just leaving my original query inside the subquery to fix it. However, I wouldn't mind posting some debugging information if it will help fix the bug. Thanks, J On Tue, Mar 3, 2009 at 8:23 PM, Dan wrote: > > On Mar 4, 2009, at 4:41 AM, Jonathon wrote: > > > Hello, > > > > I am executing a query such as: > > Can you post a database (or sql dump thereof) to run your queries > against? Also say exactly what ORDER BY clause you are adding to > the end of the query that causes it to return incorrect results? > > Thanks, > Dan. > > > > > 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c > > INNER > > JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id > > AND > > a.d_id = d.id AND c.e_id = e.id; > > 2. > > 3. CREATE TABLE tableA ( > > 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 5. b_idINTEGER NOT NULL, > > 6. c_idINTEGER NOT NULL, > > 7. d_idINTEGER NOT NULL > > 8. ) > > 9. > > 10. CREATE TABLE tableB ( > > 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 12. ); > > 13. > > 14. CREATE TABLE tableC ( > > 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 16. e_idINTEGER NOT NULL > > 17. ); > > 18. > > 19. CREATE TABLE tableD ( > > 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 21. ); > > 22. > > 23. CREATE TABLE tableE ( > > 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 25. ); > > > > > > This seems to be returning the correct records. However, when I > > append an > > ORDER BY at the end of the query, it seems to be changing the number > > of > > records that are returned back to me. From my understanding, an > > ORDER BY > > can not change the number of records correct? If I do not use the > > ORDER BY, > > I get around 150 records. If I do an ORDER BY on any column that > > is not in > > tableA, the number of records blows up (~4k) and there are duplicates. > > > > Any ideas? > > > > I thought that it was because of a buggy parser, so I wrote the > > query again > > as: > > > > > > 1. SELECT * FROM tableA a > > 2. INNER JOIN tableB b > > 3. ON a.b_id = b.id > > 4. INNER JOIN tableD d > > 5. ON a.d_id = d.id > > 6. INNER JOIN tableC c > > 7. LEFT JOIN tableE e ON c.e_id = e.id > > 8. ON ON a.c_id = c.id > > > > and it still gave me the same results... > > > > Thanks, > > J > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLITE? "Joins + Order By" Changing row count!?!
On Mar 4, 2009, at 4:41 AM, Jonathon wrote: > Hello, > > I am executing a query such as: Can you post a database (or sql dump thereof) to run your queries against? Also say exactly what ORDER BY clause you are adding to the end of the query that causes it to return incorrect results? Thanks, Dan. > 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c > INNER > JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id > AND > a.d_id = d.id AND c.e_id = e.id; > 2. > 3. CREATE TABLE tableA ( > 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > 5. b_idINTEGER NOT NULL, > 6. c_idINTEGER NOT NULL, > 7. d_idINTEGER NOT NULL > 8. ) > 9. > 10. CREATE TABLE tableB ( > 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > 12. ); > 13. > 14. CREATE TABLE tableC ( > 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > 16. e_idINTEGER NOT NULL > 17. ); > 18. > 19. CREATE TABLE tableD ( > 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > 21. ); > 22. > 23. CREATE TABLE tableE ( > 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > 25. ); > > > This seems to be returning the correct records. However, when I > append an > ORDER BY at the end of the query, it seems to be changing the number > of > records that are returned back to me. From my understanding, an > ORDER BY > can not change the number of records correct? If I do not use the > ORDER BY, > I get around 150 records. If I do an ORDER BY on any column that > is not in > tableA, the number of records blows up (~4k) and there are duplicates. > > Any ideas? > > I thought that it was because of a buggy parser, so I wrote the > query again > as: > > > 1. SELECT * FROM tableA a > 2. INNER JOIN tableB b > 3. ON a.b_id = b.id > 4. INNER JOIN tableD d > 5. ON a.d_id = d.id > 6. INNER JOIN tableC c > 7. LEFT JOIN tableE e ON c.e_id = e.id > 8. ON ON a.c_id = c.id > > and it still gave me the same results... > > Thanks, > J > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLITE? "Joins + Order By" Changing row count!?!
Hello, I am executing a query such as: 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id; 2. 3. CREATE TABLE tableA ( 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 5. b_idINTEGER NOT NULL, 6. c_idINTEGER NOT NULL, 7. d_idINTEGER NOT NULL 8. ) 9. 10. CREATE TABLE tableB ( 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT 12. ); 13. 14. CREATE TABLE tableC ( 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 16. e_idINTEGER NOT NULL 17. ); 18. 19. CREATE TABLE tableD ( 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT 21. ); 22. 23. CREATE TABLE tableE ( 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT 25. ); This seems to be returning the correct records. However, when I append an ORDER BY at the end of the query, it seems to be changing the number of records that are returned back to me. From my understanding, an ORDER BY can not change the number of records correct? If I do not use the ORDER BY, I get around 150 records. If I do an ORDER BY on any column that is not in tableA, the number of records blows up (~4k) and there are duplicates. Any ideas? I thought that it was because of a buggy parser, so I wrote the query again as: 1. SELECT * FROM tableA a 2. INNER JOIN tableB b 3. ON a.b_id = b.id 4. INNER JOIN tableD d 5. ON a.d_id = d.id 6. INNER JOIN tableC c 7. LEFT JOIN tableE e ON c.e_id = e.id 8. ON ON a.c_id = c.id and it still gave me the same results... Thanks, J ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] Bug in SQlite ?
What assertion failure are you seeing? What is the exact message? Can you use a debugger to step into the code where the assertion failure happens? RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Bug in SQlite ?
On 9/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I am seeing an assert crash on my system here. I think it is caused by the following code, --select.c (line 88)-- /* ** Delete the given Select structure and all of its substructures. */ void sqlite3SelectDelete(Select *p){ if( p ){ clearSelect(p); sqliteFree(p); } } -- I think it should be: if ( *p ) Why do you think so? The other way seems a good way to check for a non null pointer. "*p" is a structure. What does testing a structure for non-zero do? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Bug in SQlite ?
I am seeing an assert crash on my system here. I think it is caused by the following code, --select.c (line 88)-- /* ** Delete the given Select structure and all of its substructures. */ void sqlite3SelectDelete(Select *p){ if( p ){ clearSelect(p); sqliteFree(p); } } -- I think it should be: if ( *p ) Mario Hebert Legerity
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
ok, I pinned it down. it is a genuine bug, and has nothing to do with dlls and wrappers. to reproduce, do this: 1. open the command-line utility on a database (or even with no database, it does not matter) 2. type this: SQLite version 3.2.5 Enter ".help" for instructions sqlite> pragma empty_Result_callbacks=1; sqlite> analyze; this will crash immediately. in general, if empty_Result_callbacks is set, AND the database is analyzed, sqlite crashes consistently. the reason it appeared in sqlite3Explorer is that it sets the pragma by default. DRH, pls check this. > -Original Message- > From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 7:14 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > "Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 > 14:49:54 napisal(a): > > >ok. > >I use sqlite3Explorer (which should not matter), I create a new > >database, then I run : > > > >create table test1(id integer); > >analyze > > > >this crashes immediately, with dll 3.2.5. it works fine from the > >command-line. > > > I can confirm that. But it seems that the problem is in > Sqlite explorer or your sqlite3.dll wrapper implementation. > When I try that with my own Delphi wrapper implementation it > works. But opening database with sqlite explorer results in crash. > > Regards, > Miha > > >
RE: [sqlite] bug in sqlite 3.2.5 compila tion and ANALYZE
"Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 14:49:54 napisal(a): >ok. >I use sqlite3Explorer (which should not matter), I create a new >database, then I run : > >create table test1(id integer); >analyze > >this crashes immediately, with dll 3.2.5. it works fine from the >command-line. > I can confirm that. But it seems that the problem is in Sqlite explorer or your sqlite3.dll wrapper implementation. When I try that with my own Delphi wrapper implementation it works. But opening database with sqlite explorer results in crash. Regards, Miha
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
ok. I use sqlite3Explorer (which should not matter), I create a new database, then I run : create table test1(id integer); analyze this crashes immediately, with dll 3.2.5. it works fine from the command-line. > -Original Message- > From: Ned Batchelder [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 3:10 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > Perhaps you could provide the exact SQL that crashed. The > code seems to be very well tested automatically, so it is > very unlikely that all ANALYZE executions fail. > > --Ned. > http://nedbatchelder.com > > -Original Message- > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] > Sent: Monday, 05 September, 2005 5:46 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > I tried the ANALYZE statement with sqlite 3.2.5 in dll form, > (both the pre-compiled version downloaded from the site, AND > a local compilation). > in both > cases, the statement fails with an ACCESS VIOLATION. however, > the same statement, when run from the pre-compiled > sqlite3.exe, works. > further, having succesfully ANALYZED a database with the > command-line program, I can no longer work with the database > using the DLL api. > I think there is something very wrong with the compilation > defines of the DLL. > > DRH: can you pls check and fix ? > ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, > obviously) > > > > > > >
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
Perhaps you could provide the exact SQL that crashed. The code seems to be very well tested automatically, so it is very unlikely that all ANALYZE executions fail. --Ned. http://nedbatchelder.com -Original Message- From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] Sent: Monday, 05 September, 2005 5:46 AM To: sqlite-users@sqlite.org Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the pre-compiled version downloaded from the site, AND a local compilation). in both cases, the statement fails with an ACCESS VIOLATION. however, the same statement, when run from the pre-compiled sqlite3.exe, works. further, having succesfully ANALYZED a database with the command-line program, I can no longer work with the database using the DLL api. I think there is something very wrong with the compilation defines of the DLL. DRH: can you pls check and fix ? ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, obviously)
Re: [sqlite] bug in sqlite 3.2.5 compila tion and ANALYZE
>ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, >obviously) - Precompiled version of 3.2.5, - run with Sqlite Explorer 1.6 - winXP Pro SP2 Run without problems. Regards, Miha
[sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the pre-compiled version downloaded from the site, AND a local compilation). in both cases, the statement fails with an ACCESS VIOLATION. however, the same statement, when run from the pre-compiled sqlite3.exe, works. further, having succesfully ANALYZED a database with the command-line program, I can no longer work with the database using the DLL api. I think there is something very wrong with the compilation defines of the DLL. DRH: can you pls check and fix ? ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, obviously)
RE: [sqlite] Bug in SQLite C++ Wrapper?
Aha, thanks! -Original Message- From: Cory Nelson [mailto:[EMAIL PROTECTED] Sent: Saturday, June 25, 2005 11:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Bug in SQLite C++ Wrapper? cmd.executenonquery() creates a reader, which calls reset when destructed :) On 6/25/05, Brown, Dave <[EMAIL PROTECTED]> wrote: > > In looking at the SQLite C++ Wrapper > (http://dev.int64.org/sqlite.html) I noticed the example insert code has a loop like: > > for(int i=0; i<1; i++) { > cmd.bind(1, i); > cmd.executenonquery(); > } > > but sqlite3_reset() isn't being called each time after the statement > is executed (executenonquery() just ends up calling sqlite3_step() ). > Doesn't > sqlite3_reset() have to be called in order to reuse a prepared query? > > -Dave > -- Cory Nelson http://www.int64.org
Re: [sqlite] Bug in SQLite C++ Wrapper?
cmd.executenonquery() creates a reader, which calls reset when destructed :) On 6/25/05, Brown, Dave <[EMAIL PROTECTED]> wrote: > > In looking at the SQLite C++ Wrapper (http://dev.int64.org/sqlite.html) I > noticed the example insert code has a loop like: > > for(int i=0; i<1; i++) { > cmd.bind(1, i); > cmd.executenonquery(); > } > > but sqlite3_reset() isn't being called each time after the statement is > executed (executenonquery() just ends up calling sqlite3_step() ). Doesn't > sqlite3_reset() have to be called in order to reuse a prepared query? > > -Dave > -- Cory Nelson http://www.int64.org
[sqlite] Bug in SQLite C++ Wrapper?
In looking at the SQLite C++ Wrapper (http://dev.int64.org/sqlite.html) I noticed the example insert code has a loop like: for(int i=0; i<1; i++) { cmd.bind(1, i); cmd.executenonquery(); } but sqlite3_reset() isn't being called each time after the statement is executed (executenonquery() just ends up calling sqlite3_step() ). Doesn't sqlite3_reset() have to be called in order to reuse a prepared query? -Dave