Re: [sqlite] An issue with System.Data.SQLite
Aleksey Lulchenko wrote: > > Please, give me the clues what is going wrong, if you can. I think it > is a typical bug in many situations. > Could you please show us the schema of the table (or tables) involved? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suspected bug: parse error depending on platform
Ok, I see. So finally using `sqlite3.sqlite_version`, it says version 3.8.11 for the Windows version and 3.11.0 for the Ubuntu version. May be I could solve it changing the sqlite3.dll which is in the isolated Python environment. I will try to build it in the virtual machine. Thanks for having pointed out my error … -- 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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store
On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote: > Nevertheless, we will investigate from the SQLite side, > just in case. If you need any information from the core dump, please let me know which gdb commands to run. I still have one available from the latest crash and have marked it immutable to prevent it from being removed until your investigation is complete. -- bye, pabs http://bonedaddy.net/pabs3/ signature.asc Description: This is a digitally signed message part ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store
On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote: > There are no known issues like this with any recent version of SQLite. > Usually these kinds of things end up being heap corruption in the > application. Nevertheless, we will investigate from the SQLite side, > just in case. I see, thanks for the information and investigation. > "SQLite 3.21.0-1" is not an official SQLite product. It must be a > version of SQLite that has been customized by Debian. Where can we > get a copy of Debian's customized code? You can browse the Debian source code here: https://sources.debian.org/src/sqlite3/3.21.0-1/ You can browse the Debian patches here: https://sources.debian.org/patches/sqlite3/3.21.0-1/ You can download the Debian source code here (debian.tar has patches): http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.dsc http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.debian.tar.xz http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig.tar.xz http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig-www.tar.xz You can view the build logs for the package here, in the Status column: https://buildd.debian.org/status/package.php?p=sqlite3 Note that the crash was on amd64 and logs are not available for that. -- bye, pabs http://bonedaddy.net/pabs3/ signature.asc Description: This is a digitally signed message part ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store
On 12/22/17, Paul Wise wrote: > Hi all, > > I got a couple of random crashes GNOME's tracker-store daemon that > appear to be related to sqlite3. I use 3.21.0-1 from Debian buster. > > Is this a bug in sqlite3 or is it caused by data corruption? > There are no known issues like this with any recent version of SQLite. Usually these kinds of things end up being heap corruption in the application. Nevertheless, we will investigate from the SQLite side, just in case. "SQLite 3.21.0-1" is not an official SQLite product. It must be a version of SQLite that has been customized by Debian. Where can we get a copy of Debian's customized code? -- 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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store
Hi all, I got a couple of random crashes GNOME's tracker-store daemon that appear to be related to sqlite3. I use 3.21.0-1 from Debian buster. https://bugzilla.gnome.org/show_bug.cgi?id=791243 The short backtraces are available here: https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html&trace_id=238220 https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html&trace_id=238281 The full backtraces are available here: https://bugzilla.gnome.org/attachment.cgi?id=365000 https://bugzilla.gnome.org/attachment.cgi?id=365891 Is this a bug in sqlite3 or is it caused by data corruption? PS: please either CC me in response or post on the GNOME bug. -- bye, pabs http://bonedaddy.net/pabs3/ signature.asc Description: This is a digitally signed message part ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] An issue with System.Data.SQLite
Do you allow the entity framework to create your database, or do you do it yourself? You need text affinity on the columns in question. Then any numbers will be stored as text, not numbers. See: https://sqlite.org/datatype3.html Particularly: 3.1. Determination Of Column Affinity The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown: 1) If the declared type contains the string "INT" then it is assigned INTEGER affinity. 2) If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. On 21 December 2017 at 23:06, Алексей Люльченко wrote: > Dear Sir! > > I would like to ask you to help me with an issue which appears while I use > System.Data.SQLite database engine. > > I use .net framework 4.0.0, EntityFramework 6.2.0 and SQLite 1.0.106. I > have an entity with properties of string type. This entity is mapped > to my database table which has the same structure (columns names and data > types). When I write and read back data to and from the database, all is OK > while these columns have strings like "abcdef" or "123abc". But when I try > to write a string like "1234" it is written to the database table like an > int data type value. I know that it is a normal behavior and it is not an > issue (https://sqlite.org/faq.html#q3). But I get the > "System.InvalidCastException" when I try to read back the row from the > database to my entity because the datatypes are different (the entity has > the property of string type but the database table now has the cell of int > datatype). > > Please, give me the clues what is going wrong, if you can. I think it is a > typical bug in many situations. > > Thank you in advance! > > Yours Faithfully, > > Aleksey Lulchenko, .Net developer at "TDabbat". > Saratov, Russia > ___ > 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] Suspected bug: parse error depending on platform
>The simple example below works as expected on Ubuntu, but fails on >Windows 7. Both using Python3.5 and Sqlite 2.6.0, so that’s not a >version issue. 2.6.0 is not the version of SQLite3, it is the version of the pysqlite2 wrapper module. When that third party package was incorporated into the Python distribution its name was changed to the package sqlite3. The "package (wrapper)" version is sqlite3.version or sqlite3.version_info The "SQLite3 (database engine)" versions is sqlite3.sqlite_version or sqlite3.sqlite_version_info So probably you have an "old as the hills" version of SQLite3 (the database engine -- the thing that actually does anything) on Windows 7. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Thank you Peter! I like sqlite so much and I think we all benefit if errors are fixed. I see I was not very clear with my first post. Will do better next time. Yes would be nice if people would try to understand first not just think on title text. Talking about sqlite I use it for web development, desktop apps. I also created tool or gui sqlite manager and will try to sell it in near future. I needed very good tool because I work with sqlite every day. It is just for windows platform. I support different datetime formats, blobs, compress etc. See this page: http://www.arsistemi.si/izdelki/sqlite-4-all.html Every report is created with sqlite tables. I read data from different RDBMS to sqlite and then work with data as needed. petern je 22.12.2017 ob 23:19 napisal: Radovan. Thank you for sticking to your guns. Your appeal to expected behavior under other DB engines was also a very good post. I see the usual suspects of the echo chamber uselessly piled on against you to clog the forum nevertheless. That happens too frequently. BTW, here is a simpler test suite which pinpoints the problem: SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); d 1 CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; c 1 CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c); SELECT * FROM test; d 1 And still one more that illustrates the contradictory and unstable behavior. Use '*' instead of 'd' and it works as expected. CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; d 1 Obviously the most immediate well formed alias 'd' should be carried through irrespective of nesting. FYI, there was a checkin just now with some changes to track intermediate aliases: http://www.sqlite.org/src/info/5efd854fe2147033 Peter ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Radovan. Thank you for sticking to your guns. Your appeal to expected behavior under other DB engines was also a very good post. I see the usual suspects of the echo chamber uselessly piled on against you to clog the forum nevertheless. That happens too frequently. BTW, here is a simpler test suite which pinpoints the problem: SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); d 1 CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; c 1 CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c); SELECT * FROM test; d 1 And still one more that illustrates the contradictory and unstable behavior. Use '*' instead of 'd' and it works as expected. CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; d 1 Obviously the most immediate well formed alias 'd' should be carried through irrespective of nesting. FYI, there was a checkin just now with some changes to track intermediate aliases: http://www.sqlite.org/src/info/5efd854fe2147033 Peter On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga wrote: > Thank you Klaus! > > Klaus Maas je 22.12.2017 ob 20:30 napisal: > >> Radovan is correct. >> Executing the same command sequence in version 3.11.0 and 3.21.0 results >> in different column names for table test2. >> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) >> I marked the results with '<=' >> Klaus >> >> >> SQLite version 3.11.0 2016-02-15 17:29:24 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> .header on >> sqlite> >> sqlite> create table test(a int, b int); >> sqlite> insert into test values (1,1); >> sqlite> >> sqlite> select d from (select c as d from (select a as c from test)); >> d >> 1 >> sqlite> >> sqlite> create table test2 as >>...> select d from (select c as d from (select a as c from test)); >> sqlite> SELECT * FROM test2; >> d <= >> 1 >> sqlite> >> >> >> >> SQLite version 3.21.0 2017-10-24 18:55:49 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> .header on >> sqlite> create table test(a int, b int); >> sqlite> insert into test values (1,1); >> sqlite> >> sqlite> select d from (select c as d from (select a as c from test)); >> d >> 1 >> sqlite> create table test2 as >>...> select d from (select c as d from (select a as c from test)); >> sqlite> SELECT * FROM test2; >> a <= >> 1 >> sqlite> >> >> email signature Klaus Maas >> >> On 2017-12-22 20:11, Radovan Antloga wrote: >> >>> Just try this sql-s: >>> >>> create table test(a int, b int); >>> insert into test values (1,1); >>> >>> select d from (select c as d from (select a as c from test)); >>> >>> will return name d. >>> >>> create table test2 as >>> select d from (select c as d from (select a as c from test)); >>> >>> will create table test2 with column name a. >>> >>> with PostgreSQL I get table test2 with name d. >>> >>> Thank you very much for your time! >>> >>> >>> Richard Hipp je 22.12.2017 ob 20:06 napisal: >>> On 12/22/17, Radovan Antloga wrote: > My point is you do not have to change anything regards > to how select works. SELECT statement is working just > great or OK or fine. > > CREATE TABLE AS > SELECT > > does not give same name as SELECT does. > > SELECT give name d > > CREATE TABLE AS SELECT > create table with name a instead of d. > Can you provide a simple test case for this behavior? >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suspected bug: parse error depending on platform
Hello,
I’em facing an issue developing a Python+SQlite3 application on Ubuntu, to
be shipped on Windows 7.
The simple example below works as expected on Ubuntu, but fails on Windows
7. Both using Python3.5 and Sqlite 2.6.0, so that’s not a version issue.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("create view v(c) as values(1);")
No error on Ubuntu but fails on Windows 7 with this message:
sqlite3.OperationalError: near "(": syntax error
If that matters for anything, I’m testing the application on Windows 7 32
bits running in Virtualbox, using the image provided by Microsoft. The
Python3.5 I use on Ubuntu is in a vitualenv and the one for Windows is from
a bundle archive provided by the Python community website. This archive
contains an isolated Python environment.
Someone else encountered a similar issue one year ago, in the context of a
NodeJS application, here: https://github.com/brianc/node-sql/issues/314
For the personal story, the real (above is a sample for testing the issue)
view table SQLite don’t want to create is to be used by a trigger running
some checks. So I have to drop it. It does not prevent the application from
running, but it will be less reliably without this trigger.
--
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] Changed behaviour or bug using field alias in 3.21.0
Thank you Klaus! Klaus Maas je 22.12.2017 ob 20:30 napisal: Radovan is correct. Executing the same command sequence in version 3.11.0 and 3.21.0 results in different column names for table test2. (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) I marked the results with '<=' Klaus SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .header on sqlite> sqlite> create table test(a int, b int); sqlite> insert into test values (1,1); sqlite> sqlite> select d from (select c as d from (select a as c from test)); d 1 sqlite> sqlite> create table test2 as ...> select d from (select c as d from (select a as c from test)); sqlite> SELECT * FROM test2; d <= 1 sqlite> SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .header on sqlite> create table test(a int, b int); sqlite> insert into test values (1,1); sqlite> sqlite> select d from (select c as d from (select a as c from test)); d 1 sqlite> create table test2 as ...> select d from (select c as d from (select a as c from test)); sqlite> SELECT * FROM test2; a <= 1 sqlite> email signature Klaus Maas On 2017-12-22 20:11, Radovan Antloga wrote: Just try this sql-s: create table test(a int, b int); insert into test values (1,1); select d from (select c as d from (select a as c from test)); will return name d. create table test2 as select d from (select c as d from (select a as c from test)); will create table test2 with column name a. with PostgreSQL I get table test2 with name d. Thank you very much for your time! Richard Hipp je 22.12.2017 ob 20:06 napisal: On 12/22/17, Radovan Antloga wrote: My point is you do not have to change anything regards to how select works. SELECT statement is working just great or OK or fine. CREATE TABLE AS SELECT does not give same name as SELECT does. SELECT give name d CREATE TABLE AS SELECT create table with name a instead of d. Can you provide a simple test case for this behavior? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
THANK YOU! Richard Hipp je 22.12.2017 ob 20:29 napisal: Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Radovan is correct. Executing the same command sequence in version 3.11.0 and 3.21.0 results in different column names for table test2. (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) I marked the results with '<=' Klaus SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .header on sqlite> sqlite> create table test(a int, b int); sqlite> insert into test values (1,1); sqlite> sqlite> select d from (select c as d from (select a as c from test)); d 1 sqlite> sqlite> create table test2 as ...> select d from (select c as d from (select a as c from test)); sqlite> SELECT * FROM test2; d <= 1 sqlite> SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .header on sqlite> create table test(a int, b int); sqlite> insert into test values (1,1); sqlite> sqlite> select d from (select c as d from (select a as c from test)); d 1 sqlite> create table test2 as ...> select d from (select c as d from (select a as c from test)); sqlite> SELECT * FROM test2; a <= 1 sqlite> email signature Klaus Maas On 2017-12-22 20:11, Radovan Antloga wrote: Just try this sql-s: create table test(a int, b int); insert into test values (1,1); select d from (select c as d from (select a as c from test)); will return name d. create table test2 as select d from (select c as d from (select a as c from test)); will create table test2 with column name a. with PostgreSQL I get table test2 with name d. Thank you very much for your time! Richard Hipp je 22.12.2017 ob 20:06 napisal: On 12/22/17, Radovan Antloga wrote: My point is you do not have to change anything regards to how select works. SELECT statement is working just great or OK or fine. CREATE TABLE AS SELECT does not give same name as SELECT does. SELECT give name d CREATE TABLE AS SELECT create table with name a instead of d. Can you provide a simple test case for this behavior? ___ 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] Changed behaviour or bug using field alias in 3.21.0
Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Just try this sql-s: create table test(a int, b int); insert into test values (1,1); select d from (select c as d from (select a as c from test)); will return name d. create table test2 as select d from (select c as d from (select a as c from test)); will create table test2 with column name a. with PostgreSQL I get table test2 with name d. Thank you very much for your time! Richard Hipp je 22.12.2017 ob 20:06 napisal: On 12/22/17, Radovan Antloga wrote: My point is you do not have to change anything regards to how select works. SELECT statement is working just great or OK or fine. CREATE TABLE AS SELECT does not give same name as SELECT does. SELECT give name d CREATE TABLE AS SELECT create table with name a instead of d. Can you provide a simple test case for this behavior? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
What is correct? Do you understand what I write? What is my point? Tell me please. David Raymond je 22.12.2017 ob 20:04 napisal: Correct. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, December 22, 2017 1:45 PM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 12/22/17, Radovan Antloga wrote: In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
On 12/22/17, Radovan Antloga wrote: > My point is you do not have to change anything regards > to how select works. SELECT statement is working just > great or OK or fine. > > CREATE TABLE AS > SELECT > > does not give same name as SELECT does. > > SELECT give name d > > CREATE TABLE AS SELECT > create table with name a instead of d. Can you provide a simple test case for this behavior? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Correct. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, December 22, 2017 1:45 PM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 12/22/17, Radovan Antloga wrote: > In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] An issue with System.Data.SQLite
Dear Sir! I would like to ask you to help me with an issue which appears while I use System.Data.SQLite database engine. I use .net framework 4.0.0, EntityFramework 6.2.0 and SQLite 1.0.106. I have an entity with properties of string type. This entity is mapped to my database table which has the same structure (columns names and data types). When I write and read back data to and from the database, all is OK while these columns have strings like "abcdef" or "123abc". But when I try to write a string like "1234" it is written to the database table like an int data type value. I know that it is a normal behavior and it is not an issue (https://sqlite.org/faq.html#q3). But I get the "System.InvalidCastException" when I try to read back the row from the database to my entity because the datatypes are different (the entity has the property of string type but the database table now has the cell of int datatype). Please, give me the clues what is going wrong, if you can. I think it is a typical bug in many situations. Thank you in advance! Yours Faithfully, Aleksey Lulchenko, .Net developer at "TDabbat". Saratov, Russia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
My point is you do not have to change anything regards to how select works. SELECT statement is working just great or OK or fine. CREATE TABLE AS SELECT does not give same name as SELECT does. SELECT give name d CREATE TABLE AS SELECT create table with name a instead of d. Richard Hipp je 22.12.2017 ob 19:45 napisal: On 12/22/17, Radovan Antloga wrote: In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
On 12/22/17, Radovan Antloga wrote: > In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
In my example I have AS clause so rule 1. sqlite select statement is correct or name is correct. sqlite create table as statement create table with different name that select statement return Problem is different result or name. Richard Hipp je 22.12.2017 ob 19:27 napisal: These are the rules that SQLite uses to name a column in the result set: (1) If there is an AS clause, use it. (2) If the result-set value comes from a table column (even indirectly, such as through a subquery, but not if the value is altered by an expression) then use the name of the column as it appears in the original CREATE TABLE statement for the table. (3) Otherwise, name the result-set column using the SQL text in the SELECT statement. What set of rules are you proposing that we should use instead? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
These are the rules that SQLite uses to name a column in the result set: (1) If there is an AS clause, use it. (2) If the result-set value comes from a table column (even indirectly, such as through a subquery, but not if the value is altered by an expression) then use the name of the column as it appears in the original CREATE TABLE statement for the table. (3) Otherwise, name the result-set column using the SQL text in the SELECT statement. What set of rules are you proposing that we should use instead? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Just tried my example with PostgreSQL that have create table as statement. It work as expected. It creates table test2 with column name d. sqlite3 creates table with column name a. sqlite3 try to mimic postgresql but in this example is not. Simon Slavin je 22.12.2017 ob 17:58 napisal: On 22 Dec 2017, at 4:50pm, Radovan Antloga wrote: select d from (select c AS d from (select a AS c from test)); I get d as column name. If I create table with create table as I get a as column name. As I wrote, you did not specify AS for d, so you cannot depend on a column name. If you want to know what the column will be called, specify AS: select d AS myColumnNameHere from (select c AS d from (select a AS c from test)); Now you know what the column will be called. Simon. ___ 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] Changed behaviour or bug using field alias in 3.21.0
What? I have to write select d as d. You are not serious. You dont understand what I wrote. I get select correct but create table as does not have same name as select has. Why different result? If select gives some name I don't care what I expect create table as give me same name. Is this so hard to understand. Simon Slavin je 22.12.2017 ob 17:58 napisal: On 22 Dec 2017, at 4:50pm, Radovan Antloga wrote: select d from (select c AS d from (select a AS c from test)); I get d as column name. If I create table with create table as I get a as column name. As I wrote, you did not specify AS for d, so you cannot depend on a column name. If you want to know what the column will be called, specify AS: select d AS myColumnNameHere from (select c AS d from (select a AS c from test)); Now you know what the column will be called. Simon. ___ 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] Changed behaviour or bug using field alias in 3.21.0
Thank you very much to understand my point! This is exactly what I think. I have name and select is working ok. It gives me my name but create table as not. David Raymond je 22.12.2017 ob 17:59 napisal: I think the underlying feeling here is that if you're not doing anything tricky, and just straight up referencing a column name, that it should be fair to assume that the result will have that column name. I.e. with create table foo (bar); if I run select bar from foo; I should be able to assume the result column is named "bar". So... select a as c from test is certain to have a field named "c", select c as d from (something with a field named "c") is certain to have a field named "d", but select d from (something with a field named "d") doesn't give a field named "d"? I'd say it's fair to be confused at that. I definitely don't want to have to do queries in the form of "select foo as foo, bar as bar, baz as baz..." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, December 22, 2017 11:34 AM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 22 Dec 2017, at 3:04pm, Tim Streater wrote: 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it? If you want to depend on the name of a column, use AS. If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning. But there’s no standard for column names without AS in the SQL standard so no warning is needed. If you think that column names are simple, please consider this: CREATE TABLE ta (ca INTEGER); CREATE TABLE tb (cb INTEGER); SELECT ca FROM ta UNION SELECT cb FROM tb; What should the name of the resulting column be ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
I think the underlying feeling here is that if you're not doing anything tricky, and just straight up referencing a column name, that it should be fair to assume that the result will have that column name. I.e. with create table foo (bar); if I run select bar from foo; I should be able to assume the result column is named "bar". So... select a as c from test is certain to have a field named "c", select c as d from (something with a field named "c") is certain to have a field named "d", but select d from (something with a field named "d") doesn't give a field named "d"? I'd say it's fair to be confused at that. I definitely don't want to have to do queries in the form of "select foo as foo, bar as bar, baz as baz..." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, December 22, 2017 11:34 AM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 22 Dec 2017, at 3:04pm, Tim Streater wrote: > 2) I don't see this issue mentioned when I read the PHP documentation about > their SQLite interface, nor do I see it in the Xojo docs about *their* > interface either. I assume their interfaces are not rewriting SELECT > statements to include AS for every column selected, so should they be warning > their users about it? If you want to depend on the name of a column, use AS. If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning. But there’s no standard for column names without AS in the SQL standard so no warning is needed. If you think that column names are simple, please consider this: CREATE TABLE ta (ca INTEGER); CREATE TABLE tb (cb INTEGER); SELECT ca FROM ta UNION SELECT cb FROM tb; What should the name of the resulting column be ? Simon. ___ 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] Changed behaviour or bug using field alias in 3.21.0
On 22 Dec 2017, at 4:50pm, Radovan Antloga wrote: > select d from (select c AS d from (select a AS c from test)); > > I get d as column name. If I create table with > create table as I get a as column name. As I wrote, you did not specify AS for d, so you cannot depend on a column name. If you want to know what the column will be called, specify AS: select d AS myColumnNameHere from (select c AS d from (select a AS c from test)); Now you know what the column will be called. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Sorry but this is not related to my example. I have AS in inner select. My select return name as specified. Problem I have is with create table as where name is changed. I give example like this: select d from (select c AS d from (select a AS c from test)); I get d as column name. If I create table with create table as I get a as column name. Simon Slavin je 22.12.2017 ob 17:33 napisal: On 22 Dec 2017, at 3:04pm, Tim Streater wrote: 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it? If you want to depend on the name of a column, use AS. If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning. But there’s no standard for column names without AS in the SQL standard so no warning is needed. If you think that column names are simple, please consider this: CREATE TABLE ta (ca INTEGER); CREATE TABLE tb (cb INTEGER); SELECT ca FROM ta UNION SELECT cb FROM tb; What should the name of the resulting column be ? Simon. ___ 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] journal mode and transactions
In memory databases can have rollbacks if they're using journal mode memory. The last sentence there is a little confusing... "Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. Note also that the journal_mode cannot be changed while a transaction is active" ...it seems to say you can't change it, but then says if you do change it... For the transaction benefits you might be right, or it might be the case where having them in a transaction saves some disk flushes while it's going on. I.e. in both autocommit and journal_mode off you'll be making changes directly to the main file with no rollback journals. But with autocommit it'll be syncing the file after each statement whereas with the transaction it might wait until the commit to sync. Not sure on that though. An in memory database with journal_mode off I'm pretty sure will see no benefits from transactions. These are my edumacated guesses anyway. Please trust the actual experts more than me. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 Sent: Friday, December 22, 2017 11:10 AM To: SQLite mailing list Subject: [sqlite] journal mode and transactions The transactions documentation (https://sqlite.org/lang_transaction.html) states >If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined. Does that correspondingly mean there's no performance benefit to wrapping operations in a transaction? Is that true for in-memory databases as well? -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ 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] Changed behaviour or bug using field alias in 3.21.0
On 22 Dec 2017, at 3:04pm, Tim Streater wrote: > 2) I don't see this issue mentioned when I read the PHP documentation about > their SQLite interface, nor do I see it in the Xojo docs about *their* > interface either. I assume their interfaces are not rewriting SELECT > statements to include AS for every column selected, so should they be warning > their users about it? If you want to depend on the name of a column, use AS. If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning. But there’s no standard for column names without AS in the SQL standard so no warning is needed. If you think that column names are simple, please consider this: CREATE TABLE ta (ca INTEGER); CREATE TABLE tb (cb INTEGER); SELECT ca FROM ta UNION SELECT cb FROM tb; What should the name of the resulting column be ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if an in memory database runs out of memory
>You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 and then starting the "sqlite3.exe" command-line shell with the "--heap" argument to tell it how much memory to use. Give it a few megabytes. Then start up your in-memory database and fill it up to see what happens. Richard suggested this earlier. Can this be done in C rather than the shell? Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling with a minus sign before that directive I get a compile error "macro names must be identifiers ". Compiles OK if I leave out the minus sign. -- 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] journal mode and transactions
The transactions documentation (https://sqlite.org/lang_transaction.html) states >If PRAGMA journal_mode is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined. Does that correspondingly mean there's no performance benefit to wrapping operations in a transaction? Is that true for in-memory databases as well? -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor bug reports during build.
On Fri, Dec 22, 2017 at 10:26 AM, Warren Young wrote: > On Dec 22, 2017, at 7:07 AM, Michael Tiernan > wrote: > > > > "Working as advertised" Okay, that's just funny. That it doesn't build is > > correct? > > It does build. Okay, it does build the binaries. Valid point. It just doesn’t install to a directory it can’t write to, because you > told it to install system-level things. Not going to hash it out here but I didn't tell it to install system-level things, I told it to compile and install everything locally. Just like I do with lots of other source packages especially when I'm not very familiar with the software and wish to make sure of what I'm doing before committing it to the system. > > Just as an FYI, it builds correctly on MacOSX and doesn't complain about > > things it can't control. > I’m guessing you’re using Homebrew, Nope, not at all. It doesn't seem to try and touch the things it has no right to touch. -- << MCT >> Michael C Tiernan.http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRIM Function in Python3's sqlite3 import
http://www.sqlite.org/changes.html Looks like trim was added in 10 years ago in 3.4.0 (2007-06-18) Can't help you with the Linux side of things, sorry. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ed Lipson Sent: Friday, December 22, 2017 10:07 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] TRIM Function in Python3's sqlite3 import I shifting some Python code to Linux from Windows. I find that TRIN i smissing from the verison on Linux. Python reports this version information sqlite.version 2.6.0 for both Windows and Linux sqlite.sqlite_version 3.3.6 for Linux, 3.15.2 for Windows. Two questions: 1. When was TRIM added as a function? 2. How do I update the RHEL Python to a version which has the TRIM function, or is there a replacement function available in 3.3.6 to which I can change the code? Thanks, Ed ___ 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] Minor bug reports during build.
On Dec 22, 2017, at 7:07 AM, Michael Tiernan wrote: > > "Working as advertised" Okay, that's just funny. That it doesn't build is > correct? It does build. It just doesn’t install to a directory it can’t write to, because you told it to install system-level things. Rowan also gave you the solution, forcing installation of Tcl extension to other than the default you implicitly selected by not overriding the default. > Just as an FYI, it builds correctly on MacOSX and doesn't complain about > things it can't control. I’m guessing you’re using Homebrew, which gives your normal user all rights to /usr/local, which is atypical for POSIX machines. In fact on macOS’s closest cousin, FreeBSD, giving that level of access to /usr/local would be a major security problem. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing Text to Create a Table
I suspect you would be best advised to do more processing of the data to extract just the email addresses rather than treating it like an unstructured text blob. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRIM Function in Python3's sqlite3 import
I shifting some Python code to Linux from Windows. I find that TRIN i smissing from the verison on Linux. Python reports this version information sqlite.version 2.6.0 for both Windows and Linux sqlite.sqlite_version 3.3.6 for Linux, 3.15.2 for Windows. Two questions: 1. When was TRIM added as a function? 2. How do I update the RHEL Python to a version which has the TRIM function, or is there a replacement function available in 3.3.6 to which I can change the code? Thanks, Ed ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
On 22 Dec 2017, at 09:57, Hick Gunter wrote: > The problem lies in your reliance on unspecified behaviour. Unspecified > behaviour is allowed to change. > > I am sure you have read (and ignored) the following guarantee taken from > http://sqlite.org/c3ref/column_name.html : My questions are these: 1) That the name without AS is documented as being unspecified, is that the case with all/most SQL systems or is it SQLite specific? 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it? -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor bug reports during build.
"Working as advertised" Okay, that's just funny. That it doesn't build is correct? Also, despite the "disable-tcl" flag, the tests all fail because of something involving tcl. Don't think I read that in the advertisement. Just as an FYI, it builds correctly on MacOSX and doesn't complain about things it can't control. -- << MCT >> Michael C Tiernan.http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein On Dec 21, 2017 10:36 PM, "Rowan Worth" wrote: Seems to be working as advertised. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if an in memory database runs out of memory
>I would think that a temp file database (created with an empty string) is no different from a regular disk file resident database EXCEPT that the file is generated with an random tmpfile name and automatically unlinked when closed, and that "memory pressure" equates to "page cache is full". I don't know if it would use the temp page cache size or the database page cache size. I think it has something to do with persistent versus temp tables. You can't qualify a temp table e.g. "create temp table mem.Tbl" isn't allowed so it's either "create temp table Tbl" (standard temp table) or "create table mem.Tbl" (persistent table within a temp db). -- 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] What happens if an in memory database runs out of memory
>I thought I had posted this earlier but I don't see it. >Earlier I said the ideal solution would be something that uses memory >and defaults to disc if it runs out of memory. In response Richard's >suggested using a temp database with a blank name as that would use >memory but parts of it would be flushed to disc if sqlite came under >memory pressure (https://sqlite.org/inmemorydb.html). I tried the >large insert in such a db but the performance was only on a par >with a temp table with temp_store set as FILE. I would think that a temp file database (created with an empty string) is no different from a regular disk file resident database EXCEPT that the file is generated with an random tmpfile name and automatically unlinked when closed, and that "memory pressure" equates to "page cache is full". I don't know if it would use the temp page cache size or the database page cache size. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atomic DELETE index optimisation?
Thank you all for the replies, I will hack this problem one way or another after the hoildays and let you know how it went. In the mean time, I wish you all happy peaceful holidays, and a great New Year! Dinu -- 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] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
How do you test your sql-s? I write sql from step to step from very simple to complex. So I have select and finally I need table for that. It was simple until now. Just put create table as before select and you get same result (same names) as in select. Your example is not the same. If using expression name can be different and can change. This is true for every datatase I know. In Firebird If I write select 1+2 from rdb$database I get column name ADD. If I specify with AS clause I get name I want. This works in sqlite and is not a problem. If I do not specify name it can change from version to version and everbody knows it must be specified. But in my example name is explicitly defined in inner select and for select I get correct name but for create table as I do not. Please dont tell me again what is documented because at first very little was and not as one would like but I understand that. It is not the point in this example. The point is different behavior or result or names when you have select or create table as from that select. It should give same result or is better just not using create table as at all because you will never know what you can expect. Maybe as you eplained some day create table as will create name like 1, 2, 3, 4 Long names sometimes are used for excel export or some simple reports with just sql behind. R Smith je 22.12.2017 ob 12:34 napisal: On 2017/12/22 11:06 AM, Radovan Antloga wrote: I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...). Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does. It didn't break for millions of users, it only broke for the few who relied on behaviour that is documented to not be reliable and that might change in future. Usually the Dev team is not in the habit of going around messing with features just because it is documented as unspecified, but in this case, it had to change to fix another real bug that had real-world problematic implications. Sorry but I did not ask how can I fix my sql-s. I know I can create table and insert into. Change select to select d as d is just ridiculous. Rename column name into same name. What is that? The first "d" tells SQLite where to find or how to evaluate the value, the second "d" tells SQLite how to name the output of that SELECT. It isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous. This is not English101, this is Programming. Rules are rules, even silly ones. .../and later/... >If I have long name then I would have >select some_long_name_to_understand_column as some_long_name_to_understand_column >and if I have 20 columns like that. Just ridiculous or what. >So once again I use AS clause but why I must use AS clause in >outer statement if name is already defined in inner select. Why would you use such a long silly name in the inner query KNOWING that it is ignored (wrt. to output naming anyway)? That doesn't make a sound argument. The CREATE TABLE Evaluates the select with some special considerations (as documented), nothing prescribes the resulting column name except the final outer statement's AS clause (as documented). Internally sqlite doesn't even see Aliases, it has numbers for columns. The outer select is however the boss, when it says "this-column" AS 'this_name' then sqlite (and any other DB engine) /has/ to abide by that (as documented). The aliasing in the inner queries matter none other than to allow reliable reference pointing between query levels (as documented). If it were undocumented behaviour you'd still not have a case but some anger would be understandable, however, you've explicitly relied on /documented-to-be-non-reliable/ behaviour, I still feel your pain, but it still isn't a bug and calling it "ridiculous" still won't change that. (Btw. - the devs might change the behaviour again, maybe even to your liking if they feel merit and it doesn't break another behaviour, the point remains that it must not be trusted until it becomes the documented behaviour.) I sincerely hope not too many of those 1000+ sql-s need fixing... Good luck! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
On 2017/12/22 11:06 AM, Radovan Antloga wrote: I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...). Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does. It didn't break for millions of users, it only broke for the few who relied on behaviour that is documented to not be reliable and that might change in future. Usually the Dev team is not in the habit of going around messing with features just because it is documented as unspecified, but in this case, it had to change to fix another real bug that had real-world problematic implications. Sorry but I did not ask how can I fix my sql-s. I know I can create table and insert into. Change select to select d as d is just ridiculous. Rename column name into same name. What is that? The first "d" tells SQLite where to find or how to evaluate the value, the second "d" tells SQLite how to name the output of that SELECT. It isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous. This is not English101, this is Programming. Rules are rules, even silly ones. .../and later/... >If I have long name then I would have >select some_long_name_to_understand_column as some_long_name_to_understand_column >and if I have 20 columns like that. Just ridiculous or what. >So once again I use AS clause but why I must use AS clause in >outer statement if name is already defined in inner select. Why would you use such a long silly name in the inner query KNOWING that it is ignored (wrt. to output naming anyway)? That doesn't make a sound argument. The CREATE TABLE Evaluates the select with some special considerations (as documented), nothing prescribes the resulting column name except the final outer statement's AS clause (as documented). Internally sqlite doesn't even see Aliases, it has numbers for columns. The outer select is however the boss, when it says "this-column" AS 'this_name' then sqlite (and any other DB engine) /has/ to abide by that (as documented). The aliasing in the inner queries matter none other than to allow reliable reference pointing between query levels (as documented). If it were undocumented behaviour you'd still not have a case but some anger would be understandable, however, you've explicitly relied on /documented-to-be-non-reliable/ behaviour, I still feel your pain, but it still isn't a bug and calling it "ridiculous" still won't change that. (Btw. - the devs might change the behaviour again, maybe even to your liking if they feel merit and it doesn't break another behaviour, the point remains that it must not be trusted until it becomes the documented behaviour.) I sincerely hope not too many of those 1000+ sql-s need fixing... Good luck! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
But column name is specified in inner select with AS clause. It can be used in outer select and work as expected. select d from (select c AS d from (select a AS c from test)); I hope you did read my example. You can see explicitly defined name in select a AS c from test then again explicitly defined new name select c AS d from and finally outer select can select only name d or what you think? select statement gives correct answer or column name but when you put CREATE TABLE AS in front of this statement you get a as column name not d in created table. Do you understand that? So outer select must have select d AS d so column must be renamed into it self to get proper name?! If I have long name then I would have select some_long_name_to_understand_column as some_long_name_to_understand_column and if I have 20 columns like that. Just ridiculous or what. So once again I use AS clause but why I must use AS clause in outer statement if name is already defined in inner select. Hick Gunter je 22.12.2017 ob 10:57 napisal: The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change. I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html: "Column Names In A Result Set ... The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next." Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html : " The ORDER BY clause If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined." The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0 I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...). Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does. Sorry but I did not ask how can I fix my sql-s. I know I can create table and insert into. Change select to select d as d is just ridiculous. Rename column name into same name. What is that? Hick Gunter je 22.12.2017 ob 7:55 napisal: The behaviour does not need to match what you think of as consistent. The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake. If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Donnerstag, 21. Dezember 2017 16:35 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 Behaviour is not consistent when using create table as or just select statement. Try this simple test. create table test(a int, b int); insert into test values (1, 1); select d from (select c as d from (select a as c from test)); you get column name d as expected but when you have create table as statement create table test2 as select d from (select c as d from (select a as c from test)); you get table test2 with column name a. If you change to this create table test2 as select d as d from (select c as d from (select a as c from test)); you will get name correct. I think it should be the same as when just using select statement. Best Regards Radovan select a from (select b from (select c from test))) Richard Hipp je 21.12.2017 ob 14:52 napisal: The behavior change is a bug fix. See http://sqlite.org/src/info/de3403bf5ae for details. On 12/21/17, Radovan Antloga wrote: I have table (create statement): CREATE TABLE SOPP1 ( STAT varchar(1) collate systemnocase, RID varchar(2) collate systemnocase, VP integer, BLANK varchar(6) collate systemnocase, NAZIV varchar(24) collate systemnocase, KN varchar(12) collate systemnocase, A varc
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change. I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html: "Column Names In A Result Set ... The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next." Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html : " The ORDER BY clause If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined." The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0 I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...). Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does. Sorry but I did not ask how can I fix my sql-s. I know I can create table and insert into. Change select to select d as d is just ridiculous. Rename column name into same name. What is that? Hick Gunter je 22.12.2017 ob 7:55 napisal: > The behaviour does not need to match what you think of as consistent. > > The only way to force a certain column name is with the AS clause *on the > outermost statement*. Otherwise, the column name is implementation defined > and may change between releases. You should not be relying on column names > other than those you explicitly set using the AS clause. This is a common > mistake. > > If you really need the column names, then just CREATE TABLE first (this gives > you defined column names AND declared data types) and the INSERT INTO ... > SELECT later (which ignores the generated column names from the select > statement). > > -Ursprüngliche Nachricht- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Radovan Antloga > Gesendet: Donnerstag, 21. Dezember 2017 16:35 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field > alias in 3.21.0 > > Behaviour is not consistent when using create table as or just select > statement. > Try this simple test. > > create table test(a int, b int); > insert into test values (1, 1); > > select d from (select c as d from (select a as c from test)); > > you get column name d as expected > but when you have create table as statement > > create table test2 as > select d from (select c as d from (select a as c from test)); > > you get table test2 with column name a. > > If you change to this > > create table test2 as > select d as d from (select c as d from (select a as c from test)); > > you will get name correct. I think it should be the same as when just using > select statement. > > Best Regards > Radovan > > > select a from (select b from (select c from test))) > > Richard Hipp je 21.12.2017 ob 14:52 napisal: >> The behavior change is a bug fix. See >> http://sqlite.org/src/info/de3403bf5ae for details. >> >> On 12/21/17, Radovan Antloga wrote: >>> I have table (create statement): >>> >>> CREATE TABLE SOPP1 ( >>> STAT varchar(1) collate systemnocase, >>> RID varchar(2) collate systemnocase, >>> VP integer, >>> BLANK varchar(6) collate systemnocase, >>> NAZIV varchar(24) collate systemnocase, >>> KN varchar(12) collate systemnocase, >>> A varchar(1) collate systemnocase, >>> B varchar(1) collate systemnocase, >>> RACUN varchar(1) collate systemnocase, >>> URE varchar(1) collate systemnocase, >>> ZN varchar(1) collate systemnocase, >>> TOCKE varchar(1) collate systemnocase, >>> PRC varchar(1) collate systemnocase, >>> UP varchar(1) collate systemnocase, >>> IZPIS varchar(1) collate systemnocase, >>> D varchar(1) collate systemnocase, >>> F2U varchar(1) collate systemnocase, >>> F2O varchar(1) collate systemnocase, >>> F2T varchar(1) collate systemnocase, >>> F2Z varchar(1) collate systemnocase, >>> F2P_1 integer, >>> F2P_2 integer, >>> F2P_3 integer, >>> F5 varchar(1) collate systemnocase, >>> AJPES varchar(1) collate systemnocase, >>> ZZ integer, >>> VD integer, >>> NS integer, >>> MES integer, >>> NORURE varcha
Re: [sqlite] What happens if an in memory database runs out of memory
I thought I had posted this earlier but I don't see it. Earlier I said the ideal solution would be something that uses memory and defaults to disc if it runs out of memory. In response Richard's suggested using a temp database with a blank name as that would use memory but parts of it would be flushed to disc if sqlite came under memory pressure (https://sqlite.org/inmemorydb.html). I tried the large insert in such a db but the performance was only on a par with a temp table with temp_store set as FILE. -- 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] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.
Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?
Hick Gunter je 22.12.2017 ob 7:55 napisal:
The behaviour does not need to match what you think of as consistent.
The only way to force a certain column name is with the AS clause *on the
outermost statement*. Otherwise, the column name is implementation defined and
may change between releases. You should not be relying on column names other
than those you explicitly set using the AS clause. This is a common mistake.
If you really need the column names, then just CREATE TABLE first (this gives
you defined column names AND declared data types) and the INSERT INTO ...
SELECT later (which ignores the generated column names from the select
statement).
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in
3.21.0
Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.
create table test(a int, b int);
insert into test values (1, 1);
select d from (select c as d from (select a as c from test));
you get column name d as expected
but when you have create table as statement
create table test2 as
select d from (select c as d from (select a as c from test));
you get table test2 with column name a.
If you change to this
create table test2 as
select d as d from (select c as d from (select a as c from test));
you will get name correct. I think it should be the same as when just using
select statement.
Best Regards
Radovan
select a from (select b from (select c from test)))
Richard Hipp je 21.12.2017 ob 14:52 napisal:
The behavior change is a bug fix. See
http://sqlite.org/src/info/de3403bf5ae for details.
On 12/21/17, Radovan Antloga wrote:
I have table (create statement):
CREATE TABLE SOPP1 (
STAT varchar(1) collate systemnocase,
RID varchar(2) collate systemnocase,
VP integer,
BLANK varchar(6) collate systemnocase,
NAZIV varchar(24) collate systemnocase,
KN varchar(12) collate systemnocase,
A varchar(1) collate systemnocase,
B varchar(1) collate systemnocase,
RACUN varchar(1) collate systemnocase,
URE varchar(1) collate systemnocase,
ZN varchar(1) collate systemnocase,
TOCKE varchar(1) collate systemnocase,
PRC varchar(1) collate systemnocase,
UP varchar(1) collate systemnocase,
IZPIS varchar(1) collate systemnocase,
D varchar(1) collate systemnocase,
F2U varchar(1) collate systemnocase,
F2O varchar(1) collate systemnocase,
F2T varchar(1) collate systemnocase,
F2Z varchar(1) collate systemnocase,
F2P_1 integer,
F2P_2 integer,
F2P_3 integer,
F5 varchar(1) collate systemnocase,
AJPES varchar(1) collate systemnocase,
ZZ integer,
VD integer,
NS integer,
MES integer,
NORURE varchar(1) collate systemnocase,
G varchar(1) collate systemnocase,
E varchar(1) collate systemnocase,
H varchar(1) collate systemnocase,
I varchar(1) collate systemnocase,
J varchar(1) collate systemnocase,
SM varchar(1) collate systemnocase,
NO varchar(1) collate systemnocase,
PRIO varchar(1) collate systemnocase,
V_1 varchar(1) collate systemnocase,
V_2 varchar(1) collate systemnocase,
V_3 varchar(1) collate systemnocase,
V_4 varchar(1) collate systemnocase,
V_5 varchar(1) collate systemnocase,
V_6 varchar(1) collate systemnocase,
V_7 varchar(1) collate systemnocase,
V_8 varchar(1) collate systemnocase,
V_9 varchar(1) collate systemnocase,
V_10 varchar(1) collate systemnocase,
V_11 varchar(1) collate systemnocase,
V_12 varchar(1) collate systemnocase,
FOR integer,
P_1 integer,
P_2 integer,
P_3 integer,
P_4 integer,
P_5 integer,
P_6 integer,
primary key (RID, VP, BLANK));
When I create new table using this sql:
drop table if exists WM4P;
create table WM4P as
select
P, A, B, AB, U, H, ZZ,
case
when AB in ('7') then 99
when AB in ('57', '58', '59', '5M') then null
when AB = '56' and ZZ = 12 then 01
when AB = '56' then 02
when A = '3' then 03
when AB in ('1M') then 08
when AB in ('10') then 07
when AB in ('12') then null
when A in ('1', '5')
Re: [sqlite] Importing Text to Create a Table
What operating system are you using, and what software are you using to do the import? What specificially are you trying to import? If you perform a sequence of 'insert' statements, then that can be time consuming - its better to incorporate them into one 'transaction' - sqlite bundles the operations into one big operation. This is better IO wise - it performs far less writes to the disk, etc. But understanding the methods you're using so far would be helpful. On Fri, Dec 22, 2017 at 12:22 AM, Lawrence Murphy wrote: > I am supporting a website which aims to protect a forest from development. > The website produces an email for supporters to mail out. A copy of the > email is sent to our Gmail address and we wish to capture the supporters > return email address. Google provides an archive of our Gmail account which > is 458Mbs in size and contains a lot of superfluous data. > > I have tried making a table with one column, text 255c in size and doing > the import but it takes more than overnight and is still running. Is there > a quicker way to import the data? > > Warm Regards, > Lawrence > > Mb: 0408 403 324 > PO Box 263 Cherrybrook NSW 2126 > > Lose Weight, Gain Health & Prevent Disease > Find out how here > http://lwghpd.blogspot.com.au/ > ___ > 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] Importing Text to Create a Table
Can you give a (short) example of some lines, and how you import them? Creating a table with 1 field text 255 seems not the right way to do this if you are only interested in emailaddresses. On 22-12-17 01:22, Lawrence Murphy wrote: > I am supporting a website which aims to protect a forest from development. > The website produces an email for supporters to mail out. A copy of the > email is sent to our Gmail address and we wish to capture the supporters > return email address. Google provides an archive of our Gmail account which > is 458Mbs in size and contains a lot of superfluous data. > > I have tried making a table with one column, text 255c in size and doing > the import but it takes more than overnight and is still running. Is there > a quicker way to import the data? > > Warm Regards, > Lawrence > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
