Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> I would agree that no warning is needed for for columns that don't state 
> any affinity, or for a non-affinity FK that refers to some PK with 
> affinity.
> 
> I tend to agree with OP that an explicitly text foreign key referring to 
> an explicitly int primary key is probably worth a warning (perhaps from 
> the hypothetical "lint mode" that surfaces now and then),

I agree. So it's basically very comfortable to have such a "lazy" type checking,
but a "lint mode" could help similar situations.

Thanks everyone for all the answers,
Tamás


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Jim Morris]

I believe a check constraint with an appropriate typeof comparison has 
been suggested for this usage.


On 3/6/2013 6:29 AM, Ryan Johnson wrote:
I would agree that no warning is needed for for columns that don't 
state any affinity, or for a non-affinity FK that refers to some PK 
with affinity.


I tend to agree with OP that an explicitly text foreign key referring 
to an explicitly int primary key is probably worth a warning (perhaps 
from the hypothetical "lint mode" that surfaces now and then), since 
it's pretty likely that a user who took the trouble to specify 
affinities for both PK and FK probably made a mistake if the types are 
different. Sure, some record might override affinity and store 'abc' 
as its "int" primary key, but even if your app relies on that 
behavior, an "int" foreign key would be harmless for the same reason.


Off topic, I'd love a way to request strong typing for a column (so 
that attempts to store 'abc' into an int column would fail). You can 
emulate it with a pair of before/update triggers (select raise(...) 
where typeof(intcol)!='integer'), but that's clunky. Oh well... in 
retrospect, most of the times I've been bitten by type mismatches were 
probably either due to this bug or (more likely) due to my not 
specifying any affinity at all and then being surprised when 1 != '1'.


Ryan

On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens 
to be

orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:


Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.

A simple, reproducible test case for (what we think is) your 
problem can

be

seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you 
are the
first to hit it.  Probably this is because not many applications 
contain
A=B in the WHERE clause where A is a text expression and B is an 
integer

expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.

  This
does not excuse SQLite:  It is still getting the wrong answer and 
needs

to

be fixed.  We are working on a fix now.  But a simple change to your

schema

will work around the problem and get you going even before that fix is
available.
Very good example, thank you! It really solved my problem. 
Nevertheless, I

think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an 
error (or

at
least a warning) in cases where a foreign key constraint refers to a
different
data type?

Thanks again,
Tamás

___
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


___
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

Re: [sqlite] inner vs. outer join inconsistency

[Simon Slavin]

On 6 Mar 2013, at 9:50am, Tom Matrix  wrote:

> My only remaining concern is, however: Should not SQLite give an error (or at
> least a warning) in cases where a foreign key constraint refers to a different
> data type?

I see why you asked but that won't work in SQLite.  Because SQLite doesn't have 
column types, it just has column affinities, which aren't enforced.  Each 
individual value in a column may have a different type.  You could define the 
column type as 'integer' but put the text 'overdrawn' into the value for one 
row.

You can see the rules that SQLite actually follows when comparing values of 
different types in section 3.3 of



Not that this would help you in the case reported because of the (now fixed) 
bug.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Ryan Johnson]

I would agree that no warning is needed for for columns that don't state 
any affinity, or for a non-affinity FK that refers to some PK with 
affinity.


I tend to agree with OP that an explicitly text foreign key referring to 
an explicitly int primary key is probably worth a warning (perhaps from 
the hypothetical "lint mode" that surfaces now and then), since it's 
pretty likely that a user who took the trouble to specify affinities for 
both PK and FK probably made a mistake if the types are different. Sure, 
some record might override affinity and store 'abc' as its "int" primary 
key, but even if your app relies on that behavior, an "int" foreign key 
would be harmless for the same reason.


Off topic, I'd love a way to request strong typing for a column (so that 
attempts to store 'abc' into an int column would fail). You can emulate 
it with a pair of before/update triggers (select raise(...) where 
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect, 
most of the times I've been bitten by type mismatches were probably 
either due to this bug or (more likely) due to my not specifying any 
affinity at all and then being surprised when 1 != '1'.


Ryan

On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens to be
orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:


Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.


A simple, reproducible test case for (what we think is) your problem can

be

seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.

  This

does not excuse SQLite:  It is still getting the wrong answer and needs

to

be fixed.  We are working on a fix now.  But a simple change to your

schema

will work around the problem and get you going even before that fix is
available.

Very good example, thank you! It really solved my problem. Nevertheless, I
think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or
at
least a warning) in cases where a foreign key constraint refers to a
different
data type?

