[sqlite] Report a warning bug about Lemon parser
Hi, yy_destructor function can not suppress warning about unused
%extra_argument variable. My yy_destructor function that Lemon generated
like this:
```
static void yy_destructor(
yyParser *yypParser,/* The parser */
YYCODETYPE yymajor, /* Type code for object to destroy */
YYMINORTYPE *yypminor /* The object to be destroyed */
){
COSStyleParseARG_FETCH;
switch( yymajor ){
/* Here is inserted the actions which take place when a
** terminal or non-terminal is destroyed. This can happen
** when the symbol is popped from the stack during a
** reduce or during error processing or when a parser is
** being destroyed before it is finished parsing.
**
** Note: during a reduce, the only symbols destroyed are those
** which appear on the RHS of the rule, but which are not used
** inside the C code.
*/
default: break; /* If no destructor action specified: do nothing */
}
}
```
--
*By tan...@gmail.com tan...@gmail.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Report a warning bug about Lemon parser
On 1/22/15, Tang Tianyong tan...@gmail.com wrote:
Hi, yy_destructor function can not suppress warning about unused
%extra_argument variable.
Sure it can. Just add code to one of your destructors that references
the %extra_argument variable. It doesn't have to actually do anything
with the variable. If the variable is named xyzzy then it will
probably suffice to just say (void)xyzzy; inside one of your
destructors.
My yy_destructor function that Lemon generated
like this:
```
static void yy_destructor(
yyParser *yypParser,/* The parser */
YYCODETYPE yymajor, /* Type code for object to destroy */
YYMINORTYPE *yypminor /* The object to be destroyed */
){
COSStyleParseARG_FETCH;
switch( yymajor ){
/* Here is inserted the actions which take place when a
** terminal or non-terminal is destroyed. This can happen
** when the symbol is popped from the stack during a
** reduce or during error processing or when a parser is
** being destroyed before it is finished parsing.
**
** Note: during a reduce, the only symbols destroyed are those
** which appear on the RHS of the rule, but which are not used
** inside the C code.
*/
default: break; /* If no destructor action specified: do nothing */
}
}
```
--
*By tan...@gmail.com tan...@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] Sqlite problem possible bug
On 1/7/15, The Responsa Project gr.respo...@biu.ac.il wrote:
To Whom it amy concern
I am trying to use SQLITE and the like statement with wildcards and hebrew
when I put in an english string it works correctly, such as
Select * from dbname where colname like '%123%'
I will get all the entries from that column that contain 123 anywhere in the
column.
However if I substitute 123 with hebrew letters - it matches all the
entries, not just the ones containing what I asked for.
If I do not use the wilcards in the like it matches the exact word properly.
In version 3.2.2 of sqlite this worked fine (with wildcards), later versions
it does not. So for example SELECT * from dbname where colname like '%אב%'
will give me all the entries not only the ones matching only אב.
It should work. Here is the test case I used:
CREATE TABLE t1(x TEXT);
INSERT INTO t1(x) VALUES('abc'),('אב'),
('בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ'),
('וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ '),('xyz');
.print --- all ---
SELECT rowid, x FROM t1;
.print --- Using %אב% ---
SELECT rowid, x FROM t1 WHERE x LIKE '%אב%';
The above gives me this output:
--- all ---
1|abc
2|אב
3|בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ
4|וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ
5|xyz
--- Using %אב% ---
2|אב
Which is exactly what you would expect, no?
Perhaps you can give us more details about how you are invoking
SQLite. The problem might be in the interface to your programming
language, not in SQLite itself.
--
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] Sqlite problem possible bug
To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? All help is appreciated, thanks in advance Sincerely,Sharon Gottlieb The Responsa Project Bar-Ilan University Ramat-Gan 52900, ISRAEL Tel: 972-3-5318-411 / Fax: 972-3-5341-850 Email: respo...@mail.biu.ac.il Internet http://responsa.biu.ac.il ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite problem possible bug
On 2015/01/07 12:13, The Responsa Project wrote: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? Not a bug in the latest version - works fine for me, but I am not sure which other versions you have tested. Are you using the C api directly or going through some wrapper? (It might mess with the UTF8 or whatever encoding you start off with). Is your DB in UTF-8 mode? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite problem possible bug
How about to use dynamic binding? For example, is your SQL(SELECT * from dbname where colname like '%אב%'), use '?' instead of 'אב'. In my guess, 'אב' can have same ASCII code of wildcard(%). Full SQL can be as like as follows. SELECT * from dbname where colname like '%?%' To do this, you need to use sqlite3_bind*** functions in c API. Regards YONGIL. 2015. 1. 7. 오후 9:34에 The Responsa Project gr.respo...@biu.ac.il님이 작성: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? All help is appreciated, thanks in advance Sincerely,Sharon Gottlieb The Responsa Project Bar-Ilan University Ramat-Gan 52900, ISRAEL Tel: 972-3-5318-411 / Fax: 972-3-5341-850 Email: respo...@mail.biu.ac.il Internet http://responsa.biu.ac.il ___ 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 report on bug reporting page (grammatical errors)
Hello All, Page: https://www.sqlite.org/src/wiki?name=Bug+Reports I recommend this change: what the problem is. what the problem was. Reason: Rest of discussion is in past tense. There were also numerous duplicates. There were also numerous duplicate bug reports. Reason: more complete sentence about what was duplicates. -- inum: 883510009027723 sip: jungleboo...@sip2sip.info xmpp: jungle-boo...@jit.si ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, temp text UNIQUE NOT NULL); works OK CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY AUTOINCREMENT, temp text UNIQUE NOT NULL); gives error AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
On 11/16/2014 10:51 AM, Paul Sanderson wrote: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. Which part of the error message do you find unclear? For details, see http://www.sqlite.org/autoinc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which, clearly, is not true. http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite see this link for more on the subject. 2014-11-16 13:56 GMT-02:00 Igor Tandetnik i...@tandetnik.org: On 11/16/2014 10:51 AM, Paul Sanderson wrote: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. Which part of the error message do you find unclear? For details, see http://www.sqlite.org/autoinc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Bernardo Sulzbach ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
Thanks for the link Bernard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 16 November 2014 16:07, Bernardo Sulzbach mafagafogiga...@gmail.com wrote: You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which, clearly, is not true. http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite see this link for more on the subject. 2014-11-16 13:56 GMT-02:00 Igor Tandetnik i...@tandetnik.org: On 11/16/2014 10:51 AM, Paul Sanderson wrote: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. Which part of the error message do you find unclear? For details, see http://www.sqlite.org/autoinc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Bernardo Sulzbach ___ 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] Is it a bug ?
Hi, I have using Sqlite for my small embedded projects for quite a while. Recently we found a condition where is simple select all does not operate properly. Problem is incorrect journal file, which is created on system reboot. Reproduce this condition relativity simple: step 1: we open db step 2: write/update something to db. step 3: switch journal to memory step 4: write/update something to db. Do not close you SW keep it running and DB open step 5: reboot your system or kill you SW not gracefully. This reboot/kill will dump incorrect incorrect journal file to the file system. This will corrupt DB on next start. I am using simple workaround: switch journal to memory before any other db access. I will rebuild sqlite with journal to memory by default later. This is reproduced very stable from 3.7.5 to latest release. And I do not test multiple switching journal file/memory with update db between switching. I do not need fix for this. Just was thinking you would like to know about this problem. I appreciate what you are doing with this SW. Andrei ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it a bug ?
On 6 Nov 2014, at 3:13am, Andrei Yakimov anj...@gmail.com wrote: Problem is incorrect journal file, which is created on system reboot. Reproduce this condition relativity simple: step 1: we open db step 2: write/update something to db. step 3: switch journal to memory step 4: write/update something to db. Do not close you SW keep it running and DB open step 5: reboot your system or kill you SW not gracefully. Please see http://www.sqlite.org/pragma.html#pragma_journal_mode The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt. In other words, SQLite is operating as designed. The ability to keep the journal in memory is supplied for databases where, if the system crashes, you would have to begin the task again anyway. If you need your database to be recoverable after a crash, sorry, but you can't use that mode. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Curious datetime/strftime BUG?
On 6/10/2014 18:35, Clemens Ladisch wrote: John wrote: On 5/10/2014 19:59, Clemens Ladisch wrote: The documentation http://www.sqlite.org/lang_datefunc.html says: | These functions only work for dates between -01-01 00:00:00 and | -12-31 23:59:59. For dates outside that range, the results of | these functions are undefined. All equivalent functions should return consistent results. Why do you assume that undefined should imply consistency? datetime() could return Cthulhu fhtagn at the Ides of any month in such a year, and there would be nothing wrong with it. Undefined allows _anything_. Ok Clemens, I concede, undefined does allow _anything_. God, it's got to be 40 years since I read Lovecraft. Regards, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Curious datetime/strftime BUG?
John wrote: On 5/10/2014 19:59, Clemens Ladisch wrote: The documentation http://www.sqlite.org/lang_datefunc.html says: | These functions only work for dates between -01-01 00:00:00 and | -12-31 23:59:59. For dates outside that range, the results of | these functions are undefined. All equivalent functions should return consistent results. Why do you assume that undefined should imply consistency? datetime() could return Cthulhu fhtagn at the Ides of any month in such a year, and there would be nothing wrong with it. Undefined allows _anything_. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT ... GROUP BY: Bug or misunderstanding?
Hello,
I'm using sqlite over the python sqlite3 module shipped with the standard
distribution. Being not an SQL expert at all, I stumbled over an inconsistency
with a SELECT GROUP BY statement. The following python script might be
used to reproduce:
import sqlite3
print(sqlite_version=,sqlite3.sqlite_version)
db = sqlite3.connect(:memory:)
c = db.cursor()
c.execute(CREATE TABLE Test(Id INTEGER PRIMARY KEY, Name TEXT, Score INTEGER))
c.execute(INSERT INTO Test(Name,Score) VALUES('d1',100))
c.execute(INSERT INTO Test(Name,Score) VALUES('d1',99))
c.execute(INSERT INTO Test(Name,Score) VALUES('d1',98))
c.execute(INSERT INTO Test(Name,Score) VALUES('d2',101))
c.execute(INSERT INTO Test(Name,Score) VALUES('d2',102))
a = c.execute(SELECT * FROM Test).fetchall()
print(a)
a = c.execute(SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name
ORDER BY Score).fetchall()
print(a)
Using the default sqlite.dll of the python 3.2.x distribution, the output is as
follows:
sqlite_version= 3.7.4
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (5, 'd2', 101)]
^^^
Please note the '5' in the output of the select ... group by statement.
Exchanging the sqlite.dll with a newer version, I get the following output:
sqlite_version= 3.8.6
[(1, 'd1', 100), (2, 'd1', 99), (3, 'd1', 98), (4, 'd2', 101), (5, 'd2', 102)]
[(3, 'd1', 98), (4, 'd2', 101)]
^^^
Question: Did I encounter a bug in the sqlite version 3.7.4, or are both
outputs correct and my understanding of the GROUP BY semantic is wrong?
Thanks in advance!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT ... GROUP BY: Bug or misunderstanding?
Hi Christoph, the id column does not appear in an aggregate function and also not in group by. Your statement uses 'GROUP BY Name' and so returns exactly one row per name. If there are several rows with the same name, the ID of your result is from one of these rows. The appropriate documentation is Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row. from here http://www.sqlite.org/lang_select.html#resultset The keyword here is arbitrarily chosen. So, you can not expect to get the same id every time even if you do not change the sqlite version. Hope this helps Martin Am 22.08.2014 15:19, schrieb Christoph Wiedemann: SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name ORDER BY Score -- *Codeswift GmbH * Kräutlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite querie delete bug
There is an issue that if entry is selected in Table view then editing in Query view and clicking delete key then it doesn't delete query but asks to delete database entry which is not expected behavior. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite querie delete bug
On Wed, Jul 30, 2014 at 8:53 AM, Oto _ oto...@gmail.com wrote: There is an issue that if entry is selected in Table view then editing in Query view and clicking delete key then it doesn't delete query but asks to delete database entry which is not expected behavior. It sounds as if you are describing a problem with a third-party database access application, not with the core SQLite library. This mailing list covers the core SQLite library. Please contact your vendor for support with third-party tools that use SQLite. -- 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] Sqlite querie delete bug
This doesn't sound like an SQLite problem to me. Instead it sounds like a problem with whatever visualization tool you are using. What tool are you using? If you can find a user's group for that tool, you may get more helpful answers there. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Oto _ Sent: Wednesday, July 30, 2014 8:53 AM To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite querie delete bug There is an issue that if entry is selected in Table view then editing in Query view and clicking delete key then it doesn't delete query but asks to delete database entry which is not expected behavior. ___ 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] SQLite for Windows Phone bug
Hello, SQLite team, I would like to report a bug in the Windows Phone version of SQLite, that is already present for three releases in the row. The problem arises quite randomly, when SQLite refuses to insert or update some items into database and throws the following error: SQL logic error or missing database . I have checked the SQL and that database is well present, but the problem persists. The situation is even worse because the inserts and updates that fail seem to be completely random - if I compare an entry that fails and one that doesn’t, there is nothing that should be taken as a reason for the error. I’m not the only one experiencing this - http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement . The interesting fact is that in the 3.8.1 version this never happened, so the cause must be some change between these the version 3.8.1 and version 3.8.2. Can you please check if anything can be done? My project is highly dependant on SQLite, but this way it is not reliable enough… Thank you very, very much Sincerely Martin Zikmund Sent from Surface Pro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Windows Phone bug
Sorry for another mail, but I forgot to mention, that the bug seems to arise only in transaction. When the items are inserted individually, it seems to be working correctly (although I can’t confirm it 100 %). Hello, SQLite team, I would like to report a bug in the Windows Phone version of SQLite, that is already present for three releases in the row. The problem arises quite randomly, when SQLite refuses to insert or update some items into database and throws the following error: SQL logic error or missing database . I have checked the SQL and that database is well present, but the problem persists. The situation is even worse because the inserts and updates that fail seem to be completely random - if I compare an entry that fails and one that doesn’t, there is nothing that should be taken as a reason for the error. I’m not the only one experiencing this - http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement . The interesting fact is that in the 3.8.1 version this never happened, so the cause must be some change between these the version 3.8.1 and version 3.8.2. Can you please check if anything can be done? My project is highly dependant on SQLite, but this way it is not reliable enough… Thank you very, very much Sincerely Martin Zikmund Sent from Surface Pro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite for Windows Phone bug
On 19 Feb 2014, at 5:06am, Martin Zikmund martinzikm...@live.com wrote: SQL logic error or missing database The two usual causes of this under WinMob are both related to permissions and privileges. The folder the database file is stored in may be protected against the app opening the existing database file or creating a new journal file, or the permissions on the database file may be protected against the app. First make sure you are specifying the correct path. If you didn't specify a full path, try temporarily doing so in your test setup and see if anything changes. If that doesn't work try modifying your app to create a new text file (standard C file API) in the same folder as the database file normally lives. Does it succeed ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite for Windows Phone bug
Have you activated the error and warning log to see if it gives you any further diagnostics? http://www.sqlite.org/errlog.html On Wed, Feb 19, 2014 at 12:06 AM, Martin Zikmund martinzikm...@live.comwrote: Hello, SQLite team, I would like to report a bug in the Windows Phone version of SQLite, that is already present for three releases in the row. The problem arises quite randomly, when SQLite refuses to insert or update some items into database and throws the following error: SQL logic error or missing database . I have checked the SQL and that database is well present, but the problem persists. The situation is even worse because the inserts and updates that fail seem to be completely random - if I compare an entry that fails and one that doesn't, there is nothing that should be taken as a reason for the error. I'm not the only one experiencing this - http://stackoverflow.com/questions/20474253/sqlite-3-8-2-exception-on-update-statement. The interesting fact is that in the 3.8.1 version this never happened, so the cause must be some change between these the version 3.8.1 and version 3.8.2. Can you please check if anything can be done? My project is highly dependant on SQLite, but this way it is not reliable enough... Thank you very, very much Sincerely Martin Zikmund Sent from Surface Pro ___ 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] sqlite3 shell .import bug (edge case)
Hi,
I am currently using sqlite3 version:
3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a
I found an edge case bug in the sqlite3 shell when importing csv data with
fields containing embedded quotes, CRs and LFs:
When a field contains an embedded quote, and that embedded quote is
immediately followed by an EOL then the current csv parser will consider
the end of field to be reached and fail to import the row correctly.
For example the following csv, a single data row with column headers will
fail to import correctly.
column1,column2,column3,column4
fielddata1,fielddata2,field
data 3,fielddata4
The offending code is in shell.c in function
static char *csv_read_one_field(CSVReader *p);
An example fix could be something like the following -- adding a counter
flag 'cQuoteComplete' to track quotes properly.
static char *csv_read_one_field(CSVReader *p){
int c, pc;
int cSep = p-cSeparator;
int cQuoteComplete = 0;
p-n = 0;
c = fgetc(p-in);
if( c==EOF || seenInterrupt ){
p-cTerm = EOF;
return 0;
}
if( c=='' ){
int startLine = p-nLine;
int cQuote = c;
cQuoteComplete+=1;
pc = 0;
while( 1 ){
c = fgetc(p-in);
if( c=='\n' ) p-nLine++;
if( c==cQuote ){
cQuoteComplete+=1;
if( pc==cQuote ){
pc = 0;
continue;
}
}
if( (c==cSep pc==cQuote)
|| (c=='\n' pc==cQuote)
|| (c=='\n' pc=='\r' p-n=2 p-z[p-n-2]==cQuote)
|| (c==EOF pc==cQuote)
){
if (cQuoteComplete%2 == 0) {
do{ p-n--; }while( p-z[p-n]!=cQuote );
p-cTerm = c;
break;
}
}
if( c==EOF ){
fprintf(stderr, %s:%d: unterminated %c-quoted field\n,
p-zFile, startLine, cQuote);
p-cTerm = EOF;
break;
}
csv_append_char(p, c);
pc = c;
}
if( cQuoteComplete%2 != 0 ){
fprintf(stderr, %s:%d: unescaped %c character\n,
p-zFile, p-nLine, cQuote);
}
}else{
while( c!=EOF c!=cSep c!='\n' ){
csv_append_char(p, c);
c = fgetc(p-in);
}
if( c=='\n' ){
p-nLine++;
if( p-n1 p-z[p-n-1]=='\r' ) p-n--;
}
p-cTerm = c;
}
if( p-z ) p-z[p-n] = 0;
return p-z;
}
I built the sqlite3 shell from the almagamation source and tested the above
change using my import data; about 1Gb of messy, but rfc4180 compliant,
CSV. It all imported cleanly.
sqlite3 is a wonderful bit of software. I have been using it for some time
now to munge and query multi-gigabyte size data sets and am very impressed
with its performance and capabilities.
Best Regards,
Lindsay
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 shell .import bug (edge case)
Thanks for the test case. Fixed at
http://www.sqlite.org/src/info/5e239ecda0
On Wed, Dec 11, 2013 at 6:37 AM, Lindsay Lawrence thinknl...@gmail.comwrote:
Hi,
I am currently using sqlite3 version:
3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a
I found an edge case bug in the sqlite3 shell when importing csv data with
fields containing embedded quotes, CRs and LFs:
When a field contains an embedded quote, and that embedded quote is
immediately followed by an EOL then the current csv parser will consider
the end of field to be reached and fail to import the row correctly.
For example the following csv, a single data row with column headers will
fail to import correctly.
column1,column2,column3,column4
fielddata1,fielddata2,field
data 3,fielddata4
The offending code is in shell.c in function
static char *csv_read_one_field(CSVReader *p);
An example fix could be something like the following -- adding a counter
flag 'cQuoteComplete' to track quotes properly.
static char *csv_read_one_field(CSVReader *p){
int c, pc;
int cSep = p-cSeparator;
int cQuoteComplete = 0;
p-n = 0;
c = fgetc(p-in);
if( c==EOF || seenInterrupt ){
p-cTerm = EOF;
return 0;
}
if( c=='' ){
int startLine = p-nLine;
int cQuote = c;
cQuoteComplete+=1;
pc = 0;
while( 1 ){
c = fgetc(p-in);
if( c=='\n' ) p-nLine++;
if( c==cQuote ){
cQuoteComplete+=1;
if( pc==cQuote ){
pc = 0;
continue;
}
}
if( (c==cSep pc==cQuote)
|| (c=='\n' pc==cQuote)
|| (c=='\n' pc=='\r' p-n=2 p-z[p-n-2]==cQuote)
|| (c==EOF pc==cQuote)
){
if (cQuoteComplete%2 == 0) {
do{ p-n--; }while( p-z[p-n]!=cQuote );
p-cTerm = c;
break;
}
}
if( c==EOF ){
fprintf(stderr, %s:%d: unterminated %c-quoted field\n,
p-zFile, startLine, cQuote);
p-cTerm = EOF;
break;
}
csv_append_char(p, c);
pc = c;
}
if( cQuoteComplete%2 != 0 ){
fprintf(stderr, %s:%d: unescaped %c character\n,
p-zFile, p-nLine, cQuote);
}
}else{
while( c!=EOF c!=cSep c!='\n' ){
csv_append_char(p, c);
c = fgetc(p-in);
}
if( c=='\n' ){
p-nLine++;
if( p-n1 p-z[p-n-1]=='\r' ) p-n--;
}
p-cTerm = c;
}
if( p-z ) p-z[p-n] = 0;
return p-z;
}
I built the sqlite3 shell from the almagamation source and tested the above
change using my import data; about 1Gb of messy, but rfc4180 compliant,
CSV. It all imported cleanly.
sqlite3 is a wonderful bit of software. I have been using it for some time
now to munge and query multi-gigabyte size data sets and am very impressed
with its performance and capabilities.
Best Regards,
Lindsay
___
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] incredibly minor documentation bug
Don't ask me what use case made me notice this, but the http://www.sqlite.org/lang_corefunc.html documentation for abs(), as of 12:29PM EDT on 10/26/2013, is only about 99.89157978275145% accurate. If X is the integer -9223372036854775807 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value. It's off by one: X is actually -9223372036854775808. -9223372036854775807 is the lowest number *with* a positive 64-bit two complement. On Win7 64-bit: sqlite .version SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a sqlite SELECT ABS(-9223372036854775807); --docs claim will throw error; doesn't 9223372036854775807 sqlite SELECT ABS(-9223372036854775808); --does throw error Error: integer overflow sqlite SELECT ABS(-9223372036854775809); --now we're in floating point 9.22337203685478e+18 Happy to put my 0.0010842021724855 cents in, GC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] incredibly minor documentation bug
On Sat, Oct 26, 2013 at 12:30 PM, George Collins george.coll...@outlook.com wrote: If X is the integer -9223372036854775807 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value. It's off by one: X is actually -9223372036854775808. -9223372036854775807 is the lowest number *with* a positive 64-bit two complement. Fixed at http://www.sqlite.org/docsrc/info/9e1d78e903 - the change will be in the next release. Tnx. -- 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 report: small bug in shell.c
I have found that the following command in my opinion returns an
incorrect exit code:
eric@sirius:~/src/sqlite-amalgamation-3071602$ ./sqlite3 appl.db .quit
eric@sirius:~/src/sqlite-amalgamation-3071602$ echo $?
2
This should (in my opinion) return 0 for success instead of 2. In
interactive mode the correct value is returned:
eric@sirius:~/src/sqlite-amalgamation-3071602$ ./sqlite3 appl.db
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .quit
eric@sirius:~/src/sqlite-amalgamation-3071602$ echo $?
0
The cause is that do_meta_command() returns 2 if an exit is requested.
In non-interactive mode this value is not filtered and passed directly
back to the shell.
I do not know if this is intentional or not. If so, please ignore this
message.
Below is a patch which will fix the issue and have the shell return 0
even if an exit is requested non-interactively. The fix is rather crude,
it will set rc to 0 if do_meta_command() returned 2. As far as I can
tell the return code 2 is used exclusively for exit/quit requests so
this should be safe to do.
===
--- shell.c.org 2013-04-12 14:21:39.0 +0200
+++ shell.c 2013-04-16 20:04:12.0 +0200
@@ -3128,6 +3128,7 @@
z = cmdline_option_value(argc,argv,++i);
if( z[0]=='.' ){
rc = do_meta_command(z, data);
+rc = rc == 2 ? 0 : rc;
if( rc bail_on_error ) return rc;
}else{
open_db(data);
@@ -3152,6 +3153,7 @@
*/
if( zFirstCmd[0]=='.' ){
rc = do_meta_command(zFirstCmd, data);
+ rc = rc == 2 ? 0 : rc;
}else{
open_db(data);
rc = shell_exec(data.db, zFirstCmd, shell_callback, data, zErrMsg);
===
Of course the above snippet is hereby donated to the Public Domain.
Thanks for all the hard work, I appreciate it immensely.
Regards,
Eric.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 05:18 PM, Dan Kennedy wrote: Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. You are right. I have been re-reading our mail thread and this is exactly what happens. I have build a SQLite version from the latest version in the archive (3.7.16) and this version creates a correct backup. Thanks for the fix. -- Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 05:18 PM, Dan Kennedy wrote: Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. Thanks. I have looked into ticket, but we also see this problem when the backup is written and read by the same SQLite version (3.7.5). I have recreated a backup using the 3.7.5 version. These are the hexdumps of both the original file and the backup created with SQLite 3.7.5: The original file: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 The backup created with SQLite 3.7.5: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 030 0100 040 050 0100 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 -- Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 01/02/2013 03:27 PM, Marco ten Thije wrote: On 12/21/2012 05:18 PM, Dan Kennedy wrote: Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. Thanks. I have looked into ticket, but we also see this problem when the backup is written and read by the same SQLite version (3.7.5). I have recreated a backup using the 3.7.5 version. These are the hexdumps of both the original file and the backup created with SQLite 3.7.5: The original file: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 What series of commands did you pass to 3.7.5 to create this file? That the change counters at byte offsets 24 and 92 are different makes this file look like it was created using 3.7.5 and then populated using an older version. Perhaps there is a bug in 3.7.5 causing this. What happens if you use 3.7.15 to create the original and do the backup? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 01/02/2013 10:04 AM, Dan Kennedy wrote: What series of commands did you pass to 3.7.5 to create this file? This file was copied from an existing product which uses QT 4.7 libraries to populate the database. I cannot reproduce the exact commands which resulted in this file, because it is populated dynamically by the software. I found out that QT 4.7 is using the 3.6.19 SQLite library. The databases were created using the 3.7.5 version, so you were right about this. The database is created with a 3.7.5 version and populated with 3.6.19 Perhaps there is a bug in 3.7.5 causing this. What happens if you use 3.7.15 to create the original and do the backup? I have recreated the database using a dump from the original database. I have created database using this dump with both 3.7.5 (on a ARM9 platform) and 3.7.15.1 (on Intel). To my surprise this resulted in quite different files. The one created with 3.7.5 is 125952 bytes in size, the 3.7.15.1 is 116736 bytes in size. These are the first 100 bytes in hex of both files: 3.7.5: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7b00 020 0300 0100 030 0100 040 050 0100 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 3.7.15.1: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7200 020 0300 0400 030 0100 040 050 0100 060 2d00 27e2 000d 0004 00e3 7202 2002 070 7301 e300 080 The created database files are both different from the one created with the .backup command. Both are reporting 'ok' with 'pragma integrity_check;'. The one created with .backup report errors: *** in database main *** Main freelist: invalid page number 151 On tree page 10 cell 10: invalid page number 154 On tree page 10 cell 10: Child page depth differs On tree page 10 cell 11: invalid page number 145 On tree page 10 cell 12: Child page depth differs On tree page 10 cell 13: invalid page number 120 On tree page 10 cell 13: Child page depth differs On tree page 10 cell 14: invalid page number 121 On tree page 10 cell 15: invalid page number 152 On tree page 10 cell 16: Child page depth differs On tree page 10 cell 18: invalid page number 157 On tree page 10 cell 18: Child page depth differs On tree page 10 cell 19: invalid page number 153 On tree page 10 cell 20: Child page depth differs On tree page 10 cell 21: invalid page number 141 On tree page 10 cell 21: Child page depth differs On tree page 10 cell 22: Child page depth differs On tree page 10 cell 53: invalid page number 132 On tree page 10 cell 53: Child page depth differs On tree page 10 cell 54: Child page depth differs On tree page 11 cell 0: invalid page number 143 On tree page 11 cell 1: invalid page number 129 On tree page 11 cell 2: Child page depth differs On tree page 11 cell 4: invalid page number 125 On tree page 11 cell 4: Child page depth differs On tree page 11 cell 5: Child page depth differs On tree page 11 cell 9: invalid page number 122 On tree page 11 cell 9: Child page depth differs On tree page 11 cell 10: Child page depth differs On tree page 11 cell 11: invalid page number 119 On tree page 11 cell 11: Child page depth differs On tree page 11 cell 12: Child page depth differs On tree page 11 cell 18: invalid page number 128 On tree page 11 cell 18: Child page depth differs On tree page 11 cell 19: invalid page number 149 On tree page 11 cell 20: Child page depth differs On tree page 11 cell 24: invalid page number 123 On tree page 11 cell 24: Child page depth differs On tree page 11 cell 25: Child page depth differs On tree page 11 cell 28: invalid page number 139 On tree page 11 cell 28: Child page depth differs On tree page 11 cell 29: Child page depth differs On tree page 11 cell 36: invalid page number 137 On tree page 11 cell 36: Child page depth differs On tree page 11 cell 37: invalid page number 156 On tree page 11 cell 38: invalid page number 147 On tree page 11 cell 39: invalid page number 146 On tree page 11 cell 40: Child page depth differs On tree page 11 cell 44: invalid page number 155 On tree page 11 cell 44: Child page depth differs On tree page 11 cell 45: Child page depth differs On tree page 11 cell 53: invalid page number 136 On tree page 11 cell 53: Child page depth differs On tree page 11 cell 54: Child page depth differs On page 11 at right child: invalid page number 138 Page 32 is never used Page 41 is never used Page 46 is never used Page 48 is never used Page 51 is never used Page 53 is never used Page
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 01/02/2013 11:03 AM, Marco ten Thije wrote: On 01/02/2013 10:04 AM, Dan Kennedy wrote: What series of commands did you pass to 3.7.5 to create this file? This file was copied from an existing product which uses QT 4.7 libraries to populate the database. I cannot reproduce the exact commands which resulted in this file, because it is populated dynamically by the software. I found out that QT 4.7 is using the 3.6.19 SQLite library. The databases were created using the 3.7.5 version, so you were right about this. The database is created with a 3.7.5 version and populated with 3.6.19 I did some more testing and found out that the backup database which SQLite 3.7.5 reports to be corrupted, is reported OK by SQLite 3.6.19, although this backup was created by the 3.7.5 SQLite version. I created a ASCII dump (using the .dump command) of the backup database using 3.6.19 and this dump is identical (except for the 'PRAGMA foreign_keys=OFF') to a dump of the original database. So it looks like SQLITE 3.7.5 and 3.7.15.1 (and probably all versions in between) have a problem reading the backup file, while 3.6.19 can read it. I have stored a copy of the original database on http://www.cbbio.nl/sqlite/energy.sqlite for further analysing Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
Hello, We are facing a problem with the '.backup' command using the SQLite command line interface. The resulting backup-database seems to be corrupt. We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can also be reproduced on the latest 3.7.15.1 version on Intel. I have attached our database to this email. These are the steps to reproduce it: ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; 3306 sqlite .backup backup.sqlite sqlite .exit The above shows the number of records in one of the tables and creates a backup to a new 'backup.sqlite' database file. But this new file seems to be corrupt: ./sqlite3 backup.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; Error: database disk image is malformed sqlite .exit I have look into the Bug database and found ticket 3858, but that one should already be fixed and we are not writing to the database during the backup. Is this a bug or some other problem? -- Regards, Marco ten Thije *Use system engineering bv* Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Mail Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: +31 53 5741456 F: +31 53 5741458 E: marco.tenth...@usetechnology.nl mailto:marco.tenth...@usetechnology.nl I: www.usetechnology.nl http://www.usetechnology.nl/ Chamber of Commerce nr: Veluwe en Twente 528 98210 VAT nr.: NL 8506.54.713B01 /This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Use system engineering bv shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Use system engineering bv does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. / ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
Can you please try running pragma integrity_check; on the original database? That will give an indication of whether the original database has any data corruption. On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije marco.tenth...@usetechnology.nl wrote: Hello, We are facing a problem with the '.backup' command using the SQLite command line interface. The resulting backup-database seems to be corrupt. We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can also be reproduced on the latest 3.7.15.1 version on Intel. I have attached our database to this email. These are the steps to reproduce it: ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; 3306 sqlite .backup backup.sqlite sqlite .exit The above shows the number of records in one of the tables and creates a backup to a new 'backup.sqlite' database file. But this new file seems to be corrupt: ./sqlite3 backup.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; Error: database disk image is malformed sqlite .exit I have look into the Bug database and found ticket 3858, but that one should already be fixed and we are not writing to the database during the backup. Is this a bug or some other problem? -- Regards, Marco ten Thije --**--** *Use system engineering bv* Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Mail Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: +31 53 5741456 F: +31 53 5741458 E: marco.tenthije@usetechnology.** nl marco.tenth...@usetechnology.nl mailto:marco.tenthije@** usetechnology.nl marco.tenth...@usetechnology.nl I: www.usetechnology.nl http://www.usetechnology.nl/ Chamber of Commerce nr: Veluwe en Twente 528 98210 VAT nr.: NL 8506.54.713B01 --**--** /This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Use system engineering bv shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Use system engineering bv does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. / ___ 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] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
It returns 'ok': ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma integrity_check; ok sqlite So, the original database looks ok. I have compared the two databases (original and backup) and they only differ in a few bytes in the header. The sizes match. This is a diff between the hexdumps of both databases: diff energy.hex backup.hex 2,3c2,3 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 --- 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 6,7c6,7 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 --- 050 0100 060 2d00 27e2 000d 0004 00e3 7202 2002 On 12/21/2012 02:34 PM, Eric Minbiole wrote: Can you please try running pragma integrity_check; on the original database? That will give an indication of whether the original database has any data corruption. On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije marco.tenth...@usetechnology.nl wrote: Hello, We are facing a problem with the '.backup' command using the SQLite command line interface. The resulting backup-database seems to be corrupt. We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can also be reproduced on the latest 3.7.15.1 version on Intel. I have attached our database to this email. These are the steps to reproduce it: ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; 3306 sqlite .backup backup.sqlite sqlite .exit The above shows the number of records in one of the tables and creates a backup to a new 'backup.sqlite' database file. But this new file seems to be corrupt: ./sqlite3 backup.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select count(*) from EnergyLogData; Error: database disk image is malformed sqlite .exit I have look into the Bug database and found ticket 3858, but that one should already be fixed and we are not writing to the database during the backup. Is this a bug or some other problem? -- Regards, Marco ten Thije --**--** *Use system engineering bv* Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Mail Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: +31 53 5741456 F: +31 53 5741458 E: marco.tenthije@usetechnology.** nlmarco.tenth...@usetechnology.nl mailto:marco.tenthije@** usetechnology.nlmarco.tenth...@usetechnology.nlI: www.usetechnology.nlhttp://www.usetechnology.nl/ Chamber of Commerce nr: Veluwe en Twente 528 98210 VAT nr.: NL 8506.54.713B01 --**--** /This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Use system engineering bv shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Use system engineering bv does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. / ___ 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 -- Met een vriendelijke groet, Marco ten Thije *Use system engineering bv* Bezoekadres: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Postadres: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: 053 5741456 F: 053 5741458 E: marco.tenth...@usetechnology.nl mailto:marco.tenth...@usetechnology.nl I: www.usetechnology.nl http://www.usetechnology.nl/ KvK nr: Veluwe en Twente 528 98210 BTW nr.: NL 8506.54.713B01 /Dit bericht (inclusief de eventuele bijlagen) is vertrouwelijk. Wanneer u dit bericht ten onrechte heeft ontvangen, dient u de afzender hiervan onmiddellijk per kerende e-mail op de hoogte te brengen en dit bericht te verwijderen uit uw systeem. Elk onbevoegd gebruik en/of onbevoegde verspreiding van dit bericht is niet
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 08:46 PM, Marco ten Thije wrote: It returns 'ok': ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite pragma integrity_check; ok sqlite So, the original database looks ok. I have compared the two databases (original and backup) and they only differ in a few bytes in the header. The sizes match. This is a diff between the hexdumps of both databases: diff energy.hex backup.hex What is the size of the two database files? Also, can we have the first 6 lines of each hex dump (i.e. enough to see the first 100 bytes)? Thanks, Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
What is the size of the two database files? The size of both files is 160768 bytes. Also, can we have the first 6 lines of each hex dump (i.e. enough to see the first 100 bytes)? The first bytes of the original database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 The first bytes of the backup database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 030 0100 040 050 0100 060 2d00 27e2 000d 0004 00e3 7202 2002 070 7301 e300 080 I can send the binary database, but the mailinglist strips the attachments. Best regards, Marco ten Thije // ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 10:54 PM, Marco ten Thije wrote: What is the size of the two database files? The size of both files is 160768 bytes. Also, can we have the first 6 lines of each hex dump (i.e. enough to see the first 100 bytes)? The first bytes of the original database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 The first bytes of the backup database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 030 0100 040 050 0100 060 2d00 27e2 000d 0004 00e3 7202 2002 070 7301 e300 080 Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl API documentation bug
The documentation (http://sqlite.org/tclsqlite.html) states: The *sqlite3* command is used as follows: *sqlite3* /dbcmd database-name/ However, the following shows up in the console: $ tclsh % package require sqlite3 3.7.12 % sqlite3 wrong # args: should be sqlite3 HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN? % What are the meaning of the additional optional arguments? P.S. -- I've not check other commands for mismatches. -- ++ | Gerald W. Lester, President, KNG Consulting LLC| | Cell: +1.504.236.6657 | Email: gerald.les...@kng-consulting.net| ++ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite4 key encoding bug
SQLite version 4.0.0 2012-07-07 12:21:48 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t(a); sqlite insert into t values(123); sqlite insert into t values(123.0); sqlite insert into t values(12323); sqlite insert into t values(12323.0); sqlite select * from t group by 1; 123.0 12323 12323.0 sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite4 key encoding bug
On 10/07/2012 9:58 AM, bardzotajneko...@interia.pl wrote: SQLite version 4.0.0 2012-07-07 12:21:48 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t(a); sqlite insert into t values(123); sqlite insert into t values(123.0); sqlite insert into t values(12323); sqlite insert into t values(12323.0); sqlite select * from t group by 1; 123.0 12323 12323.0 sqlite This is correct: 123 != 123.0 != 123 because they all have different types (int/float/string). Either create the table with typed columns to encourage all values to have the same type [1] or cast the group by key to coerce all values to the same type (not sure what happens if the type cast fails). [1] See the SQLite docs for rules about automatic type conversions for typed columns, it's not a panacea. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite4 key encoding bug
On 7/10/2012 11:24 AM, Ryan Johnson wrote: On 10/07/2012 9:58 AM, bardzotajneko...@interia.pl wrote: SQLite version 4.0.0 2012-07-07 12:21:48 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t(a); sqlite insert into t values(123); sqlite insert into t values(123.0); sqlite insert into t values(12323); sqlite insert into t values(12323.0); sqlite select * from t group by 1; 123.0 12323 12323.0 sqlite This is correct: 123 != 123.0 != 123 because they all have different types (int/float/string). Could you explain why 123 and 123.0 got grouped together, but 12323 and 12323.0 did not? Shouldn't the same logic apply? It's this inconsistency that looks qutie suspicious. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite4 key encoding bug
On Tue, Jul 10, 2012 at 11:33 AM, Igor Tandetnik itandet...@mvps.orgwrote: On 7/10/2012 11:24 AM, Ryan Johnson wrote: On 10/07/2012 9:58 AM, bardzotajneko...@interia.pl wrote: SQLite version 4.0.0 2012-07-07 12:21:48 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t(a); sqlite insert into t values(123); sqlite insert into t values(123.0); sqlite insert into t values(12323); sqlite insert into t values(12323.0); sqlite select * from t group by 1; 123.0 12323 12323.0 sqlite This is correct: 123 != 123.0 != 123 because they all have different types (int/float/string). Could you explain why 123 and 123.0 got grouped together, but 12323 and 12323.0 did not? Shouldn't the same logic apply? It's this inconsistency that looks qutie suspicious. SQLite4 is still using double internally for floating point computations. (Yes, there are plans to change that but it has not happened yet.) The bug above is due to rounding errors in the doubles, specifically rounding errors that occur when doing the key encoding. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] SQLite4 key encoding bug
On 7/10/2012 11:44 AM, Richard Hipp wrote: SQLite4 is still using double internally for floating point computations. (Yes, there are plans to change that but it has not happened yet.) The bug above is due to rounding errors in the doubles, specifically rounding errors that occur when doing the key encoding. Both 123.0 and 12323.0 should be exactly representable in a double, as far as I can tell. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite4 key encoding bug
On Tue, Jul 10, 2012 at 11:54 AM, Igor Tandetnik itandet...@mvps.orgwrote: On 7/10/2012 11:44 AM, Richard Hipp wrote: SQLite4 is still using double internally for floating point computations. (Yes, there are plans to change that but it has not happened yet.) The bug above is due to rounding errors in the doubles, specifically rounding errors that occur when doing the key encoding. Both 123.0 and 12323.0 should be exactly representable in a double, as far as I can tell. They are. The rounding error occurs in my code that converts them into the Key Encoding http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] [solution] - Re: Possible bug when rebuilding amalgamation without triggers or altertable
I looked into the problem more tonight and found all of the references to the omitted functions were from the auto-generated parser. I looked at the parser source and it was checking for omits in all the right places so the functions should have have been referenced. I checked the Makefile and found that OPT_FEATURE_FLAGS still had the default settings. I manually changed OPT_FEATURE_FLAGS in the Makefile and rebuilt sqlite3.c - this time I was able to use all of the omit features and create a usable binary. So root cause of my delima seems to be that OPT_FEATURE_FLAGS isn't populated by passing in to configure as my notes had indicated, but by manually editing the Makefile and adding the desired flags to OPT_FEATURE_FLAGS. On 4/26/2012 7:31 PM, nn6eumtr wrote: In response to Stepheen Beal's previous comments I do understand that not all combinations of -DSQLITE_OMIT statements will work, and past experience has shown its usually because the dependencies between the various options are not clear in the documentation. However someone has invested the effort to write the -DSQLITE_OMIT_ALTERTABLE and -DSQLITE_OMIT_FOREIGN_KEY functionality, and the person who did that may be concerned that these features do not work in current builds of SQLite, or they may have a fix which isn't part of the source distribution yet, or they may know other dependencies that need to be resolved. Those are the people I am attempting to reach via this list. If that isn't you or your not interested in a solution, then I would ask that you please don't discourage others from responding. On 4/25/2012 7:11 PM, nn6eumtr wrote: To clarify, the below steps to reproduce include building the amalgamation from scratch, and the errors I demonstrate occur after building and compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and -DSQLITE_OMIT_FOREIGN_KEY. In Stepheen Beal's earlier comments he appears to have not realized that I was rebuilding the amalgamation and not trying to use the -DSQLITE_OMIT_* flags with the distributed amalgamation. I still need some assistance. On 4/25/2012 1:09 AM, nn6eumtr wrote: Steps to recreate: 1) wget -nd -nH -c -t 0 -w 1 http://www.sqlite.org/src/tarball/SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b 2) mv -v SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b SQLite-9fb7da6904e479f4.tar.gz 3) tar -xzf SQLite-9fb7da6904e479f4.tar.gz 4) cd SQLite-9fb7da6904e479f4 5) OPT_FEATURE_FLAGS=-DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_FTS4 \ -DSQLITE_ENABLE_LOCKING_STYLE=0 -DSQLITE_ENABLE_MEMORY_MANAGEMENT \ -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \ -DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM -DSQLITE_OMIT_BUILTIN_TEST \ -DSQLITE_OMIT_COMPLETE -DSQLITE_OMIT_DATETIME_FUNCS \ -DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \ -DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \ -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE -DSQLITE_OMIT_TRACE \ -DSQLITE_OMIT_TRIGGER -DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0 \ ./configure --disable-tcl --disable-readline 6) make sqlite3.c 8) Compile - gcc -c -Wall -DSQLITE_ENABLE_FTS3_PARENTHESIS \ -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_LOCKING_STYLE=0 \ -DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_MEMSYS5 \ -DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \ -DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM \ -DSQLITE_OMIT_BUILTIN_TEST -DSQLITE_OMIT_COMPLETE \ -DSQLITE_OMIT_DATETIME_FUNCS -DSQLITE_OMIT_DEPRECATED \ -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \ -DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \ -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE \ -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_TRIGGER \ -DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0 \ -DSQLITE_ZERO_MALLOC sqlite3.c This will produce the following errors: sqlite3.c:(.text+0x462d2): undefined reference to `sqlite3AlterBeginAddColumn' sqlite3.c:(.text+0x462fe): undefined reference to `sqlite3AlterFinishAddColumn' sqlite3.c:(.text+0x46331): undefined reference to `sqlite3AlterRenameTable' sqlite3.c:(.text+0x46482): undefined reference to `sqlite3DropTrigger' sqlite3.c:(.text+0x4657b): undefined reference to `sqlite3TriggerSelectStep' sqlite3.c:(.text+0x465b9): undefined reference to `sqlite3TriggerDeleteStep' sqlite3.c:(.text+0x4660a): undefined reference to `sqlite3TriggerInsertStep' sqlite3.c:(.text+0x46661): undefined reference to `sqlite3TriggerInsertStep' sqlite3.c:(.text+0x466b0): undefined reference to `sqlite3TriggerUpdateStep' sqlite3.c:(.text+0x4688d): undefined reference to `sqlite3BeginTrigger' sqlite3.c:(.text+0x468f8): undefined reference to `sqlite3FinishTrigger' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
Im not a UTF expert but codepage 437 seems to work fine for your example.
codepage 65001 is not real UTF-8 according to several google sources.
You do have to use Lucida font.
C:\chcp 437
Active code page: 437
C:\sqlite test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
7
sqlite .quit
C:\chcp 65001
Active code page: 65001
C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual Studio
2005\Projects\sqlite\Debugsqlite
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
aborts
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Jens Frederich [jfreder...@gmail.com]
Sent: Tuesday, December 27, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] [shell] utf-8 bug
Hi all,
I believe there is a bug in shell tool on Windows. I've tried to store
utf-8 encoded literals in my test db. Do the following to reproduce the
issue:
1. Open a Windows Console (cmd.exe)
2. chcp 65001 # change cmd.exe code page from 437(OEM) to utf-8
3. Run the shell
sqlite3.exe test.db
create table test(id integer primary key, value text);
insert into test (id, value) values (1, 'füchsen'); # literial with
multi byte characters
The shell unexpectantly closed on the last command.
What is the preferred way to save utf-8 encoded literals with the windows
shell tool?
Jens
___
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] [shell] utf-8 bug
I'm an UTF-8 expert. Code page 65001 is CP_UTF8, look here (
http://msdn.microsoft.com/en-us/library/windows/desktop/ms683169(v=vs.85).aspx
).
I know that code page 437 (alias OEM) works. But the ü-character isn't
correct encoded in the database file.
The UTF-8 hex byte sequence of ü is \xBC\xC3. If you look at the database
via hexdump or xxd you can see that the ü-character is encoded with the hex
sequence 0x81. That's the OEM encoding and not UTF-8. I use my database on
different operating systems. Thus, it is very important that all characters
are correctly UTF-8 encoded.
The sqlite3 command line app doesn't write the string correctly to the
database file. It uses the terminal (cmd) encoding instead the 'PRAGMA
encoding' statement.
Jens
On Tue, Dec 27, 2011 at 6:29 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
Im not a UTF expert but codepage 437 seems to work fine for your example.
codepage 65001 is not real UTF-8 according to several google sources.
You do have to use Lucida font.
C:\chcp 437
Active code page: 437
C:\sqlite test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
7
sqlite .quit
C:\chcp 65001
Active code page: 65001
C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual
Studio 2005\Projects\sqlite\Debugsqlite
SQLite version 3.7.9 2011-11-01 00:52:41
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select length('füchsen');
aborts
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Jens Frederich [jfreder...@gmail.com]
Sent: Tuesday, December 27, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] [shell] utf-8 bug
Hi all,
I believe there is a bug in shell tool on Windows. I've tried to store
utf-8 encoded literals in my test db. Do the following to reproduce the
issue:
1. Open a Windows Console (cmd.exe)
2. chcp 65001 # change cmd.exe code page from 437(OEM) to utf-8
3. Run the shell
sqlite3.exe test.db
create table test(id integer primary key, value text);
insert into test (id, value) values (1, 'füchsen'); # literial with
multi byte characters
The shell unexpectantly closed on the last command.
What is the preferred way to save utf-8 encoded literals with the windows
shell tool?
Jens
___
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] [shell] utf-8 bug
On Tue, Dec 27, 2011 at 12:44 PM, Jens Frederich jfreder...@gmail.com wrote: The sqlite3 command line app doesn't write the string correctly to the database file. It uses the terminal (cmd) encoding instead the 'PRAGMA encoding' statement. None of the SQLite3 code converts between encodings (other than UTF8-UTF-16). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
On 12/27/11, Jens Frederich jfreder...@gmail.com wrote: The sqlite3 command line app doesn't write the string correctly to the database file. It uses the terminal (cmd) encoding instead the 'PRAGMA encoding' statement. chcp 65001 ...may not be a reliable avenue: https://www.google.com/search?q=codepage+65001+Windows+WriteFile+bug -- -- -- -- --ô¿ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [shell] utf-8 bug
Thank you! What a mess... On Tue, Dec 27, 2011 at 9:00 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: On 12/27/11, Jens Frederich jfreder...@gmail.com wrote: The sqlite3 command line app doesn't write the string correctly to the database file. It uses the terminal (cmd) encoding instead the 'PRAGMA encoding' statement. chcp 65001 ...may not be a reliable avenue: https://www.google.com/search?q=codepage+65001+Windows+WriteFile+bug -- -- -- -- --ô¿ô-- K e V i N ___ 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] [shell] utf-8 bug
Hi all, I believe there is a bug in shell tool on Windows. I've tried to store utf-8 encoded literals in my test db. Do the following to reproduce the issue: 1. Open a Windows Console (cmd.exe) 2. chcp 65001 # change cmd.exe code page from 437(OEM) to utf-8 3. Run the shell sqlite3.exe test.db create table test(id integer primary key, value text); insert into test (id, value) values (1, 'füchsen'); # literial with multi byte characters The shell unexpectantly closed on the last command. What is the preferred way to save utf-8 encoded literals with the windows shell tool? Jens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained minor regression (bug) 3.7.8 up
On Wed, Dec 7, 2011 at 2:48 AM, Max Vlasov max.vla...@gmail.com wrote: btw, could not find exact steps for getting trunk amalgamation. Is it : - get http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk - extract on a unix-compatible machine - ./configure - make sqlite3.c You built the amalgamation correctly. That's all there is to it. -- 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] Unexplained minor regression (bug) 3.7.8 up
Hi, Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9) I have a Russian morphology database and different queries working with it. I narrowed it to the following case and populated with a couple of English words (just to make sense) The following database CREATE TABLE [Beginnings] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Title] TEXT, [EndingId] INTEGER); CREATE TABLE [Endings] (Id INT,Title TEXT,EndingId INT); INSERT INTO Beginnings (Id, Title, EndingId) VALUES (1, 'FACTOR', 18); INSERT INTO Beginnings (Id, Title, EndingId) VALUES (2, 'SWIMM', 18); INSERT INTO Endings (Id, Title, EndingId) VALUES (1, 'ING', 18); There's a query that searches for primary form of a united list of some words (here FACTORING and SWIMMING): SELECT SrcWord, Beginnings.Title FROM (SELECT 'FACTORING' AS SrcWord UNION SELECT 'SWIMMING' AS SrcWord ) LEFT JOIN Beginnings WHERE Beginnings.Id= (SELECT BeginningId FROM (SELECT SrcWord, B.Id as BeginningId, B.Title || E.Title As Connected FROM Beginnings B LEFT JOIN Endings E ON B.EndingId=E.EndingId WHERE Connected=SrcWord LIMIT 1)) Sqlite versions before 3.7.7 (inclusive) returns results (2 rows). 3.7.8 and above shows empty result set Thanks, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained minor regression (bug) 3.7.8 up
On 12/06/2011 03:28 PM, Max Vlasov wrote: Hi, Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9) I have a Russian morphology database and different queries working with it. I narrowed it to the following case and populated with a couple of English words (just to make sense) The following database CREATE TABLE [Beginnings] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Title] TEXT, [EndingId] INTEGER); CREATE TABLE [Endings] (Id INT,Title TEXT,EndingId INT); INSERT INTO Beginnings (Id, Title, EndingId) VALUES (1, 'FACTOR', 18); INSERT INTO Beginnings (Id, Title, EndingId) VALUES (2, 'SWIMM', 18); INSERT INTO Endings (Id, Title, EndingId) VALUES (1, 'ING', 18); There's a query that searches for primary form of a united list of some words (here FACTORING and SWIMMING): SELECT SrcWord, Beginnings.Title FROM (SELECT 'FACTORING' AS SrcWord UNION SELECT 'SWIMMING' AS SrcWord ) LEFT JOIN Beginnings WHERE Beginnings.Id= (SELECT BeginningId FROM (SELECT SrcWord, B.Id as BeginningId, B.Title || E.Title As Connected FROM Beginnings B LEFT JOIN Endings E ON B.EndingId=E.EndingId WHERE Connected=SrcWord LIMIT 1)) There is a candidate fix for this in fossil now. Alternatively, a workaround with 3.7.9 is to remove the LIMIT 1 from the sub-query. Or replace the LEFT JOIN in the subquery with a regular JOIN. The query then seems to work as expected. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained minor regression (bug) 3.7.8 up
On Tue, Dec 6, 2011 at 9:49 PM, Dan Kennedy danielk1...@gmail.com wrote: On 12/06/2011 03:28 PM, Max Vlasov wrote: Hi, Noticed a strange regression after 3.7.7 (in 3.7.8 and inherited in 3.7.9) There is a candidate fix for this in fossil now. Dan, thanks I checked the latest trunk against my unprepared database and query, everything is fine. btw, could not find exact steps for getting trunk amalgamation. Is it : - get http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk - extract on a unix-compatible machine - ./configure - make sqlite3.c For me it worked, but maybe there are other things to mention. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? Can't tell from docs....
On Fri, Nov 18, 2011 at 05:30:20PM -0800, Yang Zhang scratched on the wall: I just got bit by some peculiar behavior in sqlite where id int primary key is different from: id integer primary key In particular, sqlite will generate values for the latter but not the former: Well, yes... They're different types. FLOAT and FLOATING POINT have very different behaviors as well (although not for the reasons you might guess). I couldn't find in http://www.sqlite.org/autoinc.html To quote: If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. [...] When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. The docs are quite specific that this is a column of type... and not just any column with an integer storage class. The phrase INTEGER PRIMARY KEY is also a link, which takes you to a section of the CREATE TABLE docs that includes this: Other integer type names like INT or BIGINT or SHORT INTEGER or UNSIGNED INTEGER causes the primary key column to behave as an ordinary table column... or http://www.sqlite.org/datatype3.html any mention of this peculiardistinguishing behavior. Again, the phrase INTEGER PRIMARY KEY appears on this page as a link to the above information that clearly states the column must be defined as an INTEGER. You seem to be assuming the defined type INT and the type INTEGER should be equivalent. Anyway, if this is intentional (as I'm guessing), I wouldn't have been able to tell from the docs - perhaps this would warrant special mention? Almost every use of the phrase INTEGER PRIMARY KEY in the documentation is a link that brings you right to this information. It seems like it would be difficult to make this any more accessible without copying the information to several different places on the website, thus bulking up the docs so much people don't bother to read them and making it much more difficult to maintain. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this a bug? Can't tell from docs....
I just got bit by some peculiar behavior in sqlite where id int primary key is different from: id integer primary key In particular, sqlite will generate values for the latter but not the former: sqlite create table a (a integer primary key, b integer);sqlite insert into a (b) values (0);sqlite select * from a;1|0sqlite create table b (a int primary key, b integer);sqlite insert into b (b) values (0);sqlite select * from b;|0 I couldn't find in http://www.sqlite.org/autoinc.html orhttp://www.sqlite.org/datatype3.html any mention of this peculiardistinguishing behavior. Anyway, if this is intentional (as I'm guessing), I wouldn't have been able to tell from the docs - perhaps this would warrant special mention? Just thought I'd bring this to your attention. -- Yang Zhang http://yz.mit.edu/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? Can't tell from docs....
Actually, it is documented, on the CREATE TABLE page, near the bottom, in the section titled ROWIDs and the INTEGER PRIMARY KEY. Not that this is an exactly obvious place to look for it... Best regards, Peter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Yang Zhang Sent: Friday, November 18, 2011 6:30 PM To: sqlite-users@sqlite.org Subject: [sqlite] Is this a bug? Can't tell from docs I just got bit by some peculiar behavior in sqlite where id int primary key is different from: id integer primary key In particular, sqlite will generate values for the latter but not the former: sqlite create table a (a integer primary key, b integer);sqlite insert into a (b) values (0);sqlite select * from a;1|0sqlite create table b (a int primary key, b integer);sqlite insert into b (b) values (0);sqlite select * from b;|0 I couldn't find in http://www.sqlite.org/autoinc.html orhttp://www.sqlite.org/datatype3.html any mention of this peculiardistinguishing behavior. Anyway, if this is intentional (as I'm guessing), I wouldn't have been able to tell from the docs - perhaps this would warrant special mention? Just thought I'd bring this to your attention. -- Yang Zhang http://yz.mit.edu/ ___ 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] [patch] Re: Potential bug: insert into X select * from Y ignores the ON CONFLICT REPLACE conflict-clause
Yuriy Kaminskiy wrote: Yuriy Kaminskiy wrote: David wrote: Simon L wrote 2011-10-25 06:20: To reproduce this problem, enter the following 5 SQL statements at the SQLite command line. create table X(id INTEGER primary key ON CONFLICT REPLACE); create table Y(id INTEGER primary key ON CONFLICT REPLACE); insert into X values (1); insert into Y select * from X; insert into Y select * from X; When I tried to run the last SQL statement twice, SQLite produced the following error message. Error: PRIMARY KEY must be unique Is this a bug? Please advise. Thank you. This certainly looks like a bug. I got a constraint failure when I tried it in sqlite 3.7.8. But it works fine when you state the column name explicitly in the select clause. Like this: create table X(id INTEGER primary key ON CONFLICT REPLACE); create table Y(id INTEGER primary key ON CONFLICT REPLACE); insert into X values (1); insert into Y select id from X; insert into Y select id from X; I recall the INSERT INTO ... SELECT * ... had a bug related to foreign key checks, which was reported on the mailing list earlier this year: http://www.sqlite.org/src/tktview?name=6284df89de Hopefully, a member of the sqlite dev team will acknowledge this bug soon. Thanks for pointer; root cause, indeed, transfer optimization (it ignores table INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use table's ON CONFLICT clause by default; falls back to regular transfer if destination table is not empty and we cannot handle ON CONFLICT resolution); Index: sqlite3-3.7.8/src/insert.c === --- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.0 +0400 +++ sqlite3-3.7.8/src/insert.c 2011-10-25 15:54:54.0 +0400 Ping. Okey, I've noticed http://www.sqlite.org/src/info/6f9898db7f Won't that result in performance regression in VACUUM? [only on tables with INTEGER PRIMARY KEY ON REPLACE xxx, obviously; so not *terrible* big deal - but still] When we insert into empty table, ON CONFLICT will never trigger, so we can safely use optimized xfer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [patch] Re: Potential bug: insert into X select * from Y ignores the ON CONFLICT REPLACE conflict-clause
Yuriy Kaminskiy wrote: David wrote: Simon L wrote 2011-10-25 06:20: To reproduce this problem, enter the following 5 SQL statements at the SQLite command line. create table X(id INTEGER primary key ON CONFLICT REPLACE); create table Y(id INTEGER primary key ON CONFLICT REPLACE); insert into X values (1); insert into Y select * from X; insert into Y select * from X; When I tried to run the last SQL statement twice, SQLite produced the following error message. Error: PRIMARY KEY must be unique Is this a bug? Please advise. Thank you. This certainly looks like a bug. I got a constraint failure when I tried it in sqlite 3.7.8. But it works fine when you state the column name explicitly in the select clause. Like this: create table X(id INTEGER primary key ON CONFLICT REPLACE); create table Y(id INTEGER primary key ON CONFLICT REPLACE); insert into X values (1); insert into Y select id from X; insert into Y select id from X; I recall the INSERT INTO ... SELECT * ... had a bug related to foreign key checks, which was reported on the mailing list earlier this year: http://www.sqlite.org/src/tktview?name=6284df89de Hopefully, a member of the sqlite dev team will acknowledge this bug soon. Thanks for pointer; root cause, indeed, transfer optimization (it ignores table INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use table's ON CONFLICT clause by default; falls back to regular transfer if destination table is not empty and we cannot handle ON CONFLICT resolution); Index: sqlite3-3.7.8/src/insert.c === --- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.0 +0400 +++ sqlite3-3.7.8/src/insert.c2011-10-25 15:54:54.0 +0400 Ping. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [patch] Re: Potential bug: insert into X select * from Y ignores the ON CONFLICT REPLACE conflict-clause
David wrote:
Simon L wrote 2011-10-25 06:20:
To reproduce this problem, enter the following 5 SQL statements at the
SQLite command line.
create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select * from X;
insert into Y select * from X;
When I tried to run the last SQL statement twice, SQLite produced the
following error message.
Error: PRIMARY KEY must be unique
Is this a bug? Please advise. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This certainly looks like a bug. I got a constraint failure when I tried
it in sqlite 3.7.8.
But it works fine when you state the column name explicitly in the
select clause.
Like this:
create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select id from X;
insert into Y select id from X;
I recall the INSERT INTO ... SELECT * ... had a bug related to foreign
key checks,
which was reported on the mailing list earlier this year:
http://www.sqlite.org/src/tktview?name=6284df89de
Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
Thanks for pointer; root cause, indeed, transfer optimization (it ignores table
INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use table's
ON CONFLICT clause by default; falls back to regular transfer if destination
table is not empty and we cannot handle ON CONFLICT resolution);
Disclaimer: /me is not sqlite dev team member, review carefully, use with care.
The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.
Signed-off-by: Yuriy M. Kaminskiy yum...@gmail.com
Index: sqlite3-3.7.8/src/insert.c
===
--- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.0 +0400
+++ sqlite3-3.7.8/src/insert.c 2011-10-25 15:54:54.0 +0400
@@ -1626,6 +1626,7 @@ static int xferOptimization(
int regAutoinc; /* Memory register used by AUTOINC */
int destHasUniqueIdx = 0;/* True if pDest has a UNIQUE index */
int regData, regRowid; /* Registers holding data and rowid */
+ int keyConf = pDest-iPKey=0 ? pDest-keyConf : OE_Default;
if( pSelect==0 ){
return 0; /* Must be of the form INSERT INTO ... SELECT ... */
@@ -1639,7 +1640,22 @@ static int xferOptimization(
}
#endif
if( onError==OE_Default ){
-onError = OE_Abort;
+if( keyConf==OE_Abort || keyConf==OE_Rollback )
+ /* can be handled - take ON CONFLICT from table declaration */
+ onError = keyConf;
+else {
+ if( keyConf==OE_Default )
+keyConf = OE_Abort;
+ else {
+/* Not default and cannot be handled;
+** fallback to regular transfer if destination is not empty (below)
+*/
+ }
+ onError = OE_Abort;
+}
+ } else {
+/* statement ON CONFLICT overrides table ON CONFLICT */
+keyConf = onError;
}
if( onError!=OE_Abort onError!=OE_Rollback ){
return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
@@ -1766,7 +1782,8 @@ static int xferOptimization(
iDest = pParse-nTab++;
regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
- if( (pDest-iPKey0 pDest-pIndex!=0) || destHasUniqueIdx ){
+ if( (pDest-iPKey0 pDest-pIndex!=0) || destHasUniqueIdx ||
+ (/*pDest-iPKey=0 */keyConf!=onError) ) {
/* If tables do not have an INTEGER PRIMARY KEY and there
** are indices to be copied and the destination is not empty,
** we have to disallow the transfer optimization because the
@@ -1776,6 +1793,9 @@ static int xferOptimization(
** we also disallow the transfer optimization because we cannot
** insure that all entries in the union of DEST and SRC will be
** unique.
+**
+** Or if destination INTEGER PRIMARY KEY has ON CONFLICT clause
+** that we cannot handle and destination is not empty.
*/
addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Reserved Words Bug
Thanks for fixing that so quickly. Looking forward to a new release. Patrick Earl On Sat, Jul 9, 2011 at 2:39 PM, Joe Mistachkin sql...@mistachkin.com wrote: Patrick Earl wrote: System.Resources.MissingManifestResourceException was unhandled Message=Could not find any resources appropriate for the specified culture or the neutral culture. Make sure System.Data.SQLite.SR.resources was correctly embedded or linked into assembly System.Data.SQLite at compile time, or that all the satellite assemblies required are loadable and fully signed. This issue appears to be caused by an incorrect resource name in the mixed-mode assembly compiled with VS 2010. The following line in the project file SQLite.Interop.2010.vcxproj is incorrect: LogicalName$(IntDir)System.Data.SQLite.%(Filename).resources/LogicalName It should read: LogicalNameSystem.Data.SQLite.%(Filename).resources/LogicalName This issue has been fixed in: http://system.data.sqlite.org/index.html/ci/55f56ce508 Thanks for pointing out this problem. -- Joe Mistachkin ___ 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] System.Data.SQLite Reserved Words Bug
First I wanted to say that I was so excited to see the 1.0.74 release
with .NET 4, zip files, and SQLite 3.7.7. I've been waiting for .NET
4 support for a long while. Thanks so much. :)
Unfortunately, I was unable to upgrade from 1.0.66 because of the
following problem.
Using this code produces the following exception:
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
SQLiteConnection conn = new SQLiteConnection(Data Source=test.db);
conn.Open();
conn.GetSchema(ReservedWords);
}
}
}
/*
System.Resources.MissingManifestResourceException was unhandled
Message=Could not find any resources appropriate for the specified
culture or the neutral culture. Make sure
System.Data.SQLite.SR.resources was correctly embedded or linked
into assembly System.Data.SQLite at compile time, or that all the
satellite assemblies required are loadable and fully signed.
Source=mscorlib
StackTrace:
at
System.Resources.ManifestBasedResourceGroveler.HandleResourceStreamMissing(String
fileName)
at
System.Resources.ManifestBasedResourceGroveler.GrovelForResourceSet(CultureInfo
culture, Dictionary`2 localResourceSets, Boolean tryParents, Boolean
createIfNotExists, StackCrawlMark stackMark)
at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo
requestedCulture, Boolean createIfNotExists, Boolean tryParents,
StackCrawlMark stackMark)
at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo
culture, Boolean createIfNotExists, Boolean tryParents)
at System.Resources.ResourceManager.GetString(String name,
CultureInfo culture)
at System.Data.SQLite.SR.get_Keywords() in
c:\dev\sqlite\dotnet\System.Data.SQLite\SR.Designer.cs:line 87
at System.Data.SQLite.SQLiteConnection.Schema_ReservedWords()
in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line
1239
at System.Data.SQLite.SQLiteConnection.GetSchema(String
collectionName, String[] restrictionValues) in
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 1223
at System.Data.SQLite.SQLiteConnection.GetSchema(String
collectionName) in
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:line 1176
at ConsoleApplication4.Program.Main(String[] args) in
c:\temp\projects\ConsoleApplication4\Program.cs:line 15
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly,
String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state, Boolean
ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
*/
Thanks for your help with this.
Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Reserved Words Bug
Patrick Earl wrote: System.Resources.MissingManifestResourceException was unhandled Message=Could not find any resources appropriate for the specified culture or the neutral culture. Make sure System.Data.SQLite.SR.resources was correctly embedded or linked into assembly System.Data.SQLite at compile time, or that all the satellite assemblies required are loadable and fully signed. This issue appears to be caused by an incorrect resource name in the mixed-mode assembly compiled with VS 2010. The following line in the project file SQLite.Interop.2010.vcxproj is incorrect: LogicalName$(IntDir)System.Data.SQLite.%(Filename).resources/LogicalName It should read: LogicalNameSystem.Data.SQLite.%(Filename).resources/LogicalName This issue has been fixed in: http://system.data.sqlite.org/index.html/ci/55f56ce508 Thanks for pointing out this problem. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
Hello there!
I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:
What am I missing here ?
SQLite version 3.7.5
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .mode column
sqlite .header on
sqlite select (select v from t1 where n=a) wrong,* from a1;
wrong a b
-- -- --
123 456
999 999
sqlite
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO a1 VALUES(123,456);
INSERT INTO a1 VALUES(999,999);
CREATE TABLE t1 (n text primary key on conflict replace,v integer);
INSERT INTO t1 VALUES('good',1000);
INSERT INTO t1 VALUES('a',2000);
COMMIT;
sqlite
Any light to the problem (especially if it is already fixed) is very
much appreciated.
My OS is NetBSD,
cheers thilo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On Tue, Jun 28, 2011 at 8:42 PM, thilo th...@nispuk.com wrote: sqlite select (select v from t1 where n=a) wrong,* from a1; use SINGLE quotes, not double quotes. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
string literals are enclose in single quotes not double quotes select (select v from t1 where n='a') wrong,* from a1; On 6/28/2011 11:42 AM, thilo wrote: select (select v from t1 where n=a) wrong,* from a1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
Use single quotes instead of double
sqlite select (select v from t1 where n='a') wrong,* from a1;
wrong|a|b
2000|123|456
2000|999|999
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of thilo [th...@nispuk.com]
Sent: Tuesday, June 28, 2011 1:42 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing
when column id matches the inner query
Hello there!
I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:
What am I missing here ?
SQLite version 3.7.5
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .mode column
sqlite .header on
sqlite select (select v from t1 where n=a) wrong,* from a1;
wrong a b
-- -- --
123 456
999 999
sqlite
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO a1 VALUES(123,456);
INSERT INTO a1 VALUES(999,999);
CREATE TABLE t1 (n text primary key on conflict replace,v integer);
INSERT INTO t1 VALUES('good',1000);
INSERT INTO t1 VALUES('a',2000);
COMMIT;
sqlite
Any light to the problem (especially if it is already fixed) is very
much appreciated.
My OS is NetBSD,
cheers thilo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On 6/28/2011 8:45 PM, Stephan Beal wrote: On Tue, Jun 28, 2011 at 8:42 PM, thilo th...@nispuk.com wrote: sqlite select (select v from t1 where n=a) wrong,* from a1; use SINGLE quotes, not double quotes. bummer, Thanks a lot thilo -- Dipl. Ing. Thilo Jeremias Zur Rabenwiese 14 27239 Twistringen T: +49 15782492240 T: +49 4243941633 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On Tue, Jun 28, 2011 at 8:50 PM, thilo th...@nispuk.com wrote: use SINGLE quotes, not double quotes. bummer, Thanks a lot i PROMISE that you won't find such an obvious bug in sqlite3 ;). sqlite uses single quotes because that's what ANSI SQL specifies. MySQL uses (or can use) double quotes, but that is an unportable SQL extension. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible NATURAL JOIN bug
Sqlite 3.7.5. Possible NATURAL JOIN bug. I have a ~100 MB database which gives me odd results when running two similar queries. I'm currently trying to create a minimal test case, but the error seems to go away when I try to narrow it down. In short, the queries look like this (scrambled :P)... SELECT N FROM (Y JOIN S USING (S_ID) JOIN B USING (A_ID)) NATURAL JOIN ((SELECT A_ID, N FROM C NATURAL JOIN A) NATURAL JOIN A) GROUP BY N ORDER BY N; 3 rows returned SELECT N FROM (Y JOIN S USING (S_ID) JOIN B USING (A_ID)) NATURAL JOIN ((SELECT A_ID, N FROM C NATURAL JOIN A)) GROUP BY N ORDER BY N; 4 rows returned Obviously, the extra natural join eliminates one row somehow. I'm quite convinced I'm facing a rare bug here. Where can I send my 100 MB database for assistance? (In case I don't manage to narrow it down.) Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl API doc bug (was Re: [3.7.4] [BUG] [TCL] busy handler not called on lock escalation)
Igor Tandetnik wrote: On 1/26/2011 6:39 PM, Eric Smith wrote: busy handler not called on lock escalation This behavior is by design. See http://sqlite.org/c3ref/busy_handler.html the part that talks about a deadlock. Understood agreed. This is a bug in the Tcl API documentation, which can lead the unwary Tcl programmer to believing the busy callback will be called in all cases of contention. I recommend adding some text to the section 'The busy method'. This example was largely lifted from the C API doc: == The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and throw an error instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite throws an error for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. See http://www.sqlite.org/lockingv3.html for more details. == Eric -- Eric A. Smith Slurm, n.: The slime that accumulates on the underside of a soap bar when it sits in the dish too long. -- Rich Hall, Sniglets ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL-query execution bug
Hello! I've found a bug in execution queries like this: SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) FROM table4 T4 WHERE T4.date_value=T2.date_value GROUP BY group) G ON G.group=T3.group) Such queries returns empty resultset because of invalid evaluation WHERE T4.date_value=T2.date_value. It seems that SQLite evaluates WHERE T4.date_value=null in fact. If we replace WHERE T4.date_value=T2.date_value with WHERE T4.date_value=const value everything will be okey. Sincerely Yours, Wadim Smirnov System architect Positive Technologies CJSC, Russia ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL-query execution bug
On Tue, Jan 18, 2011 at 12:07 PM, Vadim Smirnov vsmir...@ptsecurity.ruwrote: Hello! I've found a bug in execution queries like this: SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) FROM table4 T4 WHERE T4.date_value=T2.date_value GROUP BY group) G ON G.group=T3.group) Such queries returns empty resultset because of invalid evaluation WHERE T4.date_value=T2.date_value. It seems that SQLite evaluates WHERE T4.date_value=null in fact. It works when I try it. Why don't you send us a specific example that does not work for you (including CREATE TABLE statements and INSERTs to fill the tables with data) and we'll have another look. If we replace WHERE T4.date_value=T2.date_value with WHERE T4.date_value=const value everything will be okey. Sincerely Yours, Wadim Smirnov System architect Positive Technologies CJSC, Russia ___ 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] sqlite3 ltrim behaviour bug or feature?
Thanks Martin, Maybe the documentation could be extended: The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X. to: The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X. Similar to the regexp X ~ s/^[Y]*//g. Sorry for the noise thilo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 ltrim behaviour bug or feature?
Hi, the following seems wrong to me: bash-4.0# sqlite3 SQLite version 3.6.14.2 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select ltrim(12300567,1230); 567 sqlite select ltrim(012300567,0123); 567 sqlite select ltrim(12300567,123); 00567 sqlite Is the stripping of leading 0's intentional? (or a bug in my netbsd port?) How can I workaround this problem? cheers thilo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 ltrim behaviour bug or feature?
Hi, this is the expected behaviour. See http://www.sqlite.org/lang_corefunc.html The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X. The second argument is not a string but a set of characters. ltrim() strips leading zeros if you include a zero anywhere in your second argunent. This is the case in the first two examples. Martin Am 13.01.2011 14:41, schrieb Thilo Jeremias: Hi, the following seems wrong to me: bash-4.0# sqlite3 SQLite version 3.6.14.2 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select ltrim(12300567,1230); 567 sqlite select ltrim(012300567,0123); 567 sqlite select ltrim(12300567,123); 00567 sqlite Is the stripping of leading 0's intentional? (or a bug in my netbsd port?) How can I workaround this problem? cheers thilo ___ 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] 3.7.4 possible restore bug
On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. This is a kind of repost, there wasn't any answer for my initial one. Please let me know is this bug or not a bug, I can't sleep without knowing the truth :) Steps to reproduce without additional test data 1. download windows shell 3.7.4 binary 2. sqlite testdb CREATE TABLE [TableOne] ([VALUE] INTEGER); .quit 3. sqlite testdb BEGIN TRANSACTION; INSERT INTO TableOne (Value) VALUES (123); 4. while still running the shell, unexpectedly interrupt the executable with some external tools (task manager for example) after this there's a file called testdb-journal near testdb 5. sqlite testdb .quit The result: testdb-journal is still there. Expected result: the journal file should be deleted by sqlite. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.4 possible restore bug
Hi, 12/01/2011 12:27, Max Vlasov wrote: On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasovmax.vla...@gmail.com wrote: Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. This is a kind of repost, there wasn't any answer for my initial one. Please let me know is this bug or not a bug, I can't sleep without knowing the truth :) Steps to reproduce without additional test data [snip] 3.7.2 behaves identically. Before quitting: - select shows nothing - inserting another record removes journal Max Vlasov -- Regards, Hakki Dogusan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.4 possible restore bug
On Wed, Jan 12, 2011 at 5:27 AM, Max Vlasov max.vla...@gmail.com wrote: On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. This is a kind of repost, there wasn't any answer for my initial one. Please let me know is this bug or not a bug, I can't sleep without knowing the truth :) I suppose it would be a nice-to-have if SQLite were more aggressive about deleting stale journal files. But it is not a serious problem. There is no danger of database corruption here, or anything like that. The journal file is stale. It is not a hot journal. And it won't (and can't) be rolled back. It's just a junk file that got left around. It will get cleaned up on the next write transaction. I guess you are asking for an enhancement for it to be cleaned up on the next read transaction. So even though this is not currently a problem, fixing it might create real corruptions issues. The deletion of a journal files need to be done with extreme caution, lest they be deleted when they is still hot and hence cause database corruption. It will take us a lot of study and testing to verify that the deletion is safe. So this is not a simple fix. Steps to reproduce without additional test data 1. download windows shell 3.7.4 binary 2. sqlite testdb CREATE TABLE [TableOne] ([VALUE] INTEGER); .quit 3. sqlite testdb BEGIN TRANSACTION; INSERT INTO TableOne (Value) VALUES (123); 4. while still running the shell, unexpectedly interrupt the executable with some external tools (task manager for example) after this there's a file called testdb-journal near testdb 5. sqlite testdb .quit The result: testdb-journal is still there. Expected result: the journal file should be deleted by sqlite. Max Vlasov ___ 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] 3.7.4 possible restore bug
On Wed, Jan 12, 2011 at 3:48 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Jan 12, 2011 at 5:27 AM, Max Vlasov max.vla...@gmail.com wrote: On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. This is a kind of repost, there wasn't any answer for my initial one. Please let me know is this bug or not a bug, I can't sleep without knowing the truth :) The journal file is stale. It is not a hot journal. And it won't (and can't) be rolled back. It's just a junk file that got left around. It will get cleaned up on the next write transaction. I guess you are asking for an enhancement for it to be cleaned up on the next read transaction. Richard, thanks for the clarification, it was just a little strange that previous versions (at least 3.6.10 I mentioned) deletes the same journal file upon simple opening (no writing) so possibly something was introduced after that made things a little more complex as you described. Also generally speaking the presence of -journal always was a kind of visual indication that either a write operation in progress (if connection is live) or something ended unexpectedly on previous session (if it's closed), but it appears that currently even several read-only sessions after that can keep this file around for a long period of time and this logic no longer works. Anyway I can live with that (and also sleep:) Thanks Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.4 possible restore bug
Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. I don't know whether it's related the the contents of the file, but here the db files: www.maxerist.net/downloads/dbwithjournal.zip The steps to reproduce: unzip both files anywhere, open the db in sqlite shell (3.7.4), do the .quit and both the db and journal are still there. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.4 possible restore bug
On Thu, Dec 23, 2010 at 3:10 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, I experimented with artificial power loss (using hd box) and 3.7.4 both library and shell didn't restore the files to the initial state. 3.6.10 restores successfully. I don't know whether it's related the the contents of the file, but here the db files: http://www.maxerist.net/downloads/dbwithjournal.zip I did additional tests without power loss (just by killing the process). The result is the same (both the db and journal still exist) only when no single change is written to the db file (the original db and the db after the interruption are binary the same) and because of this it seems that the only problem is that the journal file is not deleted. If there was a real write to the db file, the restoration deletes the journal Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Weird CASCADE behavior: bug?
When I run the following piece of SQL in an empty database, I get a no such table: main.table_e error on the second DROP TABLE statement: CREATE TABLE table_e ( eid TEXT PRIMARY KEY ); CREATE TABLE table_t ( tid TEXT PRIMARY KEY, value TEXT ); CREATE TABLE table_b ( -- Foreign Keys eid TEXT REFERENCES table_e(eid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, tid TEXT REFERENCES table_t(tid) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, value TEXT, PRIMARY KEY (eid, tid) ); DROP TABLE table_e; DROP TABLE table_t; If I remove the lines that say ON DELETE CASCADE ON UPDATE CASCADE the problem goes away. Note that there are no records in any of these tables. Also, It doesn't matter whether I wrap the whole thing in a transaction or not. Is this expected behavior? Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory It's amazing what you can do with the right tools. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3: col name bug on select statemnt
[critical bug] sqlite3: col name bug on select statemnt
sqlite3 --version
3.7.3
this bug occures with count or sum .
c:\tmp sqlite3 test3.db
.header ON
-- bug
drop table t1;
drop table t2;
drop table t3;
CREATE TABLE t1 (col1 INTEGER);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2 as select col1 , count(*) from t1;
CREATE TABLE t3 as select col1 , sum(col1) from t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT [col1] FROM t2;
SELECT [col1] FROM t3;
.schema t2
.schema t3
--- Error: no such column: col1
--- Result : CREATE TABLE t2(col1 INT,count(*));
^^^^
workaround : CREATE TABLE t2 as select col1 as 'col1' , count(*) from
t1;
-- bug
drop table t1;
drop table t2;
CREATE TABLE t1 ([col-1] INTEGER);
CREATE TABLE t2 as select [col-1] , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2([col-1] INT,count(*));
-- bug
drop table t1;
drop table t2;
CREATE TABLE t1 (col-1 INTEGER);
CREATE TABLE t2 as select col-1 , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2(col-1 INT,count(*));
-- bug
drop table t1;
drop table t2;
CREATE TABLE t1 ('col1' INTEGER);
CREATE TABLE t2 as select col1 , count(*) from t1;
.schema t2
--- Result : CREATE TABLE t2(col1 INT,count(*));
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible database corruption bug in SQLite
A user has provided us with a script that appears to result in SQLite database corruption. The problem has existed in all versions of SQLite going back to 3.6.16 in June of 2009. A bisect shows that the problem was injected on 2009-06-17. The problem appears to be associated with incremental vacuum. We are still working to characterize the problem more precisely. The discovery of this bug so close to the release of version 3.7.1 is a coincidence. Nevertheless, we will likely be issuing version 3.7.2 within a few days in order to fix the problem. So if you are thinking of upgrading to 3.7.1, you might want to hold back for a day or two to see what our investigation of this new problem turns up. -- 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] Unicode command line bug in Windows version of sqlite3 with patch
Hello SQLite Team, We currently use sqlite 3.6.23. We have a big problem with characters with accents or other special characters in path to database file, for example in Czech Windows XP the Application Data folder is translated to Data aplikací so if the accented 'í' is in path the sqlite3.exe writes that it is unable to open file in this path. To workaround this problem we tried to find the source of this error. It lies in MultiByteToWideChar and WideCharToMultiByte with CP_UTF8 as encoding argument. If is instead used CP_ACP as ANSI encoding then there is no problem. In attachment you will find diff patch which works for us. But because SQLite3 source code is really big we can't be sure that this fix is correct and doesn't introduce any side effects. Thanks for your hard work and help Filip Kunc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode command line bug in Windows version of sqlite3 with patch
sqlite3_open[_v2] accepts all filenames in UTF-8 (although it doesn't check for valid UTF-8 string). So CP_UTF8 cannot be changed anywhere. OTOH maybe command line utility should have some logic of re-encoding of command line parameter from terminal encoding to UTF-8. But I'm not sure about that. Could you try to run sqlite3 from a batch file that is written in UTF-8 encoding (properly encode your path). I believe it will work this way... Pavel 2010/3/16 Kunc Filip k...@medictech.com: Hello SQLite Team, We currently use sqlite 3.6.23. We have a big problem with characters with accents or other special characters in path to database file, for example in Czech Windows XP the Application Data folder is translated to Data aplikací so if the accented 'í' is in path the sqlite3.exe writes that it is unable to open file in this path. To workaround this problem we tried to find the source of this error. It lies in MultiByteToWideChar and WideCharToMultiByte with CP_UTF8 as encoding argument. If is instead used CP_ACP as ANSI encoding then there is no problem. In attachment you will find diff patch which works for us. But because SQLite3 source code is really big we can't be sure that this fix is correct and doesn't introduce any side effects. Thanks for your hard work and help Filip Kunc ___ 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] Unicode command line bug in Windows version of sqlite3 with patch
We currently use sqlite 3.6.23. We have a big problem with characters with accents or other special characters in path to database file, for example in Czech Windows XP the Application Data folder is translated to Data aplikací so if the accented 'í' is in path the sqlite3.exe writes that it is unable to open file in this path. A much better solution is to use a MSYS terminal (installed by MinGW), so you have UTF-8 command-line and data entry/display without conversion. No need to patch anything. The culprit here isn't the command-line utility, but the WinDOS usage of old charset. In attachment you will find diff patch which works for us. But because SQLite3 source code is really big we can't be sure that this fix is correct and doesn't introduce any side effects. If you change input encoding and use your code page, then it's likely you'll going to do the same with data, which is plain wrong: SQLite needs UTF-8 (or UTF-16) data, not ANSI. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode command line bug in Windows version of sqlite3 with patch
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jean-Christophe Deschamps wrote: A much better solution is to use a MSYS terminal (installed by MinGW), so you have UTF-8 command-line and data entry/display without conversion. No need to patch anything. No need for msys. You can make a regular command prompt use UTF8 by switching to code page 65001. Either of these commands will do that. chcp 65001 mode con cp select=65001 You can use chcp or mode con to see the current code page. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuf/5UACgkQmOOfHg372QS+xACg0VZPfwEz4y2OAzs4OpHon+EG crIAoMsd8wVrRWhhPBouPVnI1m0M4lL/ =/eXW -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] llvm/ppc compile bug
Just a note to share a problem I ran into recently: Compiling sqlite 3.6.22 with -arch ppc -Os on the llvm that ships with Xcode 3.2.1, the sqlite3AtoF function appears to have an infinite loop. If you compile the sqlite3 command line tool in this way, just executing select round(1234); will cause it to hang. This version of llvm reports itself as i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5646) (LLVM build 2206) I haven't checked to see if there's a newer llvm which fixes this. Apple dudes: this is bugreporter #7599241. Hope this saves someone out there some trouble. :) -D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is this a bug?
Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite select id from test where (id 5); 6 7 8 9 10 [Good] sqlite select id from test where id in (select id from test where (id 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite select id from test where id in ((select id from test where (id 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite select id from test where id in (((select id from test where (id 5; 6 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
This is probably a bug in SQL parser, that doesn't reduced to one parenthesis, causing the side effect in VDBE engine, that doesn't returned all ID´s to IN evaluator in first select. This should be easily reproduced, but the fix you must wait for Richard or other that have knowledge on VDBE instruction debugging. I've tested the same thing on SQL Server 2008, correct results appeared: --- create database x go use x go create table test (id int not null, primary key(id)) go insert into test values (1) insert into test values (2) insert into test values (3) insert into test values (4) insert into test values (5) insert into test values (6) insert into test values (7) insert into test values (8) insert into test values (9) insert into test values (10) go select id from test where id in (select id from test where id 5) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in (select id from test where (id 5)) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in ((select id from test where (id 5))) go id --- 6 7 8 9 10 (5 row(s) affected) []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale Sent: sábado, 19 de dezembro de 2009 00:23 To: sqlite-users@sqlite.org Subject: [sqlite] is this a bug? Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite select id from test where (id 5); 6 7 8 9 10 [Good] sqlite select id from test where id in (select id from test where (id 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite select id from test where id in ((select id from test where (id 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite select id from test where id in (((select id from test where (id 5; 6 ___ 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] is this a bug?
On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale vale...@aimale.com wrote: Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite select id from test where (id 5); 6 7 8 9 10 [Good] sqlite select id from test where id in (select id from test where (id 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite select id from test where id in ((select id from test where (id 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite select id from test where id in (((select id from test where (id 5; 6 Given the above table sqlite SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10); id -- 6 7 8 9 10 sqlite SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10)); SQL error: near ,: syntax error sqlite Seems like IN expects a comma separated list, and nothing else within a single set of parens. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
It´s good to try to reproduce all conditions that this problem happens, to help with creating test-cases and with bug fix. []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: sábado, 19 de dezembro de 2009 00:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] is this a bug? On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale vale...@aimale.com wrote: Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite select id from test where (id 5); 6 7 8 9 10 [Good] sqlite select id from test where id in (select id from test where (id 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite select id from test where id in ((select id from test where (id 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite select id from test where id in (((select id from test where (id 5; 6 Given the above table sqlite SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10); id -- 6 7 8 9 10 sqlite SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10)); SQL error: near ,: syntax error sqlite Seems like IN expects a comma separated list, and nothing else within a single set of parens. -- Puneet Kishor ___ 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] is this a bug?
On Fri, Dec 18, 2009 at 07:23:24PM -0700, Valerio Aimale scratched on the wall: Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. Why only one value returned when parenthesis are duplicated? Wrapping a sub-SELECT in parenthesis turns it into an expression, rather than a result-set. This is done by returning the first value. See the diagram here: http://www.sqlite.org/lang_expr.html In your specific case, the IN operator is defined as: expression IN ( select | expression-list ) In other words, it allows either a SELECT -or- one or more expressions. If it is a sub-SELECT that has one column, the IN operator is smart enough to consider the returned column to be an expression set. However, when you wrap the sub-SELECT in parenthesis, it becomes a scalar expression of only one value (the first row), so you're IN test-set has only one value, and returns only one row in the super-SELECT. If you're using a sub-SELECT, the IN operator must see it directly. Same with triple parenthesis enclosing: An expression in parenthesis is still just an expression. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] floor help (plus bug found)
Hi,
At 00:11 13/12/2009, you wrote:
Sir any ida how can value rounddown floor have done
if not possible i have make small code
i requard make function please say how can add
i send you my rounddown funtion
please
Cose Exmaple :
value=10.666
decimal=1
Create roundd{value,decimal){
if (decimal0)
d=help requaird on decimal(10**)
Select Cast(value As integer)||substr((value-Cast(value As
integer))*10*d,1,decimal)*(1/d) As rounddown;
return rounddown;
}
i am c# devloper so i know padright please make for me complete
function for rounddown my arjent requairment
how add on SQLite please say
thanks advance
I am forwarding your mail to the list as well because readers may be
able to help you on the C# part.
If your question is about rounding values at the SQLite level at some
fixed number of decimal place, here is an example using the sqlite3
program.
Say we have a table created:
CREATE TABLE Samples (myValue FLOAT);
Now let us insert some values both positive and negative:
INSERT INTO Samples VALUES(6206.460984);
INSERT INTO Samples VALUES(259.026716);
INSERT INTO Samples VALUES(652864.9244028);
INSERT INTO Samples VALUES(5866.5317364);
INSERT INTO Samples VALUES(13.4058616);
INSERT INTO Samples VALUES(0.444);
INSERT INTO Samples VALUES(0.);
INSERT INTO Samples VALUES(1.0);
INSERT INTO Samples VALUES(-6206.460984);
INSERT INTO Samples VALUES(-259.026716);
INSERT INTO Samples VALUES(-652864.9244028);
INSERT INTO Samples VALUES(-5866.5317364);
INSERT INTO Samples VALUES(-13.4058616);
INSERT INTO Samples VALUES(-0.444);
INSERT INTO Samples VALUES(-0.);
INSERT INTO Samples VALUES(-1.0);
Now execute a simple query to show you how the round() SQLite function
works:
select myValue, round(myValue, 3), round(myValue) from Samples;
6206.460984 6206.4616206.0
259.026716 259.027 259.0
652864.9244028 652864.924 652865.0
5866.53173645866.5325867.0
13.4058616 13.406 13.0
0.444 0.444 0.0
0. 0.889 1.0
1.0 1.0 1.0
-6206.460984-6206.461 -6206.0
-259.026716 -259.027-259.0
-652864.9244028 -652864.924 -652865.0
-5866.5317364 -5866.532 -5867.0
-13.4058616 -13.406 -13.0
-0.444 -0.444 0.0
-0. -0.889 -1.0
-1.0-1.0-1.0
You can see that SQLite round(myValue, 3) rounds to the 3rd decimal
place and round(myValue) or round(myValue, 0) rounds down to integer (0
decimal places).
But if you look more closely, you can also notice that the rounding
direction is not correct for positive values with a fractional part
when a non-null second parameter is given. I did not notice that point
in my first reply because I only rounded to integers.
I believe there is a bug here.
Take for instance the first value above: 6206.460984
It is correctly rounded _down_ to the integer value 6206
it is wrongly rounded _up_ to the 3rd decimal place 6206.461
but the correct rounding down should be 6206.460
Rounding down negative values (integral or not) works well.
Thus the correct rounding down at 3rd decimal places using SQLite can
be done so:
case
when myValue 0 and cast(myValue as text) round(myValue) then
round(myValue - 0.00051, 3)
else
round(myValue, 3)
end
Let us see if that does what we want:
select myValue, case when myValue 0 and cast(myValue as text)
round(myValue) then round(myValue - 0.00051, 3) else round(myValue, 3)
end as Correct rounding from Samples;
6206.460984 6206.46
259.026716 259.026
652864.9244028 652864.924
5866.53173645866.531
13.4058616 13.405
0.444 0.444
0. 0.888
1 1.0
-6206.460984-6206.461
-259.026716 -259.027
-652864.9244028 -652864.924
-5866.5317364 -5866.532
-13.4058616 -13.406
-0.444 -0.444
-0. -0.889
-1 -1.0
The output is now correct in every case (I hope so) but it is finally
much less practical than we would like! Working with floating point
can bring unexpected problems.
Now if you need to write a similar function to perform the same
operation in C# then you should search MSDN C#. It is possible that
someone here with some C# knowledge could help you.
Anyway may I strongly suggest you read tutorials or books about your
language and also read the SQLite tutorial. Also you would certainly
benefit from reading about the virtues and the dark sides of
floating-point. Finally such rounding could be better done at some
point in the applicative code: it all depends on your application.
I hope this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
