Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36883

Oracle outer join syntax (+): operator (+) is not processed in condition like "(t2.b(+) , t1.b) in (select ...)"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 12.1
    • 12.1.1
    • OTHER
    • 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

          Activity

            People

              sanja Oleksandr Byelkin
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.