Thanks again,
Tamás

___
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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Ryan Johnson]

On 06/03/2013 4:50 AM, Tom Matrix wrote:

Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.


A simple, reproducible test case for (what we think is) your problem can be
seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.  This
does not excuse SQLite:  It is still getting the wrong answer and needs to
be fixed.  We are working on a fix now.  But a simple change to your schema
will work around the problem and get you going even before that fix is
available.

Very good example, thank you! It really solved my problem. Nevertheless, I think
I don't have to mention that entries.measurementid must have been an integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or at
least a warning) in cases where a foreign key constraint refers to a different
data type?
My understanding is that it shouldn't have mattered, because the text 
column should have been converted to int before performing the 
comparison. This works for simpler queries where the non-int column is 
only compared with an int column. Simplifying the test case from the 
ticket above:


sqlite3> select * from t,i where textid=intid;
12|12
34|34

However, when comparing the non-int column with both an int column and a 
non-int one, the bug is that the promotion no longer happens and the 
join fails:


sqlite3> select * from t t1,t t2, i where t1.textid=i.intid and 
t1.textid=t2.textid;

(nothing)

Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Stephen Chrzanowski]

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens to be
orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:

> Richard Hipp  writes:
>
> >
> > On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> >
> > >
> > > I’ve encountered a problem, which is hardly reproducable on arbitrary
> > > databases, therefore I attached one.
> > >
> >
> > A simple, reproducible test case for (what we think is) your problem can
> be
> > seen in this ticket:
> >
> >  www.sqlite.org/src/tktview/fc7bd6358f59b
> >
> > This bug has been latent in SQLite for almost four years and you are the
> > first to hit it.  Probably this is because not many applications contain
> > A=B in the WHERE clause where A is a text expression and B is an integer
> > expression.  You can probably work around the problem by changing your
> > schema so that entries.measurementid is an integer rather than text.
>  This
> > does not excuse SQLite:  It is still getting the wrong answer and needs
> to
> > be fixed.  We are working on a fix now.  But a simple change to your
> schema
> > will work around the problem and get you going even before that fix is
> > available.
>
> Very good example, thank you! It really solved my problem. Nevertheless, I
> think
> I don't have to mention that entries.measurementid must have been an
> integer,
> and this bug must be fixed in our schema, too.
>
> My only remaining concern is, however: Should not SQLite give an error (or
> at
> least a warning) in cases where a foreign key constraint refers to a
> different
> data type?
>
> Thanks again,
> Tamás
>
> ___
> 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

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

Richard Hipp  writes:

> 
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> 
> >
> > The following query reports 18900080 rows (after some computation time):
> >
> 
> Is this the correct answer for the query below?

Yes, it seems to be correct.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

Richard Hipp  writes:

> 
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
> 
> >
> > I’ve encountered a problem, which is hardly reproducable on arbitrary
> > databases, therefore I attached one.
> >
> 
> A simple, reproducible test case for (what we think is) your problem can be
> seen in this ticket:
> 
>  www.sqlite.org/src/tktview/fc7bd6358f59b
> 
> This bug has been latent in SQLite for almost four years and you are the
> first to hit it.  Probably this is because not many applications contain
> A=B in the WHERE clause where A is a text expression and B is an integer
> expression.  You can probably work around the problem by changing your
> schema so that entries.measurementid is an integer rather than text.  This
> does not excuse SQLite:  It is still getting the wrong answer and needs to
> be fixed.  We are working on a fix now.  But a simple change to your schema
> will work around the problem and get you going even before that fix is
> available.

Very good example, thank you! It really solved my problem. Nevertheless, I think
I don't have to mention that entries.measurementid must have been an integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an error (or at
least a warning) in cases where a foreign key constraint refers to a different
data type?

Thanks again,
Tamás

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Tue, Mar 5, 2013 at 2:29 PM, Richard Hipp  wrote:

