[sqlite] sqlite bug report

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] sqlite bug report

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] fts5 module does not build from the 3.8.11 release's source tarball

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

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

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

. 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] Join of two virtual tables returns incorrect result set in 3.8.4.1

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

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

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);

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

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] problem with INSERT after ALTER TABLE ... ADD COLUMN ... DEFAULT ... performed on source table

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

[sqlite] group_concat(distinct) with empty strings

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] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

$ 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

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

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

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

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

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

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] affinity critical problem with 3.8.5 - IN single value optimisation

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

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

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] column affinity and the query planner's use of indices

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

, 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

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

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

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

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