hello thanks for your quick reply > On 2 Mar 2011, at 8:53am, alexis bialot wrote: > >> i do not believe it should be possible that a query with a left join >> yields less results >> than the exact same query with an inner join. > Do you have any null fields anywhere in either TABLE ? Nulls make analysis > of what's happening very difficult because semantically they mean something > like "don't know". This not only makes the rows returned from a JOIN tricky, > but also influences functions like max() which I see you used. there are plenty of nulls, but none in the left-hand side table, and (subsequently) none in the problematic where clause which happens to be a "like" clause on the field that is used for the join (left hand side). > Apart from that, a LEFT OUTER should always return at least every record in > the main TABLE. If it doesn't, there's something wrong. It might return > additional rows too. Sorry, but your example is too complicated to tell me > whether it's working right for you. i tried to trim the query a bit more, but the bug only becomes reproductible with real-world examples. btw the original query was much worse, and the current one even uglier after the workarounds i had to add because of this bug
here is an easier to catch example, i guess select ...> pmilestone.name, ...> typeof(pmilestone.name), ...> pmilestone.name not like '%release%', ...> typeof(pmilestone.name not like '%release%'), ...> ticket.type ...> from milestone as pmilestone ...> left outer join ticket on pmilestone.name=milestone ...> where ...> due > (select max(due) from milestone where name like 'Release%' and completed) ...> and pmilestone.name not like '%release%' ...> group by pmilestone.name; CRM|text|1|integer|Blanc - Évolution FAI|text|1|integer|Blanc - Évolution iter60 (31/01 -> 11/02/2011)|text|1|integer|Rouge - Bug iter61 (14/02 -> 25/02/2011)|text|1|integer|Blanc - Évolution iter62 (28/02 -> 11/03/2011)|text|1|integer|Rouge - Bug iter63 (14/03 -> 25/03/2011)|text|1|integer| iter64|text|1|integer| iter65|text|1|integer| iter66|text|1|integer| i just add an extra where clause sqlite> select ...> pmilestone.name, ...> typeof(pmilestone.name), ...> pmilestone.name not like '%release%', ...> typeof(pmilestone.name not like '%release%'), ...> ticket.type ...> from milestone as pmilestone ...> left outer join ticket on pmilestone.name=milestone ...> where ...> due > (select max(due) from milestone where name like 'Release%' and completed) ...> and pmilestone.name not like '%release%' ...> and ticket.type like 'Blanc%' /* **** the extra where clause, rest of the query is identical **** */ ...> group by pmilestone.name; CRM|text|1|integer|Blanc - Évolution FAI|text|1|integer|Blanc - Évolution iter60 (31/01 -> 11/02/2011)|text|1|integer|Blanc - Évolution iter62 (28/02 -> 11/03/2011)|text|1|integer|Blanc - Évolution as expected, the lines for iter63 and up disapear : there are no corresponding lines on the right hand side but there is data for iter60 through 62 that do match the criteria i can only assume the group by clause is evaluated before the where clause which means the ticket.type field is populated randomly from the first or last examined line ... and the where clause is actually treated like a having, which in this case is meaningless thanks for your time > Simon. > _______________________________________________ > 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
