Re: [sqlite] Bug in sqlite3.c
I tried "INSERT INTO ft(ft) VALUES('integrity-check')" and it also did not
give any error. But the rebuild command helped solve my problem as the
crashes stopped, otherwise my application was crashing at every commit
operation (thanks a lot for that).
Is there any command/API which can bypass FTS5 when the db itself has been
instructed to use the FTS5 extension ?
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.c
On 4/6/62 12:11, bhandari_nikhil wrote:
Thanks Dan. I had checked the database integrity using the following command:
sqlite3 myfile.db "PRAGMA integrity_check;"
Try "INSERT INTO ft(ft) VALUES('integrity-check')", where "ft" is the
name of the fts5 table.
And it had reported ok. I will see if I can share the database file here.
Can you let me know how to check the db file (in case I am not able to share
the db file here) ? And how the fts5 can get corrupted ?
The easiest explanation is that the fts5 tables were modified directly,
bypassing fts5. Or there could be a bug in fts5 - a bug that may or may not
still be present; there have been fixes since 3.14. A memory related bug in the
application could also cause this.
To run the rebuild command, the ft refers to the db name ?
The fts5 table name.
Dan.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.c
Thanks Dan. I had checked the database integrity using the following command: sqlite3 myfile.db "PRAGMA integrity_check;" And it had reported ok. I will see if I can share the database file here. Can you let me know how to check the db file (in case I am not able to share the db file here) ? And how the fts5 can get corrupted ? To run the rebuild command, the ft refers to the db name ? BTW, I just looked at the code, not used the latest version. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.c
On 3/6/62 12:51, bhandari_nikhil wrote: I am facing a crash in sqlite3fts5BufferAppendBlob. Following is the backtrace: #0 sqlite3Fts5BufferAppendBlob (pRc=pRc@entry=0xf54139c8, pBuf=pBuf@entry=0xf5afeb90, nData=4294967295, pData=0xf49fff76 "90246ture") at sqlite3.c:180474 #1 0xf717b6f8 in fts5WriteAppendTerm (p=p@entry=0xf54139a8, pWriter=pWriter@entry=0xf5afeb84, nTerm=5, pTerm=0xf49fff70 "06280290246ture") at sqlite3.c:188868 #2 0xf717bf29 in fts5IndexMergeLevel (p=p@entry=0xf54139a8, ppStruct=ppStruct@entry=0xf5afec3c, iLvl=3, pnRem=0xf5afec38) at sqlite3.c:189176 There is an apparent bug in sqlite3fts5BufferAppendBlob where it is asserting for check on nData < 0 but nData is actually u32. The nData should be int, not u32. I am using version 3.14.0.100 but the bug is present in the latest version as well. Also, if you notice in frame #0, the nData passed is 0x which is -1. It was calculated to be -1 in frame #1 where it did nTerm - nPrefix. The nPrefix value came out to be 6 and nTerm was 5. I want to know when this nPrefix becomes > nTerm ? Thanks for reporting this. I think that can only happen if the FTS5 records stored in the database are corrupt. If you are able to share the database I can check for you. You can probably repair the index using the following: https://sqlite.org/fts5.html#the_rebuild_command Also, I would have thought this crash would have been fixed by this change, which is in 3.28.0: https://sqlite.org/src/info/673a7dd698 Have you demonstrated the crash with the latest version, or just eyeballed the code? Cheers then, Dan. Regards Nikhil Bhandari -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite3.c
I am facing a crash in sqlite3fts5BufferAppendBlob. Following is the backtrace: #0 sqlite3Fts5BufferAppendBlob (pRc=pRc@entry=0xf54139c8, pBuf=pBuf@entry=0xf5afeb90, nData=4294967295, pData=0xf49fff76 "90246ture") at sqlite3.c:180474 #1 0xf717b6f8 in fts5WriteAppendTerm (p=p@entry=0xf54139a8, pWriter=pWriter@entry=0xf5afeb84, nTerm=5, pTerm=0xf49fff70 "06280290246ture") at sqlite3.c:188868 #2 0xf717bf29 in fts5IndexMergeLevel (p=p@entry=0xf54139a8, ppStruct=ppStruct@entry=0xf5afec3c, iLvl=3, pnRem=0xf5afec38) at sqlite3.c:189176 There is an apparent bug in sqlite3fts5BufferAppendBlob where it is asserting for check on nData < 0 but nData is actually u32. The nData should be int, not u32. I am using version 3.14.0.100 but the bug is present in the latest version as well. Also, if you notice in frame #0, the nData passed is 0x which is -1. It was calculated to be -1 in frame #1 where it did nTerm - nPrefix. The nPrefix value came out to be 6 and nTerm was 5. I want to know when this nPrefix becomes > nTerm ? Regards Nikhil Bhandari -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in sqlite3??
Let's not forget that the size of the database is going to grow above and beyond the "number of rows" due to pages that aren't reserved for your data, such index pages, etc. On Tue, May 28, 2019 at 1:49 PM Jens Alfke wrote: > > > > On May 26, 2019, at 7:21 PM, John Brigham wrote: > > > > The size of the file reflects the number of rows that should be > present. And furthermore, the size of the file nicely reflects the number > of days. […] Trust me when I say that this file is way to big for the > number of rows. > > SQLite files can contain free space after rows are deleted. (The free > space will eventually be reused for new data, or it can be reclaimed using > the VACUUM pragma.) So the size of the file does not necessarily reflect > the amount of data it currently contains. > > Try opening a copy of the database with the `sqlite3` CLI tool and > entering “PRAGMA vacuum;”. Then exit and look at the file size. > > —Jens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in sqlite3??
On 28 May 2019, at 6:49pm, Jens Alfke wrote: > Try opening a copy of the database with the `sqlite3` CLI tool and entering > “PRAGMA vacuum;”. Then exit and look at the file size. Alternatively use the sqlite3_analyze tool and read the "Pages on the freelist" figures. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in sqlite3??
> On May 26, 2019, at 7:21 PM, John Brigham wrote: > > The size of the file reflects the number of rows that should be present. And > furthermore, the size of the file nicely reflects the number of days. […] > Trust me when I say that this file is way to big for the number of rows. SQLite files can contain free space after rows are deleted. (The free space will eventually be reused for new data, or it can be reclaimed using the VACUUM pragma.) So the size of the file does not necessarily reflect the amount of data it currently contains. Try opening a copy of the database with the `sqlite3` CLI tool and entering “PRAGMA vacuum;”. Then exit and look at the file size. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in sqlite3??
On 5/26/19, John Brigham wrote: > I have an Arduino/Python experiment that generates lots of simple > numerical data; about 7000 records a day. I run it through Python into an > sqlite3 database.I have been running this for 8 months. I start a new > database about every six weeks so every database has about 260k rows. I > have one database that is missing the first three weeks. The "dec3_database" contains 168601 records from 2018-12-03 10:50:07.64 to 2018-12-26 21:46:53.346000 in the "first_table" table, but then just 9 more record in the "dec26_table" table and 341 more records in "dec26_table_cont". > There is evidence > that the records are in the file but not appearing: The size of the file > reflects the number of rows that should be present. And furthermore, the > size of the file nicely reflects the number of days. How many rows were you expecting, and covering what dates? > My objectives are > twofold: first, can you fix this faulty file The file looks to be intact to me. I'm not sure what about it needs fixing? > and second, this appears to be > a bug in your software and I want you informed. What makes you think this bug is in SQLite and not in your python script? Do you have an example of SQLite doing something other than what your script specifically asked it to do? And do you know for certain that some other agent (another script, or perhaps a human using a database inspection tool) didn't connect to the database in the middle of the session and change the table names around on you? > I use DBrowser and am > satisfied with it. I have knowledge about CLI SQL, but am not presently > using it. The software can be seen at > https://github.com/mrphysh?tab=repositories I quickly check the link and see > that the example data-base is the exact one.Notice that the file name is > dec3. And notice that the database starts with dec 26. I didn't notice that. The first record in the "first_table" table seems to be dated "2018-12-03 10:50:07.64" On the other hand, it is unclear to me why you have three separate tables "first_table", "dec26_table", and "dec26_table_cont", all with the same schema and similar data. So perhaps I am misinterpreting the data. I also notice that your columns are named "column_one", "column_two", "column_three", and so forth. Is that deliberate, or have you post-processed the database to obfuscate these column names? If the latter, might the missing rows have been deleted by the post-processing and obfuscation step? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug in sqlite3??
I have an Arduino/Python experiment that generates lots of simple numerical data; about 7000 records a day. I run it through Python into an sqlite3 database.I have been running this for 8 months. I start a new database about every six weeks so every database has about 260k rows. I have one database that is missing the first three weeks. There is evidence that the records are in the file but not appearing: The size of the file reflects the number of rows that should be present. And furthermore, the size of the file nicely reflects the number of days. My objectives are twofold: first, can you fix this faulty file and second, this appears to be a bug in your software and I want you informed. I use DBrowser and am satisfied with it. I have knowledge about CLI SQL, but am not presently using it. The software can be seen at https://github.com/mrphysh?tab=repositories I quickly check the link and see that the example data-base is the exact one.Notice that the file name is dec3. And notice that the database starts with dec 26. Trust me when I say that this file is way to big for the number of rows. JohnAsheville ncmrph...@juno.com Sad News For Meghan Markle And Prince Harry track.volutrk.com http://thirdpartyoffers.juno.com/TGL3141/5ceb49d9e56fe49d939d6st03vuc ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
Works fine for me on Centos ... using the default (ancient) version of SQLite3 # sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);" # touch /tmp/test.init # sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 # sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 [root@mail ~]# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" -- Loading resources from /tmp/test.init 1 # sqlite3 --version 3.6.20 # and with the current tip of trunk # ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" -- Loading resources from /tmp/test.init 1 # ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 # ./sqlite3 --version 3.28.0 2019-03-31 18:17:00 d03b611302f68483770d49b113b4ed685ba03526d2007647c306f8ec7ae697d2 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of >softw...@quantentunnel.de >Sent: Saturday, 30 March, 2019 05:28 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu) > >Dear colleagues > >I detected an unexpected behaviour in sqlite3 (CLI) that I consider a >bug as it seems not documented. > >When using an init file (even if an empty file), sqlite3 outputs an >extra empty line to stdout. This messes up parsing of the sqlite3 >output, as this line is not present in the absence of an init file: > >buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO >test VALUES(1);" >buero:~$ touch /tmp/test.init >buero:~$ ls -l /tmp/test.* >-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db >-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init >buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null >1 >buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM >test;" 2>/dev/null > >1 >buero:~$ > > >My configuration >buero:~$ sqlite3 --version >3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f >buero:~$ uname -a >Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 >17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
> Dear colleagues
>
> I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as
> it seems not documented.
>
> When using an init file (even if an empty file), sqlite3 outputs an extra
> empty line to stdout. This messes up parsing of the sqlite3 output, as this
> line is not present in the absence of an init file:
>
> buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test
> VALUES(1);"
> buero:~$ touch /tmp/test.init
> buero:~$ ls -l /tmp/test.*
> -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
> -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
> buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
> 1
> buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
> 2>/dev/null
>
> 1
> buero:~$
>
>
> My configuration
> buero:~$ sqlite3 --version
> 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
> buero:~$ uname -a
> Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC
> 2019 x86_64 x86_64 x86_64 GNU/Linux
> ___
... [show rest of quote]
upgrade ?
~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test
VALUES(1);"
~$
~$ touch /tmp/test.init
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
~$
~$
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;"
2>/dev/null
~$ ls -l /tmp
total 16
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db
-rw-rw-rw- 1 luuk luuk0 Mar 30 17:18 test.init
~$ sqlite3 -version
3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
~$
Hi Luuk
Thanks for looking into it. There is a typo in your example (-int instead of
-init); the error message ("sqlite3: Error: unknown option: -int") went to
/dev/null. Thus, there is no output to stdout; also the expected query result
'1' is lacking.
Before I manually upgrade (I use currently the latest automatically uogradable
version in Ubuntu xenial): would it be possible that you re-run the example
(with -init) to check whether the leading empty line is indeed no longer
present in version 3.22?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
On 30-3-2019 12:28, softw...@quantentunnel.de wrote: Dear colleagues I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as it seems not documented. When using an init file (even if an empty file), sqlite3 outputs an extra empty line to stdout. This messes up parsing of the sqlite3 output, as this line is not present in the absence of an init file: buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);" buero:~$ touch /tmp/test.init buero:~$ ls -l /tmp/test.* -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 buero:~$ My configuration buero:~$ sqlite3 --version 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f buero:~$ uname -a Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux ___ upgrade ? ~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);" ~$ ~$ touch /tmp/test.init ~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 ~$ ~$ ~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null ~$ ls -l /tmp total 16 -rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db -rw-rw-rw- 1 luuk luuk 0 Mar 30 17:18 test.init ~$ sqlite3 -version 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 ~$ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
Dear colleagues I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as it seems not documented. When using an init file (even if an empty file), sqlite3 outputs an extra empty line to stdout. This messes up parsing of the sqlite3 output, as this line is not present in the absence of an init file: buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);" buero:~$ touch /tmp/test.init buero:~$ ls -l /tmp/test.* -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 buero:~$ My configuration buero:~$ sqlite3 --version 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f buero:~$ uname -a Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
It is using the country table and aliasing it to languages ... > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of John Drescher > Sent: Tuesday, 24 September, 2013 10:50 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Bug in sqlite3.exe? > > On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen > wrote: > > > SQLite version 3.7.16.2 2013-04-12 11:52:43 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> .tables > > CityCountry Languages > > Country Country Official Languages > > Country CapitalsCountryLanguage > > sqlite> select count(*) from country languages; > > count(*) > > -- > > 239 > > > > Although I am a bit surprised that this query succeeded since there is > no > languages table. > > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On 9/24/2013 12:49 PM, John Drescher wrote: On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote: SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables CityCountry Languages Country Country Official Languages Country CapitalsCountryLanguage sqlite> select count(*) from country languages; count(*) -- 239 Although I am a bit surprised that this query succeeded since there is no languages table. "languages" is an alias here. The query is equivalent to select count(*) from country AS languages; AS is optional and can be omitted. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
Many who solved this, thank you all. Staffan On Tue, Sep 24, 2013 at 7:11 PM, Yuriy Kaminskiy wrote: > Staffan Tylen wrote: > > On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin > wrote: > > > >> On 24 Sep 2013, at 5:35pm, Staffan Tylen > wrote: > >> > >>> sqlite> .tables > >>> CityCountry Languages > >>> Country Country Official Languages > >>> Country CapitalsCountryLanguage > >> Either don't use spaces in your token names (table names, column names, > >> index names, etc.) or quote them when you use them. Something like > >> > >> select count(*) from "country official languages"; > >> > >> or > >> > >> select count(*) from [country official languages]; > >> > >> will probably work. I avoid all space in token names because they cause > >> problems with other versions of SQL too, and I don't want to get into > >> dangerous habits. > > > Well, it's not my database I'm looking at. What puzzles me is that > Country > > Languages works but Country Official Languages doesn't, so could there > be a > > parsing problem? > > No. `Languages` is interpreted as *alias* to table `Country`: > > SELECT ... FROM Country Languages > is same as > SELECT ... FROM [Country] AS [Languages] > > And > SELECT ... FROM Country Official > is same as > SELECT ... FROM [Country] AS [Official] > > And that's why `SELECT ... FROM Country Languages` return exactly same > result as > `SELECT FROM Country Official`; if you would've issued just `SELECT ... > FROM > Country`, it would've returned same result as well. > > > I agree, the names should be quoted ... > > > -- > A: Because it messes up the order in which people normally read text. > Q: Why is top-posting such a bad thing? > A: Top-posting. > Q: What is the most annoying thing on usenet and in e-mail? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
Staffan Tylen wrote: > On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin wrote: > >> On 24 Sep 2013, at 5:35pm, Staffan Tylen wrote: >> >>> sqlite> .tables >>> CityCountry Languages >>> Country Country Official Languages >>> Country CapitalsCountryLanguage >> Either don't use spaces in your token names (table names, column names, >> index names, etc.) or quote them when you use them. Something like >> >> select count(*) from "country official languages"; >> >> or >> >> select count(*) from [country official languages]; >> >> will probably work. I avoid all space in token names because they cause >> problems with other versions of SQL too, and I don't want to get into >> dangerous habits. > Well, it's not my database I'm looking at. What puzzles me is that Country > Languages works but Country Official Languages doesn't, so could there be a > parsing problem? No. `Languages` is interpreted as *alias* to table `Country`: SELECT ... FROM Country Languages is same as SELECT ... FROM [Country] AS [Languages] And SELECT ... FROM Country Official is same as SELECT ... FROM [Country] AS [Official] And that's why `SELECT ... FROM Country Languages` return exactly same result as `SELECT FROM Country Official`; if you would've issued just `SELECT ... FROM Country`, it would've returned same result as well. > I agree, the names should be quoted ... > -- A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing on usenet and in e-mail? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On 09/24/2013 11:53 PM, Staffan Tylen wrote: Well, it's not my database I'm looking at. What puzzles me is that Country Languages works but Country Official Languages doesn't, so could there be a parsing problem? It's because the AS keyword is optional. These two are equivalent: SELECT count(*) FROM Country Languages; SELECT count(*) FROM Country AS Languages; Dan. On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin wrote: On 24 Sep 2013, at 5:35pm, Staffan Tylen wrote: sqlite> .tables CityCountry Languages Country Country Official Languages Country CapitalsCountryLanguage Either don't use spaces in your token names (table names, column names, index names, etc.) or quote them when you use them. Something like select count(*) from "country official languages"; or select count(*) from [country official languages]; will probably work. I avoid all space in token names because they cause problems with other versions of SQL too, and I don't want to get into dangerous habits. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On Tue, Sep 24, 2013 at 12:53 PM, Staffan Tylen wrote: > Well, it's not my database I'm looking at. What puzzles me is that Country > Languages works but Country Official Languages doesn't, so could there be a > parsing problem? > > I think I see what is happening.. when you have a space in the from the second name is being taken as an AS (used for an alias) So in your You are counting the number of countries in your Country table. The following should work in that example: select count(languages.*) from country languages; John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote: > SQLite version 3.7.16.2 2013-04-12 11:52:43 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .tables > CityCountry Languages > Country Country Official Languages > Country CapitalsCountryLanguage > sqlite> select count(*) from country languages; > The previous is parsed the same as: SELECT count(*) FROM country AS languages; Your are querying the table named "country" and giving it an alias of "languages". > count(*) > -- > 239 > sqlite> select count(*) from country official languages; > Error: near "languages": syntax error > sqlite> select count(*) from country official; > count(*) > -- > 239 > sqlite> select count(*) from countrylanguage; > count(*) > -- > 988 > sqlite> select count(*) from languages; > Error: no such table: languages > sqlite> > > I'm aware the version of SQLite is not current so it might already have > been fixed. > > Staffan > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
Well, it's not my database I'm looking at. What puzzles me is that Country Languages works but Country Official Languages doesn't, so could there be a parsing problem? I agree, the names should be quoted ... On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin wrote: > > On 24 Sep 2013, at 5:35pm, Staffan Tylen wrote: > > > sqlite> .tables > > CityCountry Languages > > Country Country Official Languages > > Country CapitalsCountryLanguage > > Either don't use spaces in your token names (table names, column names, > index names, etc.) or quote them when you use them. Something like > > select count(*) from "country official languages"; > > or > > select count(*) from [country official languages]; > > will probably work. I avoid all space in token names because they cause > problems with other versions of SQL too, and I don't want to get into > dangerous habits. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote: > SQLite version 3.7.16.2 2013-04-12 11:52:43 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .tables > CityCountry Languages > Country Country Official Languages > Country CapitalsCountryLanguage > sqlite> select count(*) from country languages; > count(*) > -- 239 > Although I am a bit surprised that this query succeeded since there is no languages table. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On 24 Sep 2013, at 5:35pm, Staffan Tylen wrote: > sqlite> .tables > CityCountry Languages > Country Country Official Languages > Country CapitalsCountryLanguage Either don't use spaces in your token names (table names, column names, index names, etc.) or quote them when you use them. Something like select count(*) from "country official languages"; or select count(*) from [country official languages]; will probably work. I avoid all space in token names because they cause problems with other versions of SQL too, and I don't want to get into dangerous habits. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3.exe?
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote: > SQLite version 3.7.16.2 2013-04-12 11:52:43 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .tables > CityCountry Languages > Country Country Official Languages > Country CapitalsCountryLanguage > sqlite> select count(*) from country languages; > count(*) > -- > 239 > sqlite> select count(*) from country official languages; > Error: near "languages": syntax error > sqlite> select count(*) from country official; > count(*) > -- > 239 > sqlite> select count(*) from countrylanguage; > count(*) > -- > 988 > sqlite> select count(*) from languages; > Error: no such table: languages > sqlite> > > I'm aware the version of SQLite is not current so it might already have > been fixed. > > This does not look like a bug to me. You need to escape spaces. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite3.exe?
SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables CityCountry Languages Country Country Official Languages Country CapitalsCountryLanguage sqlite> select count(*) from country languages; count(*) -- 239 sqlite> select count(*) from country official languages; Error: near "languages": syntax error sqlite> select count(*) from country official; count(*) -- 239 sqlite> select count(*) from countrylanguage; count(*) -- 988 sqlite> select count(*) from languages; Error: no such table: languages sqlite> I'm aware the version of SQLite is not current so it might already have been fixed. Staffan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in Sqlite3 and parameters
I am using a parametric sql statement, with parameters of the form :Name The following routines give an access violation, when called via the windows dll: sqlite3_bind_parameter_name sqlite3_bind_parameter_index The access violation indicates that a NULL pointer is being accessed. A brief look at the code suggests that some problem with the azVar initialization must be the reason, but I do not know enough C to fix it. environment: Windows 2000, Delphi 7 calling sqlite3.dll. can you pls do something about it, quickly, as I cannot continue my project. thanks.
