Can you send me the database that demonstrates this problem, via private email, please?
On Wed, Mar 2, 2011 at 3:53 AM, alexis bialot <abialot.ol...@gmail.com>wrote: > hello, > > this looks to me like a bug but maybe this is just my misunderstanding. > > the databases shema is the trac database schema visible here > http://trac.edgewall.org/wiki/TracDev/DatabaseSchema > but i do not believe the schema is really relevant to the point > > the problematic clause is "and pmilestone.name not like '%release%'" > > this first query behaves as expected > > sqlite> select > ...> pmilestone.name, > ...> typeof(pmilestone.name), > ...> pmilestone.name not like '%release%', > ...> typeof(pmilestone.name not like '%release%'), > ...> ticket.type > ...> from milestone as pmilestone > ...> inner 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%' > ...> group by pmilestone.name > ...> ; > CRM|text|1|integer|Blanc - Évolution > FAI|text|1|integer|Blanc - Évolution > iter60|text|1|integer|Blanc - Évolution > iter61|text|1|integer|Blanc - Évolution > iter62|text|1|integer|Blanc - Évolution > > but when the join becomes a left outer, some results are missing > > 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%' > ...> group by pmilestone.name; > CRM|text|1|integer|Blanc - Évolution > FAI|text|1|integer|Blanc - Évolution > iter60|text|1|integer|Blanc - Évolution > iter62|text|1|integer|Blanc - Évolution > > 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. > > possibly this would be expected from a having clause on the table right > to the join but that would be asking for trouble > > is this really a bug ? > > thanks > > > > _______________________________________________ > 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
