matrixorigin
[Bug]: related subquery which after select results error
Is there an existing issue for the same bug?
- [X] I have checked the existing issues.
Branch Name
main
Commit ID
12023e16cc66a531162ae2c41d49d12f98a84099
Other Environment Information
- Hardware parameters:
- OS type:
- Others:
Actual Behavior
select后关联子查询查询结果错误
sql：select ename, (select ename from emp i1 where i1.empno = emp.mgr order by 1 limit 1) as ab from emp order by ename;
mo结果：
mysql结果：
sql换成（子查询关联的表是不同的表）：select ename, (select dname from dept i1 where i1.deptno = emp.deptno order by 1 limit 1) as ab from emp order by ename;返回结果也是有误
mo explain: mysql> explain select ename, (select ename from emp i1 where i1.empno = emp.mgr order by 1 limit 1) as ab from emp order by ename; +--------------------------------------------------------------------+ | TP QUERY PLAN | +--------------------------------------------------------------------+ | Project | | -> Sort | | Sort Key: emp.ename INTERNAL | | -> Join | | Join Type: LEFT | | Join Cond: (emp.mgr = i1.empno) | | -> Table Scan on testdb.emp | | -> Sort | | Sort Key: i1.ename INTERNAL | | Limit: 1 | | Send Message: [tag 1 , type MsgTopValue] | | -> Table Scan on testdb.emp | | Sort Key: ename INTERNAL | | Recv Message: [tag 1 , type MsgTopValue] | +--------------------------------------------------------------------+ 14 rows in set (0.01 sec)
mysql explain: mysql> explain select ename, (select ename from emp i1 where i1.empno = emp.mgr order by 1 limit 1) as ab from emp order by ename; +----+--------------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------+ | 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using filesort | | 2 | DEPENDENT SUBQUERY | i1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.emp.mgr | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------+ 2 rows in set, 2 warnings (0.02 sec)
Expected Behavior
No response
Steps to Reproduce
ddl:
DROP TABLE IF EXISTS dept;
create table dept(
deptno int unsigned auto_increment COMMENT '部门编号',
dname varchar(15) COMMENT '部门名称',
loc varchar(50) COMMENT '部门所在位置',
primary key(deptno)
) COMMENT='部门表';
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
DROP TABLE IF EXISTS emp;
create table emp(
empno int unsigned auto_increment COMMENT '雇员编号',
ename varchar(15) COMMENT '雇员姓名',
job varchar(10) COMMENT '雇员职位',
mgr int unsigned COMMENT '雇员对应的领导的编号',
hiredate date COMMENT '雇员的雇佣日期',
sal decimal(7,2) COMMENT '雇员的基本工资',
comm decimal(7,2) COMMENT '奖金',
deptno int unsigned COMMENT '所在部门',
primary key(empno)
) COMMENT='雇员表';
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
query:
select ename, (select ename from emp i1 where i1.empno = emp.mgr order by 1 limit 1) as ab from emp order by ename;
### Additional information
_No response_
@aunjgr 1、如果2505无法解决，根据上周周会建议。先对这种语法报错，提示暂时不支持这种语法。 2、建议在comment中提供可供改写的SQL方案
