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

[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 versions.


CREATE TABLE C(A);
CREATE TABLE D(A);

CREATE TABLE join_two_tables AS SELECT C.A FROM C INNER JOIN D ON D.A=C.A;
.schema join_two_tables

CREATE VIEW G AS SELECT * FROM D;

CREATE TABLE join_table_with_view AS SELECT C.A FROM C INNER JOIN G ON
G.A=C.A;
.schema join_table_with_view

CREATE TABLE join_table_with_subselect AS SELECT C.A FROM C INNER JOIN
(SELECT * FROM D) D ON D.A=C.A;
.schema join_table_with_subselect


Output:


CREATE TABLE join_two_tables(A);
CREATE TABLE join_table_with_view("C.A");
CREATE TABLE join_table_with_subselect("C.A");


Best regards,
John Hinrichsen

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

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

[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 versions.


CREATE TABLE C(A);
CREATE TABLE D(A);

CREATE TABLE join_two_tables AS SELECT C.A FROM C INNER JOIN D ON D.A=C.A;
.schema join_two_tables

CREATE VIEW G AS SELECT * FROM D;

CREATE TABLE join_table_with_view AS SELECT C.A FROM C INNER JOIN G ON
G.A=C.A;
.schema join_table_with_view

CREATE TABLE join_table_with_subselect AS SELECT C.A FROM C INNER JOIN
(SELECT * FROM D) D ON D.A=C.A;
.schema join_table_with_subselect


Output:


CREATE TABLE join_two_tables(A);
CREATE TABLE join_table_with_view("C.A");
CREATE TABLE join_table_with_subselect("C.A");


Best regards,
John Hinrichsen

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

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

[Hinrichsen, John]

Update:

I don't have a problem compiling under centos 7 (gcc 4.8.3), but with
centos 6 (gcc 4.4.7) I do get this error.

fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
fts5Int.h:83: note: previous declaration of 'Fts5Global' was here

Unfortunately, I still have to support centos 6.


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:
>>
>>> 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
>>> ).
>>>
>>
>> Which step failed?
>>
>>
>>
>>
>>> I was able to 'make fts5.c' following the instructions that reference the
>>> "trunk" tarball.
>>>
>>> Regards,
>>> John Hinrichsen
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

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

[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 versioned source
>> tarball for this release (
>> http://www.sqlite.org/2015/sqlite-src-3081100.zip
>> ).
>>
>
> Which step failed?
>
>
>
>
>> I was able to 'make fts5.c' following the instructions that reference the
>> "trunk" tarball.
>>
>> Regards,
>> John Hinrichsen
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

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

[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 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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

Re: [sqlite] sqlite bug report

[Hinrichsen, John]

In this example, bad data is returned.  There is no assert.  valgrind does
not complain either.

Is there an ETA on when 3.8.7.2 will be released?


On Thu, Nov 13, 2014 at 1:12 PM, Richard Hipp <d...@sqlite.org> wrote:

> This is https://www.sqlite.org/src/info/094d39a4c95ee4 which 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(
> >   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 symbol,A.type,
> > CASE A.type
> > WHEN 'chars' THEN A.symbol
> > WHEN 'num' THEN B.chars
> > ELSE NULL
> > END AS chars
> > FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;
> >
> > SELECT * FROM C;
> >
> > with 3.8.7.1:
> >
> > sqlite> SELECT * FROM C;
> > ABCDEFG|chars|ABCDEFG
> > 1234567890|num|1234567
> >
> > with 3.8.6:
> >
> > sqlite> SELECT * FROM C;
> > ABCDEFG|chars|ABCDEFG
> > 1234567890|num|
> >
> > --
> >
> > 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 the
> > sender immediately by e-mail if you have received this e-mail by mistake
> > and delete this e-mail from your system. E-mail transmissions cannot be
> > guaranteed to be secure or without error as information could be
> > intercepted, corrupted, lost, destroyed, or arrive late or incomplete.
> The
> > sender, therefore, does not accept liability for any errors or omissions
> in
> > the contents of this message which arise during or as a result of e-mail
> > transmission. If verification is required, please request a hard-copy
> > version. This message is provided for information purposes and should not
> > be construed as a solicitation or offer to buy or sell any securities or
> > related financial instruments in any jurisdiction.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite bug report

[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 symbol,A.type,
CASE A.type
WHEN 'chars' THEN A.symbol
WHEN 'num' THEN B.chars
ELSE NULL
END AS chars
FROM A LEFT OUTER JOIN B ON A.type='num'  AND B.num=A.symbol;

SELECT * FROM C;

with 3.8.7.1:

sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|1234567

with 3.8.6:

sqlite> SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hinrichsen, John]

According to the documentation, when creating a table "AS SELECT ...", the
"affinity of comparison operands" rules are applied; what I am suggesting
is that these rules be extended when used with "CREATE TABLE ... AS SELECT
..."

http://www.sqlite.org/datatype3.html#expraff
3.2 Affinity Of Comparison Operands

SQLite may attempt to convert values between the storage classes INTEGER,
REAL, and/or TEXT before performing a comparison. Whether or not any
conversions are attempted before the comparison takes place depends on the
affinity of the operands. Operand affinity is determined by the following
rules:

   -

   The affinity of the right-hand operand of an IN or NOT IN operator is
   NONE if the operand is a list and is the same as the affinity of the result
   set expression if the operand is a SELECT.
   -

   An expression that is a simple reference to a column value has the same
   affinity as the column. Note that if X and Y.Z are column names, then +X
   and +Y.Z are considered expressions for the purpose of determining affinity.
   -

   An expression of the form "CAST(*expr* AS *type*)" has an affinity that
   is the same as 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
> > applies when creating a table using a SELECT where a column is the result
> > of an expression (such as min, max, or sum) or within a CTE (in the
> example
> > provided, where the expression can obviously only produce integers.)
> >
> > [snip]
> >
> > If SQLite (optionally?) permitted us to avoid writing these casts, by
> > automatically deducing the correct column affinity, it would correctly
> make
> > use of indices created, which would benefit everyone.
>
> Okay.  So the problem is with sub-SELECT which produces values without
> affinities.  What you want is for MIN() and MAX() to have the same affinity
> as the value they choose, and for SUM() to have an affinity of REAL.
>  Thanks for the explanation.
>
> So now I'm interested to know whether functions created with
> sqlite3_create_function() return a value with an affinity or just a value.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hinrichsen, John]

In most cases it should be possible for SQLite to perform type inference,
i.e. deduce the type of an expression, rather than assign the column
affinity to None for all columns that are produced by expressions.  This
applies when creating a table using a SELECT where a column is the result
of an expression (such as min, max, or sum) or within a CTE (in the example
provided, where the expression can obviously only produce integers.)

Because expressions can be anywhere and everywhere, I have to perform a
cast whenever a column is produced by an expression.  The cast is not a
one-off, do it once and forget about it kind of thing.  In SQLite, a cast
must wrap every expression if indices are to be used consistently.

In my original post I note that the cast does work--but:
-- it's redundant (the cast is a possible source of bugs if the column type
is later changed)
-- it's unintuitive (why can't the database figure out the right column
affinity?)
-- it's specific to SQLite (other DBs do assign the correct column type
automatically)
-- the code works anyway without the casts--which can be very
misleading--but performs significantly worse, because it may stubbornly
refuse to use an index that has been provided