>
>
> On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:
>
>>
>> I’ve encountered a problem, which is hardly reproducable on arbitrary
>> databases, therefore I attached one.
>>
>
> A simple, reproducible test case for (what we think is) your problem can
> be seen in this ticket:
>
>  www.sqlite.org/src/tktview/fc7bd6358f59b
>
> This bug has been latent in SQLite for almost four years and you are the
> first to hit it.  Probably this is because not many applications contain
> A=B in the WHERE clause where A is a text expression and B is an integer
> expression.  You can probably work around the problem by changing your
> schema so that entries.measurementid is an integer rather than text.  This
> does not excuse SQLite:  It is still getting the wrong answer and needs to
> be fixed.  We are working on a fix now.  But a simple change to your schema
> will work around the problem and get you going even before that fix is
> available.
>


This problem has now been fixed on trunk (see
http://www.sqlite.org/src/info/7097241c12 for details).  There is also an
amalgamation including the fix at the
http://www.sqlite.org/draft/download.html draft download page.

The release of version 3.7.16 will likely be delayed for a few days to give
folks a better changes to test this fix.  The version 3.7.16 status board
at http://www.sqlite.org/checklists/3071600 which was formerly showing a
lot of green has been reset as we intend to rerun all tests.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:

>
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
>

A simple, reproducible test case for (what we think is) your problem can be
seen in this ticket:

 www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you are the
first to hit it.  Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.  This
does not excuse SQLite:  It is still getting the wrong answer and needs to
be fixed.  We are working on a fix now.  But a simple change to your schema
will work around the problem and get you going even before that fix is
available.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:

>
> The following query reports 18900080 rows (after some computation time):
>

Is this the correct answer for the query below?


>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> Can you send the database directly to me at drh@... please?

It's already sent, and is also available in this shared folder:
https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> First of all attachments are stripped out from this list, so nobody
> saw your database.

Sorry for that. Now I created a shared folder so everyone has access and can
take a look at it:
https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing

It contains the database and the full schema as sql script and as a png image
for easier overview.


> And second your above results can be not definitive if different
> datatypes and affinities come into play. So to check things out you
> should add la2.id to the above SELECT field list. If it is NULL then
> results of query with inner join are correct.

I tried your suggestion, but it gave the result I expected (i.e. correct
behavior): adding "la2.id" to the column list returned the row "47, 47", so
nothing is NULL (therefore I think they should be "innerjoinable").


> And to check why they
> are not what you expect you can do the following queries:
> 
> SELECT id, typeof(id) FROM labels WHERE id = 47;
> SELECT labelid, typeof(labelid) FROM interval2label WHERE labelid = 47;

The first query returns the row "47, integer" (as expected), the second query
returns the same row multiple times (as expected). ("labels" and
"entryintervals" are in many-to-many connection through "interval2label".)

So these debug queries return proper results, but the original problem still
exists.


> And you could show us schema of these two tables so that we could
> explain the results to you.

Here is the schema for the two requested tables (and you can find the whole
schema in the shared folder):

CREATE TABLE interval2label
  (id   INTEGER PRIMARY KEY AUTOINCREMENT,
   labelid  INTEGER,
   entry_intervalid INTEGER,

   FOREIGN KEY(labelid)REFERENCES labels(id),
   FOREIGN KEY(entry_intervalid) REFERENCES entryintervals(id),

   UNIQUE(entry_intervalid, labelid));

CREATE TABLE labels
  (id  INTEGER PRIMARY KEY AUTOINCREMENT,
   nameTEXT,
   groupid INTEGER,

   UNIQUE(name, groupid)
   FOREIGN KEY(groupid) REFERENCES labelgroups(id));


Thanks for all your hints so far! Hope my answer will help finding the cause...

Tamás

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Tom Matrix]

> Could it be some of the features that SQLite doesn't support?
> 
> http://www.sqlite.org/omitted.html
> 
> /Patrik

No, I think this problem is independent from those thing. Only basic SQL
features are used.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Richard Hipp]

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:

> Hello,
>
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
>

Can you send the database directly to me at d...@sqlite.org please?



