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.