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.
