This bug is a beauty: text column comparison in joins may follow NUMERIC rules, even if the column(s) have text affinity!
consider this case. Tables A,B are a typical one-to-many relation, where B has N records for each A, and one field (key) relates them. CREATE TABLE a( key VARCHAR(2), data VARCHAR(30) ); -- this table contains N records for each A, with period being 1..N CREATE TABLE b( key VARCHAR(2), period INTEGER ); insert into a values('01','data01'); insert into a values('+1','data+1'); insert into b values ('01',1); insert into b values ('01',2); insert into b values ('+1',3); insert into b values ('+1',4); select a.*,a1.* from a, (select key,sum(period) from b group by key) as a1 where a.key=a1.key this select creates a temporary table because of the subselect, and then joins the two. the subselect, when run by itself, creates a table of two rows, as expected. thus, one would expect to get a result of two rows, like this: 01 data01 01 3 +1 data+1 +1 7 however, 4 rows are returned, like this : 01 data01 +1 7 01 data01 01 3 +1 data+1 +1 7 +1 data+1 01 3 the reason seems to be that '01' and '+1' are considered equal, something which would be correct IF column(s) 'key' were of numeric affinity, which they are NOT. However, if the subselect is saved in a table, and the join is done after, the correct result will be returned: create table x as select key,sum(period) from b group by key select a.*,x.* from a,x where a.key=x.key 01 data01 01 3 +1 data+1 +1 7 I suspect that this bug arises from the fact that the intermediate table that is created due to the subselect has no type information, for some reason. I have created ticket #1047 on this.