> The problem is that inner join omits the result on large datasets. But
> again, ’large’ does not necessarly refer simply the amount of data; I
> couldn’t reproduce it on different (arbitrary) databases with larger
> datasets, but I could on another database with the same structure.
>
> To be more specific:
>
> The following query reports 18900080 rows (after some computation time):
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
>
> However, the following one reports 0 rows immediately:
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> JOIN labels la2 ON il2.labelid = la2.id
>
> This is not what I expected. So I checked, that for example
>
> SELECT il2.labelid
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> LIMIT 1
>
> returns the id 47. The empty set, returned by the query containing
> inner joins only, implies that no row exists with id 47 in the
> ’labels’ table. This is, however, not true:
>
> SELECT * FROM labels WHERE id = 47
>
> returns the appropriate row...
>
> I’m using the latest (SQLite version 3.7.15.2 2013-01-09 11:53:05)
> version on Windows.
> Previously, I used SQLite version 3.7.13 2012-06-11 02:05:22 with
> Cygwin, which gave similar result, except that it was enough to have
> less joins:
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> [LEFT] JOIN entryintervals ei2 ON en2.id = ei2.entryid
>
> Have you ever experienced such behavior?
> Please take a look at it, as it might be a rare but serious low-level
> problem.
>
> Thanks,
> Tamás
>
> ___
> 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

Re: [sqlite] inner vs. outer join inconsistency

[Pavel Ivanov]

On Sun, Mar 3, 2013 at 11:48 AM, Tom Matrix  wrote:
> Hello,
>
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
> The problem is that inner join omits the result on large datasets. But
> again, ’large’ does not necessarly refer simply the amount of data; I
> couldn’t reproduce it on different (arbitrary) databases with larger
> datasets, but I could on another database with the same structure.
>
> To be more specific:
>
> The following query reports 18900080 rows (after some computation time):
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
>
> However, the following one reports 0 rows immediately:
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> JOIN labels la2 ON il2.labelid = la2.id
>
> This is not what I expected. So I checked, that for example
>
> SELECT il2.labelid
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> LIMIT 1
>
> returns the id 47. The empty set, returned by the query containing
> inner joins only, implies that no row exists with id 47 in the
> ’labels’ table. This is, however, not true:
>
> SELECT * FROM labels WHERE id = 47
>
> returns the appropriate row...

First of all attachments are stripped out from this list, so nobody
saw your database.
And second your above results can be not definitive if different
datatypes and affinities come into play. So to check things out you
should add la2.id to the above SELECT field list. If it is NULL then
results of query with inner join are correct. And to check why they
are not what you expect you can do the following queries:

SELECT id, typeof(id) FROM labels WHERE id = 47;
SELECT labelid, typeof(labelid) FROM interval2label WHERE labelid = 47;

And you could show us schema of these two tables so that we could
explain the results to you.


Pavel

> I’m using the latest (SQLite version 3.7.15.2 2013-01-09 11:53:05)
> version on Windows.
> Previously, I used SQLite version 3.7.13 2012-06-11 02:05:22 with
> Cygwin, which gave similar result, except that it was enough to have
> less joins:
>
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> [LEFT] JOIN entryintervals ei2 ON en2.id = ei2.entryid
>
> Have you ever experienced such behavior?
> Please take a look at it, as it might be a rare but serious low-level
> problem.
>
> Thanks,
> Tamás
>
> ___
> 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

Re: [sqlite] inner vs. outer join inconsistency

[Patrik Nilsson]

Could it be some of the features that SQLite doesn't support?

http://www.sqlite.org/omitted.html

/Patrik

On 03/03/2013 08:48 PM, Tom Matrix wrote:
> Hello,
> 
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
> The problem is that inner join omits the result on large datasets. But
> again, ’large’ does not necessarly refer simply the amount of data; I
> couldn’t reproduce it on different (arbitrary) databases with larger
> datasets, but I could on another database with the same structure.
> 
> To be more specific:
> 
> The following query reports 18900080 rows (after some computation time):
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> 
> However, the following one reports 0 rows immediately:
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> JOIN labels la2 ON il2.labelid = la2.id
> 
> This is not what I expected. So I checked, that for example
> 
> SELECT il2.labelid
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> LIMIT 1
> 
> returns the id 47. The empty set, returned by the query containing
> inner joins only, implies that no row exists with id 47 in the
> ’labels’ table. This is, however, not true:
> 
> SELECT * FROM labels WHERE id = 47
> 
> returns the appropriate row...
> 
> I’m using the latest (SQLite version 3.7.15.2 2013-01-09 11:53:05)
> version on Windows.
> Previously, I used SQLite version 3.7.13 2012-06-11 02:05:22 with
> Cygwin, which gave similar result, except that it was enough to have
> less joins:
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> [LEFT] JOIN entryintervals ei2 ON en2.id = ei2.entryid
> 
> Have you ever experienced such behavior?
> Please take a look at it, as it might be a rare but serious low-level
> problem.
> 
> Thanks,
> Tamás
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] inner vs. outer join inconsistency

