Alvaro Herrera <alvherre@commandprompt.com> writes:
> Javier Hernandez wrote:
>> select * from numeric_col_table where num_col in (select int_col from
>> int_col_table);
> So what's happening here is that it's writing the hash table using the
> wrong datatype ...
Yeah, the planner is at fault here --- it should be coercing the value
to numeric before hashing. I think this is wrong all the way back,
but pre-8.3 you'd have silently gotten wrong answers instead of a crash,
because the executor made up its own mind about how to unique-ify the
subquery outputs, and it looked directly at their actual data type
and chose some default equality operator for that. This can be
demonstrated to be the Wrong Thing when the conversion to the IN
operator's datatype is lossy, as in this variant example:
create table numeric_col_table (
num_col numeric
);
create table float_col_table (
float_col float8
);
insert into numeric_col_table values (1), (1.000000000000000000001), (2), (3);
insert into float_col_table values (1), (2), (3);
select * from numeric_col_table;
select * from float_col_table;
select * from float_col_table where float_col in (select num_col from
numeric_col_table);
In 8.2 I get bogus results like
float_col
-----------
1
1
2
3
(4 rows)
because 1 and 1.000000000000000000001 are perfectly distinct numeric
values, but not so much after they've been coerced to float.
In 8.3/HEAD I think this can be fixed by coercing the Vars that are put
into the InClauseInfo entry for the IN join. Not sure how far back it
will be practical to apply that fix, though.
regards, tom lane
