Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
12.1
-
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
- is caused by
-
MDEV-13817 add support for oracle outer join syntax - the ( + )
-
- Closed
-