[sqlite] Enhance the SELECT statement?

2015-08-17 Thread Kees Nuyt
On Mon, 17 Aug 2015 20:08:23 +0200, "R.Smith" wrote: > CORRECTION: It seems one of the two options I've mentioned earlier, > namely the CREATE TABLE AS SELECT... does not actually work on the back > of a WITH clause. The other option still do, but this request has more > appeal now. CREATE TA

[sqlite] order by not working in combination with random()

2015-08-17 Thread Petite Abeille
> On Aug 17, 2015, at 12:01 PM, Clemens Ladisch wrote: > > Just because the ORDER BY clause refers to a column of the > SELECT clause does not mean that the value is not computed > a second time. And yet: with DataSet( position, value ) as ( select 1 as position, random() as value

[sqlite] order by not working in combination with random()

2015-08-17 Thread John McKown
On Aug 17, 2015 21:53, "Yuriy M. Kaminskiy" wrote: > > ... and then, at some wonderful moment, sqlite devs will implement query > flattening for CTE (like they did for subquery above), and you'll be in > square one. > > (Or, maybe, they will finally implement "common subexpression > elimination",

[sqlite] order by not working in combination with random()

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > Could the random() be made part of an expression (that doesn't change the > result) to fool the optimizer into only doing the random() once, like this: > > SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x Use a sub-sele

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 5:08pm, Olivier Barthelemy wrote: > As stated in the second question of my first message, shouldn't there be > some check in sqlite that the type in the constraints are compatible with > the fields, with an error at table creation? SQLite allows what was done, on purpose. F

[sqlite] System.Data.SQLite preRelease branch

2015-08-17 Thread Steffen Mangold
Steffen Mangold wrote: > > Until final release I want to download the preRelease [e670692d90] > created for 18 days. > Joe Mistachkin wrote: > > Normally, the "preRelease" branches are just a staging area for changes > needed during the release process. > > If packages are available for a pre

[sqlite] order by not working in combination with random()

2015-08-17 Thread Richard Brinkman
I guess that a query likeSELECT random() as x FROM some_non_empty_table ORDER BY xis indeed transformed intoSELECT random() as x FROM some_non_empty_table ORDER BY random()prior to its execution. Question is why the implementation does that. Both from an efficiency point (don't calculate somethi

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread R.Smith
On 2015-08-17 08:15 PM, Petite Abeille wrote: >> On Aug 17, 2015, at 8:08 PM, R.Smith wrote: >> >> CORRECTION: It seems one of the two options I've mentioned earlier, namely >> the CREATE TABLE AS SELECT... does not actually work on the back of a WITH >> clause. The other option still do, but

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread Petite Abeille
> On Aug 17, 2015, at 8:08 PM, R.Smith wrote: > > CORRECTION: It seems one of the two options I've mentioned earlier, namely > the CREATE TABLE AS SELECT... does not actually work on the back of a WITH > clause. The other option still do, but this request has more appeal now. Hmmm?!? create

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread R.Smith
CORRECTION: It seems one of the two options I've mentioned earlier, namely the CREATE TABLE AS SELECT... does not actually work on the back of a WITH clause. The other option still do, but this request has more appeal now. On 2015-08-17 07:24 PM, R.Smith wrote: > > > On 2015-08-17 05:44 PM, Jo

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread Petite Abeille
> On Aug 17, 2015, at 5:44 PM, John McKown > wrote: > > One thing that would be really useful for me in SQLite3 which is in > PostgreSQL is the INTO phrase. CTAS? AKA 'create table as'?

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread R.Smith
On 2015-08-17 06:08 PM, Olivier Barthelemy wrote: > OK, > I already had removed my constraint as it is, since it is obviously invalid > > So for me : > Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to > avoid other integer values, or is the constraint useless? (i guess the

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread R.Smith
On 2015-08-17 05:44 PM, John McKown wrote: > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html H

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread sqlite-mail
Hello ! The query you mention is not the same as: INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; Cheers ! > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > Subject: [sqlite] Enhance the SELECT >statement? > > I use both SQLite3 and PostgreSQL. One thing t

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Olivier Barthelemy
OK, I already had removed my constraint as it is, since it is obviously invalid So for me : Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to avoid other integer values, or is the constraint useless? (i guess the answer will be 'it depends on your code' :-P) And for sqlite

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Olivier Barthelemy
The missing space is a copy paste error. And the table IS getting created. Only the insertion fails. sqlite return code is 19 (/* Abort due to constraint violation */). > SQLite has no such type I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else. The statement is passed a

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Stephan Beal
On Mon, Aug 17, 2015 at 5:19 PM, Olivier Barthelemy < barthelemy at geovariances.com> wrote: > Create statement : > CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT, > storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR > storage_implicit = 'false'), storage_type

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Paolo Bolzoni
The pragma integrity_check is still running... Maybe my disk sucks for some reason? The output of iostat looks normal to me though.. $ iostat -dmN encplate 1 5 Linux 4.1.4-1-ARCH (slyrogue) 08/17/2015 _x86_64_(8 CPU) Device:tpsMB_read/sMB_wrtn/sMB_read

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Olivier Barthelemy
>From my logs : Create statement : CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT, storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR storage_implicit = 'false'), storage_type TEXT NOT NULL); Insert statement INSERT INTO VariableDetails (storage_implicit, stor

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Tim Streater
On 17 Aug 2015 at 16:50, Olivier Barthelemy wrote: >> SQLite has no such type > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else. > The statement is passed as is to sqlite. > On previously created sqlite files, when i open then in sqlite manager > addon of Firefox, t

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 4:50pm, Olivier Barthelemy wrote: >> SQLite has no such type > > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else. > The statement is passed as is to sqlite. SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table in section 2.2

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 4:19pm, Olivier Barthelemy wrote: > Create statement : > CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT, > storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR > storage_implicit = 'false'), storage_type TEXT NOT NULL); [I assume there is a

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

2015-08-17 Thread Paolo Bolzoni
If the problem is the multi-threading, it would be not enough to simply document that the tmp directory should be set before spawning any thread? e.g., in the main? On Sat, Aug 15, 2015 at 9:17 AM, Simon Slavin wrote: > >> On 15 Aug 2015, at 12:53am, James K. Lowden >> wrote: >> >> Simon Slavin

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Paolo Bolzoni
Dear list, I have a program that materialize the subset of a database in a second database for future use. To do so I create the database object on the OUTPUT database, create tables, create the indexes, and vacuum it just in the case I reused an old file. After I attach the INPUT db and I copy

[sqlite] order by not working in combination with random()

2015-08-17 Thread Jeffrey Mattox
Could the random() be made part of an expression (that doesn't change the result) to fool the optimizer into only doing the random() once, like this: SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x Jeff > On Aug 17, 2015, at 5:01 AM, Clemens Ladisch wrote: > > select

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Rowan Worth
On 17 August 2015 at 14:52, Paolo Bolzoni wrote: > After I attach the INPUT db and I copy the useful lines. > > The input db is about 13GB, so not really large, however the step on > this sql statement (where ? is of course binded to the db name) > > ATTACH DATABASE ? AS indb; > > requires severa

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Stephan Beal
On Mon, Aug 17, 2015 at 2:12 PM, Olivier Barthelemy < barthelemy at geovariances.com> wrote: > I have been using for a while the boolean constraint "CHECK (varname='true' > OR varname='false')" when creating tables with boolean fields and had no > issues in Qt 4.8.6 (sqlite 3.7.7.1) > This looks

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Olivier Barthelemy
Hi, I have been using sqlite through Qt. I have been using for a while the boolean constraint "CHECK (varname='true' OR varname='false')" when creating tables with boolean fields and had no issues in Qt 4.8.6 (sqlite 3.7.7.1) Now i am trying to port to Qt 5.4.2 (sqlite 3.8.6). I can load alread

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread Darren Duncan
On 2015-08-17 11:29 AM, John McKown wrote: > ?WONDERFUL! That just never entered my poor head. I guess that I was too > used to the way I do it in PostgreSQL. The way things are going for me > today, I feel that I'm in the lead role of "Moron Monday".? You know, PostgreSQL supports the same INSERT

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread John McKown
On Mon, Aug 17, 2015 at 1:15 PM, Petite Abeille wrote: > > > On Aug 17, 2015, at 8:08 PM, R.Smith wrote: > > > > CORRECTION: It seems one of the two options I've mentioned earlier, > namely the CREATE TABLE AS SELECT... does not actually work on the back of > a WITH clause. The other option stil

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 9:22am, Paolo Bolzoni wrote: > The pragma integrity_check is still running... Maybe my disk sucks for > some reason? I wonder whether the hard disk is faulty or the file is on a bad sector. If the other tests show nothing, can you duplicate the input database file ? Do

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread John McKown
On Mon, Aug 17, 2015 at 11:41 AM, sqlite-mail wrote: > Hello ! > > The query you mention is not the same as: > > INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; > ?Thanks for that. When I read the SQLite documentation, I somehow assumed that I _had_ to do a SELECT start

[sqlite] Temporary table unexpectedly disappears

2015-08-17 Thread Ulrich Telle
Am 17.08.2015 um 12:32 schrieb Richard Hipp: > On 8/17/15, Ulrich Telle wrote: >> I use version 3.8.11.1 of SQLite in my application. The application >> opens a database connection on start up. This connection is kept open >> until the application is terminated. >> >> After opening the database a

[sqlite] System.Data.SQLite preRelease branch

2015-08-17 Thread Steffen Mangold
Hi, on the "News" site I see the update with support of Visual Studio 2015 is scheduled for some day in near future. Until final release I want to download the preRelease [e670692d90] created for 18 days. When I try to download the "sqlite-netFx46-setup-bundle-x86-2015-1.0.98.0.exe" from http

[sqlite] Temporary table unexpectedly disappears

2015-08-17 Thread Ulrich Telle
I use version 3.8.11.1 of SQLite in my application. The application opens a database connection on start up. This connection is kept open until the application is terminated. After opening the database a temporary table is created. Accessing this temporary table works as expected directly after

[sqlite] order by not working in combination with random()

2015-08-17 Thread Clemens Ladisch
Richard Brinkman wrote: > When a perform the following query: > select random() as x from some_non_empty_table order by x desc limit 20; > I get something like: > -4348240540797173967 > -8823092517172356709 > 4237024158005380173 > 897958093325532613 > -6349939216731113298 > ... > which clearly is N

[sqlite] Enhance the SELECT statement?

2015-08-17 Thread John McKown
I use both SQLite3 and PostgreSQL. One thing that would be really useful for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL documentation this is here: http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html The main reason that I could use this would be in conj

[sqlite] System.Data.SQLite preRelease branch

2015-08-17 Thread Joe Mistachkin
Steffen Mangold wrote: > > Until final release I want to download the preRelease [e670692d90] > created for 18 days. > Normally, the "preRelease" branches are just a staging area for changes needed during the release process. If packages are available for a pre-release build, there will be a "Pr

[sqlite] Bug report page contains invalid e-mail address

2015-08-17 Thread Richard Brinkman
Hi, I tried to file a bug report. However the e-mail address as stated on http://www.sqlite.org/src/wiki?name=Bug+Reports (sqlite-users at sqlite.org) does not exist. It cost me quite some effort to find out the correct address which is sqlite-users at mailinglists.sqlite.org. Can someone chang

[sqlite] order by not working in combination with random()

2015-08-17 Thread Richard Brinkman
I've encountered strange behaviour which seems to be a bug in sqlite3. When a perform the following query: select random() as x from some_non_empty_table order by x desc limit 20; I get something like: -4348240540797173967 -8823092517172356709 4237024158005380173 897958093325532613 -6349939216

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 8:17am, Simon Slavin wrote: > Also, out of interest, can you run "PRAGMA integrity_check" on all the > database files involved ? Also out of interest, instead of just ATTACHing the input database, 1) Close the output database 2) Reopen the output database 3) ATTACH the inpu

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

