From 6311e48c8b60b091c9030612e73ae2d42ec3da9b Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin <sanja@mariadb.com> Date: Mon, 16 Jun 2025 19:05:04 +0200 Subject: [PATCH] MDEV-36883 Oracle outer join syntax (+): operator (+) is not processed in condition like "(t2.b(+) , t1.b) in (select ...)"

Fixed transfer of ora join flag from left expression to whole query (fix for all flags and versions?)

Prohibit (+) in row operations.

What is a row operation? I can infer some rules from this patch and some related code, where the following is not allowed: 1. An Item_row has a (+) somewhere 2. The LHS of an IN function or subquery is a tuple of at least two items and has a (+) somewhere 3. The RHS of an IN function has a (+) somewhere Note that Items 1 and 2 above are introduced in this patch, and Item 3 was already there. It is hard to tell from these rules what a row operation is. For example, "t1.c1 IN (t2.c2(+), t2.c3(+))" is banned because of Item 3 above, but Item 1 causes "(t2.a, t2.b) = (t1.c(+), t1.b(+))" to be banned. Does that mean that the latter is a row operation but not the former? As another example, "t2.a(+) IN (t1.c, t1.b)" is allowed so it can't be a row operation, but Item 1 bans "(t2.a(+), t2.b(+)) = (t1.c, t1.b)". If there is no simple definition of row operations, can you list the actual Rules 1 and 2 in the commit message? Items 1-3 above is also not the complete set of rules of all disallowed cases. And the division between what is allowed and what is not allowed is getting quite complex (another example: "t1.c1(+) IN (t2.c2, t2.c2)" is allowed but "t1.c1 IN (t2.c2(+), t2.c2(+))" is not). I suppose the complete set of rules will be in the docs when the feature is released. But it would be nice to document them in the code as well, especially that the beginning of sql_oracle_outer_join.cc already mentions some forbidden cases such as cycles, so it would be natural for the rest of the rules to go there.

--- .../compat/oracle/r/ora_outer_join.result | 4 +-- .../compat/oracle/r/ora_outer_join_err.result | 22 ++++++++++++++ .../compat/oracle/t/ora_outer_join_err.test | 29 +++++++++++++++++++ sql/item_cmpfunc.cc | 6 ++++ sql/item_row.h | 11 +++++++ sql/item_subselect.cc | 4 ++- sql/item_subselect.h | 11 +++++++ sql/share/errmsg-utf8.txt | 2 +- 8 files changed, 85 insertions(+), 4 deletions(-)

diff --git a/mysql-test/suite/compat/oracle/r/ora_outer_join.result b/mysql-test/suite/compat/oracle/r/ora_outer_join.result index ac0a09d5c43..1b99e1b3014 100644 --- a/mysql-test/suite/compat/oracle/r/ora_outer_join.result +++ b/mysql-test/suite/compat/oracle/r/ora_outer_join.result @@ -294,9 +294,9 @@ Warning 4232 Oracle outer join operator (+) ignored in '"test"."tj1"."a" = 1' # ORA-01719 # SELECT * FROM tj1, tj2 WHERE tj1.a IN (tj2.c(+), tj2.d(+)); -ERROR HY000: Invalid usage of (+) operator: used in OR or IN +ERROR HY000: Invalid usage of (+) operator: used in OR, IN or ROW operation SELECT * FROM tj1, tj2 WHERE tj1.a NOT IN (tj2.c(+), tj2.d(+)); -ERROR HY000: Invalid usage of (+) operator: used in OR or IN +ERROR HY000: Invalid usage of (+) operator: used in OR, IN or ROW operation # # Outer join in 'IN' condition with a single expression # This is also allowed in oracle since the expression is diff --git a/mysql-test/suite/compat/oracle/r/ora_outer_join_err.result b/mysql-test/suite/compat/oracle/r/ora_outer_join_err.result index 59ea247ccb3..f54ee14bbf4 100644 --- a/mysql-test/suite/compat/oracle/r/ora_outer_join_err.result +++ b/mysql-test/suite/compat/oracle/r/ora_outer_join_err.result @@ -83,3 +83,25 @@ select u2.a, (select t1.a, t2.a from t1,t2 where u1.a(+) = t2.a) from t1 as u1, t2 as u2 where u1.a(+) = u2.a; ERROR HY000: Invalid usage of (+) operator with outer reference a DROP TABLE t1,t2; +# +# MDEV-36883: Oracle outer join syntax (+): operator (+) is not +# processed in condition like "(t2.b(+) , t1.b) in (select ...)" +# +create table t1 ( c int, b char(1)); +insert into t1 values (1,'b'); +create table t2 ( 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'); +SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (SELECT * from t3); +ERROR HY000: Invalid usage of (+) operator: used in OR, IN or ROW operation

Question: I made a little modification to this query: SELECT t2.b FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t2.b) IN (SELECT c1, c1 from t3); Instead of ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC, I got ER_INVALID_USE_OF_ORA_JOIN_CYCLE. Does it make sense to count this as a cycle?

+SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (('a','a'),('b','b')); +ERROR HY000: Invalid usage of (+) operator: used in OR, IN or ROW operation +SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) = (SELECT * from t3 +ORDER BY a LIMIT 1); +ERROR HY000: Invalid usage of (+) operator: used in OR, IN or ROW operation +drop tables t1,t2,t3; diff --git a/mysql-test/suite/compat/oracle/t/ora_outer_join_err.test b/mysql-test/suite/compat/oracle/t/ora_outer_join_err.test index 22bb8514b79..94faa293323 100644 --- a/mysql-test/suite/compat/oracle/t/ora_outer_join_err.test +++ b/mysql-test/suite/compat/oracle/t/ora_outer_join_err.test @@ -107,3 +107,32 @@ select u2.a, (select t1.a, t2.a from t1,t2 where u1.a(+) = t2.a) from t1 as u1, t2 as u2 where u1.a(+) = u2.a;

DROP TABLE t1,t2; + +--echo # +--echo # MDEV-36883: Oracle outer join syntax (+): operator (+) is not +--echo # processed in condition like "(t2.b(+) , t1.b) in (select ...)" +--echo # +create table t1 ( c int, b char(1)); +insert into t1 values (1,'b'); + +create table t2 ( 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'); + +--error ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC +SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (SELECT * from t3); + +--error ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC +SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) IN (('a','a'),('b','b')); + +--error ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC +SELECT t2.b +FROM t1, t2 WHERE t1.c = t2.a(+) AND (t2.b(+), t1.b) = (SELECT * from t3 +ORDER BY a LIMIT 1); + +drop tables t1,t2,t3; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index ca03b13f724..c94e185aa33 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5037,6 +5037,12 @@ bool Item_func_in::ora_join_processor(void *arg) { if (with_ora_join()) { + if (cols() > 1 && args[0]->with_ora_join())

rr is telling me that cols() always returns 1 here (see below), so this if condition will always be false (a run of compat/oracle.ora_outer_join_err also does go into this if branch). Do you mean args[0]->cols()? If so, then testcases such as "(t2.b(+), t1.b) IN (('a','a'),('b','b'))" already works because the Item_row method is already returning error. But I suppose there's no harm doing another check here. (rr) list Item_func_in::cols 2523 current value and pointer passed via parameter otherwise. 2524 */ 2525 virtual Item **this_item_addr(THD *thd, Item **addr_arg) { return addr_arg; } 2526 2527 // Row emulation 2528 virtual uint cols() const { return 1; } 2529 virtual Item* element_index(uint i) { return this; } 2530 virtual Item** addr(uint i) { return 0; } 2531 virtual bool check_cols(uint c); 2532 bool check_type_traditional_scalar(const LEX_CSTRING &opname) const;

+ { + // used in ROW operaton + my_error(ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC, MYF(0)); + return TRUE; + } uint n= argument_count(); DBUG_ASSERT(n >= 2); // first argument (0) is right part of IN where oracle joins are allowed

diff --git a/sql/item_row.h b/sql/item_row.h index 2c884c0f858..b234a0499c5 100644 --- a/sql/item_row.h +++ b/sql/item_row.h @@ -152,6 +152,17 @@ class Item_row: public Item_fixed_hybrid, Item *do_get_copy(THD *thd) const override { return get_item_copy<Item_row>(thd, this); } Item *do_build_clone(THD *thd) const override; + + bool ora_join_processor(void *arg) override + { + if (with_ora_join()) + { + // Oracle join operator is used inside rows. + my_error(ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC, MYF(0)); + return(TRUE); + } + return (FALSE); + } };

Just a note: this part does not apply to "t1.c1 IN (t2.c2(+), t2.c3(+))" because no Item_row is involved here even though the rhs looks exactly the same as an Item_row. The Item_func_in has three args. This is banned is because anything on the RHS of Item_func_in with a (+) is banned.

#endif /* ITEM_ROW_INCLUDED */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 57a1f01294c..e14f1a01763 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3614,11 +3614,13 @@ bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref) } }

- if (left_expr && left_expr->fix_fields_if_needed(thd_arg, &left_expr)) + if (!left_expr || left_expr->fix_fields_if_needed(thd_arg, &left_expr)) goto err;

What is this change for? The test in the patch compat/oracle.ora_outer_join_err do not reach inside the if branch. Neither does the other test compat/oracle.ora_outer_join

else if (Item_subselect::fix_fields(thd_arg, ref)) goto err; + if (left_expr->with_ora_join()) + copy_flags(left_expr, item_with_t::ORA_JOIN);

What are the circumstances when this is needed? All statements in compat/oracle.ora_outer_join_err that reach the copy_flags call here have left_expr already having the ORA_JOIN flag. The other test compat/oracle.ora_outer_join does not reach here.

base_flags|= item_base_t::FIXED; thd->where= save_where; DBUG_RETURN(FALSE); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index f75fafc4812..cb58c62196c 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -790,6 +790,17 @@ class Item_in_subselect :public Item_exists_subselect Subq_materialization_tracker *get_materialization_tracker() const { return materialization_tracker; } + bool ora_join_processor(void *arg) override + { + if (left_expr->with_ora_join() && left_expr->cols() > 1) + { + // used in ROW operaton + my_error(ER_INVALID_USE_OF_ORA_JOIN_WRONG_FUNC, MYF(0)); + return TRUE; + } + return FALSE; + } +

This if branch is not entered in the test, likely because the Item_row method already returns error before the walk calls the method on the Item_in_subselect. Is it possible to add a test that covers this?

[... 19 lines elided]

Best, Yuchen