Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
12.1
-
None
-
Not for Release Notes
-
Q3/2025 Maintenance
Description
Operator (+) is not processed in condition like "(t2.b(+) , t1.b) in (select ...)":
|
no warnings about ignoring the operator, no conversion
|
Testcase:
create table t1 ( c int, b char(1)) ; |
insert into t1 values (1,'b'); |
|
create tablet2 ( a int , b char(1)) ; |
insert into t2 values (1,'a'); |
|
create table t3 (c1 char(1), c2 char(2)); |
insert into t3 values ('c','d'); |
insert into t3 values ('c','d'); |
|
EXPLAIN EXTEDED SELECT t2.b |
FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (SELECT * from t3); |
|
CREATE VIEW v1 AS |
SELECT t2.b |
FROM t1 , t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (SELECT * from t3); |
SHOW CREATE VIEW v1; |
|
drop view v1; |
drop tables t1,t2,t3; |
Actual result: no warnings about ignoring the operator
EXPLAIN EXTENDED SELECT t2.b |
FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+),t1.b) IN (SELECT * from t3); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 |
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 |
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t2) |
Warnings:
|
Note 1003 select 'a' AS "b" from ("test"."t3") where "test"."t3"."c1" = 'a' and 'b' = "test"."t3"."c2" |
Also testcase fails on "SHOW CREATE VIEW v1;" and there is no conversion of the operator ( + ) in v1.frm
TYPE=VIEW |
query=select `test`.`t2`.`b` AS `b` from (`test`.`t1` left join `test`.`t2` on(`test`.`t1`.`c` = `test`.`t2`.`a`)) where (`test`.`t2`.`b` (+),`test`.`t1`.`b`) in (select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3`) |
md5=4c521307514facea9e1188052ae7c5fa
|
updatable=0
|
algorithm=0
|
definer_user=root
|
definer_host=localhost
|
suid=2
|
with_check_option=0
|
timestamp=0001748341172721182 |
create-version=2 |
source=SELECT t2.b\nFROM t1 , t2 WHERE t1.c = t2.a(+) AND (t2.b(+),t1.b) IN (SELECT * from t3) |
client_cs_name=latin1
|
connection_cl_name=latin1_swedish_ci
|
view_body_utf8=select `test`.`t2`.`b` AS `b` from (`test`.`t1` left join `test`.`t2` on(`test`.`t1`.`c` = `test`.`t2`.`a`)) where (`test`.`t2`.`b` (+),`test`.`t1`.`b`) in (select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3`) |
mariadb-version=120001
|
Attachments
Issue Links
- is caused by
-
MDEV-13817 add support for oracle outer join syntax - the ( + )
-
- Closed
-