If SQLite (optionally?) permitted us to avoid writing these casts, by
automatically deducing the correct column affinity, it would correctly make
use of indices created, which would benefit everyone.  It would also
simplify the writing of queries, remove redundant code, 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...@c10p.com> wrote:
>
> >>> It would be more intuitive: why should aggregate functions like min(),
> >>> max(), and sum() return column data stripped of the original column
> >>> affinity?
>
> Can you talk us through the original problem again ?
>
> Are you talking about the affinity of the column x, or the affinity of the
> result of these functions.  And how is this a problem ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hinrichsen, John]

Hi Richard,

Your concern about breaking existing code makes a lot of sense.  Would you
consider a PRAGMA or compile-time directive to enable keeping column
affinity where possible?

I tested postgres, and found that columns produced by aggregate functions
retain the column affinity of the input columns.  Most users of sqlite have
experience with other sql databases, so it's fair to assume that a lot of
sql for sqlite is losing column affinity unintentionally in cases like this.


On Tue, Jul 8, 2014 at 11:09 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Tue, Jul 8, 2014 at 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.  Changing the rules would break
> some fraction of those half-million apps, which would likely be very
> annoying to the developers.
>
>
>
>
> > so that
> > expressions would, at least in easy-to-deduce cases, automatically assign
> > the appropriate column affinity?
> >
> > Making this change would improve the performance of some queries (see my
> > original email.)
> >
> > It would be more 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 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 effective way to enforce
> column
> > > affinities, at the expense of redundant code.  Column types that could
> be
> > > declared in just one place, or simply inferred, have to be repeatedly
> > > re-declared via casts.  I assume that this is a result of sqlite's
> > manifest
> > > typing.
> > >
> > > Would you consider changing the column affinity determination rules
> such
> > > that expressions could, at least in easy-to-deduce cases, automatically
> > > assign the appropriate column affinity?
> > >
> > > My colleague Ivan, who diagnosed the issue, put the following repro
> > > together to illustrate the problem.  He uses the syntax "a JOIN b ON
> b.x
> > > IN (a.x)" and compares its query plan and performance to that of using
> > > the standard join syntax.  He also requests the query planner to
> > > specifically use the index within the context of the normal join
> syntax,
> > > which the query planner rejects.
> > >
> > > $ 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>
> > > sqlite> CREATE TEMP TABLE z AS
> > >...> WITH RECURSIVE cnt(x) AS (
> > >...> VALUES(1) UNION ALL SELECT x+1 FROM cnt
> > >...> WHERE x < 1e6
> > >...> ) SELECT x FROM cnt;
> > > sqlite>
> > > sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY);
> > > sqlite> INSERT INTO a SELECT x FROM temp.z;
> > > sqlite>
> > > sqlite> CREATE TABLE b AS SELECT x FROM temp.z;
> > > sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x);
> > > sqlite>
> > > sqlite> ANALYZE;
> > > sqlite>
> > > sqlite> PRAGMA table_info(a);
> > > cid nametypenotnull dflt_value  pk
> > > --  --  --  --  --  --
> > > 0   x   INTEGER 1   1
> > > sqlite> PRAGMA table_info(b);
> > > cid nametypenotnull dflt_value  pk
> > > --  --  --  --  --  --
> > > 0   x   0   0
> > > sqlite> PRAGMA index_info(b_idx_1);
> > > seqno   cid name
> > > --  --  --
> > > 0   0   x
> > > sqlite> EXPLAIN QUERY PLAN
> > >...> SELECT * FROM a JOIN

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

