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
