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