[Hinrichsen, John]

Hi,

Would you consider changing the column affinity determination rules so that
expressions would, at least in easy-to-deduce cases, automatically assign
the appropriate column affinity?

Making this change would improve the performance of some queries (see my
original email.)

It would be more 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 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 effective way to enforce column
> affinities, at the expense of redundant code.  Column types that could be
> declared in just one place, or simply inferred, have to be repeatedly
> re-declared via casts.  I assume that this is a result of sqlite's manifest
> typing.
>
> Would you consider changing the column affinity determination rules such
> that expressions could, at least in easy-to-deduce cases, automatically
> assign the appropriate column affinity?
>
> My colleague Ivan, who diagnosed the issue, put the following repro
> together to illustrate the problem.  He uses the syntax "a JOIN b ON b.x
> IN (a.x)" and compares its query plan and performance to that of using
> the standard join syntax.  He also requests the query planner to
> specifically use the index within the context of the normal join syntax,
> which the query planner rejects.
>
> $ 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>
> sqlite> CREATE TEMP TABLE z AS
>...> WITH RECURSIVE cnt(x) AS (
>...> VALUES(1) UNION ALL SELECT x+1 FROM cnt
>...> WHERE x < 1e6
>...> ) SELECT x FROM cnt;
> sqlite>
> sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY);
> sqlite> INSERT INTO a SELECT x FROM temp.z;
> sqlite>
> sqlite> CREATE TABLE b AS SELECT x FROM temp.z;
> sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x);
> sqlite>
> sqlite> ANALYZE;
> sqlite>
> sqlite> PRAGMA table_info(a);
> cid nametypenotnull dflt_value  pk
> --  --  --  --  --  --
> 0   x   INTEGER 1   1
> sqlite> PRAGMA table_info(b);
> cid nametypenotnull dflt_value  pk
> --  --  --  --  --  --
> 0   x   0   0
> sqlite> PRAGMA index_info(b_idx_1);
> seqno   cid name
> --  --  --
> 0   0   x
> sqlite> EXPLAIN QUERY PLAN
>...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123;
> selectidorder   fromdetail
> --  --  --
> --
> 0   0   0   SEARCH TABLE a USING INTEGER PRIMARY
> KEY (rowid=?)
> 0   1   1   SCAN TABLE b
> sqlite> EXPLAIN QUERY PLAN
>...> SELECT * FROM a JOIN b INDEXED BY b_idx_1 ON a.x = b.x AND a.x =
> 123;
> Error: no query solution
>
> sqlite> EXPLAIN QUERY PLAN
>...> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123;
> selectidorder   fromdetail
> --  --  --
> --
> 0   0   0   SEARCH TABLE a USING INTEGER PRIMARY
> KEY (rowid=?)
> 0   1   1   SEARCH TABLE b USING COVERING INDEX
> b_idx_1 (x=?)
> 0   0   0   EXECUTE LIST SUBQUERY 1
> sqlite> .timer on
> sqlite> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123;
> x   x
> --  --
> 123 123
> Run Time: real 0.157 user 0.155976 sys 0.00
>
> sqlite> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123;
> x   x
> --  --
> 123 123
> Run Time: real 0.000 user 0.00 sys 0.00
> sqlite>
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-m

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

