[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect

2016-04-13 Thread Hinrichsen, John
Hello all, The schemas generated by the last two joins below are not what I was expecting. Could someone point me to documentation on how the schema generation works when using a subselect in a join? This was tested on 3.7.17, 3.9.2, and 3.11.0, and the behavior is consistent across these three

[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect

2016-04-13 Thread Hinrichsen, John
Hello all, The schemas generated by the last two joins below are not what I was expecting. Could someone point me to documentation on how the schema generation works when using a subselect in a join? This was tested on 3.7.17, 3.9.2, and 3.11.0, and the behavior is consistent across these three

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

2015-07-31 Thread Hinrichsen, John
. On Mon, Jul 27, 2015 at 4:16 PM, Hinrichsen, John wrote: > This was the error I got: > > fts5_main.c:30: error: redefinition of typedef 'Fts5Global' > > > On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy > wrote: > >> On 07/28/2015 02:55 AM, Hinrichsen, John wrote: >

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

2015-07-27 Thread Hinrichsen, John
This was the error I got: fts5_main.c:30: error: redefinition of typedef 'Fts5Global' On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy wrote: > On 07/28/2015 02:55 AM, Hinrichsen, John wrote: > >> Hi, >> >> I was not able to get the fts5 module to build from the ver

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

2015-07-27 Thread Hinrichsen, John
Hi, I was not able to get the fts5 module to build from the versioned source tarball for this release (http://www.sqlite.org/2015/sqlite-src-3081100.zip ). I was able to 'make fts5.c' following the instructions that reference the "trunk" tarball. Regards, John Hinrichsen -- This message

Re: [sqlite] sqlite bug report

2014-11-13 Thread Hinrichsen, John
has been > fixed > in trunk and will be fixed in 3.8.7.2. > > On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John <jhinrich...@c10p.com> > wrote: > > > The following SQL produces an incorrect result with sqlite-3.8.7.1: > > > > CREATE TABLE A( > > sym

[sqlite] sqlite bug report

2014-11-13 Thread Hinrichsen, John
The following SQL produces an incorrect result with sqlite-3.8.7.1: CREATE TABLE A( symbol TEXT, type TEXT ); INSERT INTO A VALUES('ABCDEFG','chars'); INSERT INTO A VALUES('1234567890','num'); CREATE TABLE B( chars TEXT, num TEXT ); CREATE TABLE IF NOT EXISTS C AS SELECT A.symbol AS

Re: [sqlite] column affinity and the query planner's use of indices

2014-07-09 Thread Hinrichsen, John
a column with a declared type of "*type*". - Otherwise, an expression has NONE affinity. On Tue, Jul 8, 2014 at 7:31 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Jul 2014, at 11:11pm, Hinrichsen, John <jhinrich...@c10p.com> wrote: > > > This

Re: [sqlite] column affinity and the query planner's use of indices

2014-07-08 Thread Hinrichsen, John
, and behave like SQL as understood by other DBs. An alternative might be to make SQLite consistently use indices regardless of column affinity. On Tue, Jul 8, 2014 at 1:47 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Jul 2014, at 6:16pm, Hinrichsen, John <jhinrich...@

Re: [sqlite] column affinity and the query planner's use of indices

2014-07-08 Thread Hinrichsen, John
11:01 AM, Hinrichsen, John <jhinrich...@c10p.com> > wrote: > > > Hi, > > > > Would you consider changing the column affinity determination rules > > > Probably not. There are over a half million apps (literally) in > circulation that use the existing rules. Chan

Re: [sqlite] column affinity and the query planner's use of indices

2014-07-08 Thread Hinrichsen, John
intuitive: why should aggregate functions like min(), max(), and sum() return column data stripped of the original column affinity? On Fri, May 23, 2014 at 2:21 PM, Hinrichsen, John <jhinrich...@c10p.com> wrote: > At table creation time, when column types are not declared e

Re: [sqlite] affinity critical problem with 3.8.5 - IN single value optimisation

2014-07-08 Thread Hinrichsen, John
This is a nasty bug; I do not see any follow-up regarding a fix. On Thu, Jun 26, 2014 at 9:17 AM, Guillaume Fougnies wrote: > Hi, > > It seems there's a problem with 3.8.5 and its affinity behavior. > It's quite critical. > > --- CUT --- > sqlite> CREATE TABLE T (v

[sqlite] column affinity and the query planner's use of indices

2014-05-23 Thread Hinrichsen, John
At table creation time, when column types are not declared explicitly, or are produced by an expression, column affinity defaults to NONE, with the result that indexes added afterwards often go unused in joins because of a column affinity mismatch. Adding casts around the expressions is an

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-08 Thread Hinrichsen, John
to make calls to scalar functions more efficiently within the context of the join. On Wed, May 7, 2014 at 8:30 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 7, 2014 at 6:58 PM, Hinrichsen, John <jhinrich...@c10p.com > >wrote: > > > On Wed, May 7, 2014

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Hinrichsen, John
On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > Do you have a database file where the 3.8.4.3 query plan really is slower? > Can you please run ANALYZE on that database and send us the content of the > "sqlite_stat1" table? > > It is true that if we add the analyze, the

[sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

2014-05-07 Thread Hinrichsen, John
$ sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; sqlite> CREATE INDEX ix ON x (a); sqlite> CREATE TABLE y AS SELECT 1 AS b; sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER

[sqlite] group_concat(distinct) with empty strings

2014-05-06 Thread Hinrichsen, John
Are the results below expected? $ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE z AS SELECT NULL AS a; sqlite> SELECT (SELECT DISTINCT

[sqlite] problem with INSERT after ALTER TABLE ... ADD COLUMN ... DEFAULT ... performed on source table

2014-04-25 Thread Hinrichsen, John
Default non-NULL values copied from a column that was added using "ALTER TABLE ... ADD COLUMN ... DEFAULT ..." are inserted into another table as NULLs when copied using "INSERT INTO ... SELECT * FROM ..." However, the same values are propagated correctly when "CREATE TABLE ... AS SELECT * FROM

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

2014-04-03 Thread Hinrichsen, John
That was a fast turn-around. Thank you for addressing this issue so quickly! -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify

[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

2014-04-03 Thread Hinrichsen, John
T * FROM t2", NULL, NULL, NULL); assert(res == SQLITE_OK); printf("Joining nonvirtual tables based on virtual tables:\n"); execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t3 L JOIN t4 R ON L.C1=R.C1 AND L.C2=R.C2"); sqlite3_close(db);

[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

2014-04-02 Thread Hinrichsen, John
sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. -- This message contains confidential information and is intended