Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL)
-
None
Description
- When evaluating outer query with `on` and `using` keyword on the same column results are the same with right/left joins.
- However with full outer aka left union right joins the results are not the same. More specifically, query with using is creating additional rows that IMHO should not exist or should have valid order.
See the test case (error is at the end in comment):create table vips(id1 int, name varchar(10));
create table guests(id2 int, name varchar(10));
insert into guests values (1,"p2"),(2,"p1"),(3,"vip1"),(4,"vip2"),(5,"vip3");
insert into vips values (1,"vip1"),(2,"vip2");
# This is correct with `on` key word
MariaDB [test]> select * from guests g left join vips v on v.name=g.name union select * from guests g right join vips v on v.name=g.name;
+------+------+------+------+
| id2 | name | id1 | name |
+------+------+------+------+
| 3 | vip1 | 1 | vip1 || 4 | vip2 | 2 | vip2 || 1 | p2 | NULL | NULL |
| 2 | p1 | NULL | NULL |
| 5 | vip3 | NULL | NULL |
+------+------+------+------+
5 rows in set (0.000 sec)
Â
# This is not correct using `using` keyword :
MariaDB [test]> select * from guests g left join vips v using(name) union select * from guests g right join vips v using(name);
+------+------+------+
| name | id2 | id1 |
+------+------+------+
| vip1 | 3 | 1 || vip2 | 4 | 2 || p2 | 1 | NULL |
| p1 | 2 | NULL |
| vip3 | 5 | NULL |
| vip1 | 1 | 3 | # this is the right join row and should be id2=3, id1=1 or row should be removed
| vip2 | 2 | 4 | # this is the right join row and should be id2=4, id1=3 or row should be removed
+------+------+------+
7 rows in set (0.001 sec)
- Looking into Explain analyse seems that there are 7 rows in the first phase generated with using keyword:
Table scan on <union temporary> (cost=0.14..2.75 rows=20) (actual time=0.074..0.076 rows=7 loops=1),
where for on keyword there are 5 rows:Table scan on <union temporary> (cost=0.14..2.75 rows=20) (actual time=0.091..0.092 rows=5 loops=1)`
- The same happens in MySQL - example