[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 text);
> sqlite> insert into T values('1');
> sqlite> insert into T values('2');
> sqlite> select v from T where v=1;
> 1
> sqlite> select v from T where v='1';
> 1
> sqlite> select v from T where v IN(1);
> sqlite> select v from T where v IN('1');
> 1
> sqlite> select v from T where v IN(1,2);
> 1
> 2
> sqlite> select v from T where v IN('1','2');
> 1
> 2
> --- /CUT ---
>
>
> It must be linked to this change:
>
> "Render expressions of the form "x IN (?)" (with a single value in the
> list on the right-hand side of the IN operator) as if they where "x==?",
> Similarly optimize "x NOT IN (?)""
>
> Best regards,
> --
> Guillaume FOUGNIES
> Eulerian Technologies
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 effective way to enforce column
affinities, at the expense of redundant code.  Column types that could be
declared in just one place, or simply inferred, have to be repeatedly
re-declared via casts.  I assume that this is a result of sqlite's manifest
typing.

Would you consider changing the column affinity determination rules such
that expressions could, at least in easy-to-deduce cases, automatically
assign the appropriate column affinity?

My colleague Ivan, who diagnosed the issue, put the following repro
together to illustrate the problem.  He uses the syntax "a JOIN b ON b.x IN
(a.x)" and compares its query plan and performance to that of using the
standard join syntax.  He also requests the query planner to specifically
use the index within the context of the normal join syntax, which the query
planner rejects.

$ 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>
sqlite> CREATE TEMP TABLE z AS
   ...> WITH RECURSIVE cnt(x) AS (
   ...> VALUES(1) UNION ALL SELECT x+1 FROM cnt
   ...> WHERE x < 1e6
   ...> ) SELECT x FROM cnt;
sqlite>
sqlite> CREATE TABLE a(x INTEGER NOT NULL PRIMARY KEY);
sqlite> INSERT INTO a SELECT x FROM temp.z;
sqlite>
sqlite> CREATE TABLE b AS SELECT x FROM temp.z;
sqlite> CREATE UNIQUE INDEX b_idx_1 ON b(x);
sqlite>
sqlite> ANALYZE;
sqlite>
sqlite> PRAGMA table_info(a);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   x   INTEGER 1   1
sqlite> PRAGMA table_info(b);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   x   0   0
sqlite> PRAGMA index_info(b_idx_1);
seqno   cid name
--  --  --
0   0   x
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123;
selectidorder   fromdetail
--  --  --
--
0   0   0   SEARCH TABLE a USING INTEGER PRIMARY
KEY (rowid=?)
0   1   1   SCAN TABLE b
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM a JOIN b INDEXED BY b_idx_1 ON a.x = b.x AND a.x =
123;
Error: no query solution

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123;
selectidorder   fromdetail
--  --  --
--
0   0   0   SEARCH TABLE a USING INTEGER PRIMARY
KEY (rowid=?)
0   1   1   SEARCH TABLE b USING COVERING INDEX
b_idx_1 (x=?)
0   0   0   EXECUTE LIST SUBQUERY 1
sqlite> .timer on
sqlite> SELECT * FROM a JOIN b ON a.x = b.x AND a.x = 123;
x   x
--  --
123 123
Run Time: real 0.157 user 0.155976 sys 0.00

sqlite> SELECT * FROM a JOIN b ON b.x IN (a.x) AND a.x = 123;
x   x
--  --
123 123
Run Time: real 0.000 user 0.00 sys 0.00
sqlite>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hinrichsen, John]

These are all good points.

