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