Details
Major
Description
Hello all.
I've got a strange behavior of the queries with outer right join.
First let's create some tables, fill them with data and create a function.
CREATE TABLE `test1` ( `id` INT NOT NULL, PRIMARY KEY (`id`)); insert into test1 values(1); insert into test1 values(2); insert into test1 values(3); insert into test1 values(9);
CREATE TABLE `test2` ( `id` INT NOT NULL, PRIMARY KEY (`id`)); insert into test2 values(1); insert into test2 values(7); insert into test2 values(8); insert into test2 values(9);
CREATE TABLE `test3` ( `id` INT NOT NULL, PRIMARY KEY (`id`)); insert into test3 values(1); insert into test3 values(2);
CREATE TABLE `test4` ( `id` INT NOT NULL, PRIMARY KEY (`id`)); insert into test4 values(2); insert into test4 values(88);
DELIMITER $$ CREATE FUNCTION `foo` (var1 int ) RETURNS INTEGER BEGIN RETURN coalesce((select min(id) from test4 where id = var1), 1); END$$ DELIMITER ;
Now let's try the query:
select test1.*, test2.*, test3.* from test1 right join test2 ON test1.id = test2.id right join test3 ON test3.id = foo(test1.id);
What do I get:
'1', '1', '1' '9', '9', '1' NULL, NULL, '2'
What do I expect:
1, 1, 1 Null, 7, 1 Null, 8, 1 9, 9, 1 Null, Null, 2
Now look at this query:
select test1.*, test2.*, test3.*, foo(coalesce(test1.id)), foo(test1.id) from test1 right join test2 ON test1.id = test2.id right join test3 ON test3.id = foo(coalesce(test1.id));
What do I expect (without 2 last columns):
1, 1, 1 Null, 7, 1 Null, 8, 1 9, 9, 1 Null, Null, 2
I think that "test1.id" and "coalesce(test1.id)" are similar, yes? This means that both of queries must return a similar resultset. But the second query returns exactly what I expect.
I think the reason is in query optimizer. It converts outer join to inner join because of using "test1.id" in ON clause. In this case it is not a correct behavior. It does not assume that the function used in this expression may work with nulls well.
But coalesce (and case expression also) hints to optimizer dont do this conversion.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
in SP select list turned some how to this
(gdb) p dbug_print_item(val_item)
$3 = 0x1569cc0 <dbug_item_print_buf> "min(NULL)"
(gdb)
was:
(gdb) p dbug_print_item(val_item)
$2 = 0x1569cc0 <dbug_item_print_buf> "min(`test`.`t4`.`id`)"
probably equality this equality worked in such strange way somehow:
WHERE:(after remove) 0x7fffe00369d8 multiple equal(var1@0, `test`.`t4`.`id`)
This looks suspicious taking into account that t4 is not constant:
T@5 : | | | | | | | | | | | | | | | | | | | | | error: Error: no matching row in const table
T@5 : | | | | | | | | | | | | | | | | | | | | <JOIN::optimize
"correct" execution somehow avoid make_join_statistics() where one table referenced by a key become "const table" (it looks like quite correct, incorrect is the variable (key) value).
opt_sum_query() is for blame
In first statement (3rd call in it) we somehow do not have NULL values for the key lookup
Hi Konstantin,
Thank you for the bug report.
Reproducible on all of MySQL 5.1 - MySQL 5.7, MariaDB 5.1 - MariaDB 10.0 that I tried.
Does not depend on the optimizer_switch.
PostgreSQL returns identical results for both queries.