Have you considered implementing hash joins for tables that join on columns
that are not indexed?  Typical hash joins (using the equality operator) can
be performed in O(N) time without indexes.  Because hash joins evaluate
each row just once, they might also permit us 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 at 5:21 PM, Richard Hipp <d...@sqlite.org> 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 query does use the automatic
> > covering index.  The analyze wasn't necessary with sqlite-3.7.17.
> >
>
> The query planner in 3.7.17 was not nearly as clever as the 3.8.0+ query
> planner.  It got the right answer given wrong information by dumb luck.
> See http://www.sqlite.org/queryplanner-ng.html and especially
> http://www.sqlite.org/queryplanner-ng.html#howtofix for further
> information.
>
> Also, it is generally considered good practice to create sufficient indices
> to avoid having to use an automatic index.  Using an automatic index will
> make a two-way join O(NlogN).  That's better than the O(N*N) that would
> occur without the automatic index, but you could have O(logN) if an
> appropriate persistent index is available.  I know that there may arise
> cases where the query is sufficiently infrequent and the size of the
> necessary index is sufficiently large, that you may want to deliberately
> make use of a transient automatic index.  But those cases are rare.  SQLite
> comes with instrumentation (specifically the SQLITE_STMTSTATUS_AUTOINDEX
> verb for sqlite3_stmt_status() -
> http://www.sqlite.org/c3ref/stmt_status.html) that can be used to detect
> when automatic indices are used and alert the developer through a back
> channel to this fact so that she can fix the problem with an appropriate
> CREATE INDEX.  In other words, SQLite provides you with the tools to help
> you detect and eliminate the use of automatic indices.  Just saying
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 query does use the automatic
covering index.  The analyze wasn't necessary with sqlite-3.7.17.

The following will demonstrate the performance regression:

CREATE TABLE x AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1
FROM t WHERE n < 5 ) SELECT 1 AS a, n AS b FROM t;
CREATE TABLE y AS SELECT b FROM x;
CREATE INDEX ix ON x(a);
SELECT COUNT(*) FROM (SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE x.a=1);

Although you can't execute the first statement under sqlite-3.7.17, you can
save the db after creating it.

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 JOIN y ON x.b=y.b;
0|0|0|SCAN TABLE x (~100 rows)
0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE
x.a = 1;
0|0|0|SEARCH TABLE x USING INDEX ix (a=?) (~10 rows)
0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows)
sqlite>

$ 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 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 JOIN y ON x.b=y.b;
0|0|0|SCAN TABLE x
0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE
x.a = 1;
0|0|0|SEARCH TABLE x USING INDEX ix (a=?)
0|1|1|SCAN TABLE y
sqlite>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group_concat(distinct) with empty strings

[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 COALESCE(a,'') FROM z) IS NULL;
0
sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,'')) FROM z) IS
NULL;
1
sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,' ')) FROM z) IS
NULL;
0
sqlite>

This problem looks similar to:
http://sqlite.1065341.n5.nabble.com/group-concat-and-empty-strings-td62226.html

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 ..." is executed.

See example below:

$ 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> .header on
sqlite> .mode column
sqlite> CREATE TABLE a(a);
sqlite> INSERT INTO a VALUES(1);
sqlite>
sqlite> ALTER TABLE a ADD COLUMN b DEFAULT 2;
sqlite>
sqlite> CREATE TABLE b AS SELECT * FROM a;
sqlite> INSERT INTO  bSELECT * FROM a;
sqlite>
sqlite> SELECT * FROM a;
a   b
--  --
1   2
sqlite> SELECT * FROM b;
a   b
--  --
1   2
1
sqlite>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hinrichsen, John]

_OK);

res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t1 USING test("
   "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY
KEY (C1,C2))',"
   "'SELECT DISTINCT A,B FROM (SELECT A,B FROM
t0 GROUP BY A,B)  ORDER BY A,B')",
   NULL, NULL, NULL);
assert(res == SQLITE_OK);

res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t2 USING test("
   "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY
KEY (C0,C2))',"
   "'SELECT DISTINCT A,B FROM (SELECT A,B FROM
t9 GROUP BY A,B)  ORDER BY A,B')",
   NULL, NULL, NULL);
assert(res == SQLITE_OK);

printf("Joining virtual tables:\n");
execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t1 L JOIN t2
R ON L.C1=R.C1 AND L.C2=R.C2");

res = sqlite3_exec(db, "CREATE TABLE t3 AS SELECT * FROM t1", NULL,
NULL, NULL);
assert(res == SQLITE_OK);

res = sqlite3_exec(db, "CREATE TABLE t4 AS SELECT * 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);
s_db = db = NULL;

return 0;
}



On Wed, Apr 2, 2014 at 7:53 PM, Donald Griggs <dfgri...@gmail.com> wrote:

> Attachments can't appear on this list.   You can use a shared file service
> and post a link, or for smallish amounts of text use something like
> pastbin.com.
>
>
> On Wed, Apr 2, 2014 at 6:42 PM, Andy Goth <andrew.m.g...@gmail.com> wrote:
>
> > On 4/2/2014 4:52 PM, Hinrichsen, John wrote:
> >
> >> 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.
> >>
> >
> > Attachment appears to be missing.
> >
> > --
> > Andy Goth | 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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 the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users