> From 6311e48c8b60b091c9030612e73ae2d42ec3da9b Mon Sep 17 00:00:00 2001
> From: Oleksandr Byelkin <sa...@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
_______________________________________________
developers mailing list -- developers@lists.mariadb.org
To unsubscribe send an email to developers-le...@lists.mariadb.org