[Tom Matrix]

Hello,

I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.
The problem is that inner join omits the result on large datasets. But
again, ’large’ does not necessarly refer simply the amount of data; I
couldn’t reproduce it on different (arbitrary) databases with larger
datasets, but I could on another database with the same structure.

To be more specific:

The following query reports 18900080 rows (after some computation time):

SELECT COUNT(*)
FROM entryintervals ei1
JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
JOIN labels la1 ON il1.labelid = la1.id
JOIN labelgroups lg1ON la1.groupid = lg1.id
JOIN entries en1ON ei1.entryid = en1.id
JOIN modules mo1ON en1.moduleid = mo1.id
JOIN measurements me1   ON en1.measurementid = me1.id
JOIN entries en2ON en1.measurementid = en2.measurementid
JOIN modules mo2ON en2.moduleid = mo2.id
JOIN entryintervals ei2 ON en2.id = ei2.entryid
JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
LEFT JOIN labels la2ON il2.labelid = la2.id

However, the following one reports 0 rows immediately:

SELECT COUNT(*)
FROM entryintervals ei1
JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
JOIN labels la1 ON il1.labelid = la1.id
JOIN labelgroups lg1ON la1.groupid = lg1.id
JOIN entries en1ON ei1.entryid = en1.id
JOIN modules mo1ON en1.moduleid = mo1.id
JOIN measurements me1   ON en1.measurementid = me1.id
JOIN entries en2ON en1.measurementid = en2.measurementid
JOIN modules mo2ON en2.moduleid = mo2.id
JOIN entryintervals ei2 ON en2.id = ei2.entryid
JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
JOIN labels la2 ON il2.labelid = la2.id

This is not what I expected. So I checked, that for example

SELECT il2.labelid
FROM entryintervals ei1
JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
JOIN labels la1 ON il1.labelid = la1.id
JOIN labelgroups lg1ON la1.groupid = lg1.id
JOIN entries en1ON ei1.entryid = en1.id
JOIN modules mo1ON en1.moduleid = mo1.id
JOIN measurements me1   ON en1.measurementid = me1.id
JOIN entries en2ON en1.measurementid = en2.measurementid
JOIN modules mo2ON en2.moduleid = mo2.id
JOIN entryintervals ei2 ON en2.id = ei2.entryid
JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
LEFT JOIN labels la2ON il2.labelid = la2.id
LIMIT 1

returns the id 47. The empty set, returned by the query containing
inner joins only, implies that no row exists with id 47 in the
’labels’ table. This is, however, not true:

SELECT * FROM labels WHERE id = 47

returns the appropriate row...

I’m using the latest (SQLite version 3.7.15.2 2013-01-09 11:53:05)
version on Windows.
Previously, I used SQLite version 3.7.13 2012-06-11 02:05:22 with
Cygwin, which gave similar result, except that it was enough to have
less joins:

SELECT COUNT(*)
FROM entryintervals ei1
JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
JOIN labels la1 ON il1.labelid = la1.id
JOIN labelgroups lg1ON la1.groupid = lg1.id
JOIN entries en1ON ei1.entryid = en1.id
JOIN modules mo1ON en1.moduleid = mo1.id
JOIN measurements me1   ON en1.measurementid = me1.id
JOIN entries en2ON en1.measurementid = en2.measurementid
JOIN modules mo2ON en2.moduleid = mo2.id
[LEFT] JOIN entryintervals ei2 ON en2.id = ei2.entryid

Have you ever experienced such behavior?
Please take a look at it, as it might be a rare but serious low-level
problem.

Thanks,
Tamás
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users