On Sun, Mar 3, 2013 at 11:48 AM, Tom Matrix <ratomat...@gmail.com> 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 lg1 ON la1.groupid = lg1.id > JOIN entries en1 ON ei1.entryid = en1.id > JOIN modules mo1 ON en1.moduleid = mo1.id > JOIN measurements me1 ON en1.measurementid = me1.id > JOIN entries en2 ON en1.measurementid = en2.measurementid > JOIN modules mo2 ON en2.moduleid = mo2.id > JOIN entryintervals ei2 ON en2.id = ei2.entryid > JOIN interval2label il2 ON ei2.id = il2.entry_intervalid > LEFT JOIN labels la2 ON 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 lg1 ON la1.groupid = lg1.id > JOIN entries en1 ON ei1.entryid = en1.id > JOIN modules mo1 ON en1.moduleid = mo1.id > JOIN measurements me1 ON en1.measurementid = me1.id > JOIN entries en2 ON en1.measurementid = en2.measurementid > JOIN modules mo2 ON 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 lg1 ON la1.groupid = lg1.id > JOIN entries en1 ON ei1.entryid = en1.id > JOIN modules mo1 ON en1.moduleid = mo1.id > JOIN measurements me1 ON en1.measurementid = me1.id > JOIN entries en2 ON en1.measurementid = en2.measurementid > JOIN modules mo2 ON en2.moduleid = mo2.id > JOIN entryintervals ei2 ON en2.id = ei2.entryid > JOIN interval2label il2 ON ei2.id = il2.entry_intervalid > LEFT JOIN labels la2 ON 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 lg1 ON la1.groupid = lg1.id > JOIN entries en1 ON ei1.entryid = en1.id > JOIN modules mo1 ON en1.moduleid = mo1.id > JOIN measurements me1 ON en1.measurementid = me1.id > JOIN entries en2 ON en1.measurementid = en2.measurementid > JOIN modules mo2 ON 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