2015-08-17 Thread Richard Hipp
On 8/17/15, Olivier Barthelemy wrote: > Hi, > > I have been using sqlite through Qt. > > I have been using for a while the boolean constraint "CHECK (varname='true' > OR varname='false')" when creating tables with boolean fields and had no > issues in Qt 4.8.6 (sqlite 3.7.7.1) > > Now i am trying

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 8:11am, Paolo Bolzoni wrote: > If the problem is the multi-threading, it would be not enough to > simply document that the tmp directory should be set before spawning > any thread? e.g., in the main? It's partly a philosophical one. The setting for the temporary directory i

[sqlite] ATTACH DATABASE statement speed

2015-08-17 Thread Simon Slavin
On 17 Aug 2015, at 7:52am, Paolo Bolzoni wrote: > I have a program that materialize the subset of a database in a second > database for future use. > > To do so I create the database object on the OUTPUT database, create > tables, create the indexes, and vacuum it just in the case I reused an

[sqlite] Temporary table unexpectedly disappears

2015-08-17 Thread Richard Hipp
On 8/17/15, Ulrich Telle wrote: > I use version 3.8.11.1 of SQLite in my application. The application > opens a database connection on start up. This connection is kept open > until the application is terminated. > > After opening the database a temporary table is created. Accessing this > tempora