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

Oracle outer join syntax (+): outer join not converted to inner

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 12.1
    • 12.1.1
    • Optimizer
    • None
    • Q2/2025 Development

    Description

      It is expected that the query plan for left join will be the same for normal syntax and for oracle syntax. For the example below, this is not the case.

      Testcase:
      Tables:

      CREATE TABLE t1 (
        siteid varchar(25) NOT NULL default '',
        emp_id varchar(30) NOT NULL default '',
        rate_code varchar(10) default NULL,
        UNIQUE KEY site_emp (siteid,emp_id),
        KEY siteid (siteid)
      ) ENGINE=MyISAM;
       
      INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
       
      CREATE TABLE t2 (
        siteid varchar(25) NOT NULL default '',
        rate_code varchar(10) NOT NULL default '',
        base_rate float NOT NULL default '0',
        PRIMARY KEY  (siteid,rate_code),
        FULLTEXT KEY rate_code (rate_code)
      ) ENGINE=MyISAM;
       
      INSERT INTO t2 VALUES ('rivercats','cust',20);
      

      left join:

      SELECT emp.rate_code, lr.base_rate FROM t1 AS emp 
      LEFT JOIN t2 AS lr ON lr.siteid = emp.siteid WHERE lr.rate_code = emp.rate_code 
      and emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
      

      And its query plan (expected):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	lr	system	PRIMARY,rate_code	NULL	NULL	NULL	1	100.00	
      1	SIMPLE	emp	const	site_emp,siteid	site_emp	224	const,const	1	100.00	
      Warnings:
      Note	1003	select 'cust' AS "rate_code",20 AS "base_rate" from "test"."t1" "emp" where 1
      

      Oracle syntax with :

      SELECT emp.rate_code, lr.base_rate FROM t1 AS emp, t2 AS lr
      WHERE emp.siteid=lr.siteid(+) AND   lr.rate_code = emp.rate_code AND
      emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
      

      Query plan (actual result):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	emp	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	SIMPLE	lr	ref	PRIMARY	PRIMARY	102	test.emp.siteid	1	100.00	Using where
      Warnings:
      Note	1003	select "test"."emp"."rate_code" AS "rate_code","test"."lr"."base_rate" AS "base_rate" from "test"."t1" "emp" left join "test"."t2" "lr" on("test"."lr"."siteid" = "test"."emp"."siteid") where "test"."lr"."rate_code" = "test"."emp"."rate_code" and "test"."emp"."emp_id" = 'psmith' and "test"."lr"."siteid" = 'rivercats'
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              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.