[MDEV-10232] Scalar result of subquery changes after adding an outer select stmt Created: 2016-06-14  Updated: 2017-01-20  Resolved: 2017-01-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.10, 10.0, 10.1, 10.2
Fix Version/s: 10.1.22, 10.2.4

Type: Bug Priority: Major
Reporter: Dylan Su Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 10.1.15, 10.1.21

 Description   

Output：
===

mysql> create table t1(c1 int, c2 int, primary key(c2));
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> insert into t1 values(2,1),(1,2);
 
(select c1 frQuery OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
mysql> select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x;
 
+------+
 
| x    |
 
+------+
 
|    2 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
mysql> (select c1 from t1 group by c1,c2 order by c1 limit 1);
 
+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select version();
 
+---------------------+
 
| version()           |
 
+---------------------+
 
| 10.1.10-MariaDB-log |
 
+---------------------+
 
1 row in set (0.00 sec)

Recreate:
===

 
 
drop table t1;
 
create table t1(c1 int, c2 int, primary key(c2));
 
insert into t1 values(2,1),(1,2);
 
select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x;
 
(select c1 from t1 group by c1,c2 order by c1 limit 1);

Problem:
===
1 is expected for both queries.

 Comments   
Comment by Elena Stepanova [ 2016-06-15 ]

Thanks for the report and the test case.

Reproducible on MariaDB 10.0, 10.1, 10.2, not reproducible on MariaDB 5.5.
Reproducible on MySQL 5.6, but not on 5.5 or 5.7.

Comment by Varun Gupta (Inactive) [ 2017-01-12 ] 

EXPLAIN 
(select c1 from t1 group by c1,c2 order by c1 limit 1);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 
EXPLAIN
 
select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2
 
 

Comment by Sergei Petrunia [ 2017-01-14 ]

Review feedback provided over email

Comment by Sergei Petrunia [ 2017-01-16 ]

Ok to push

Generated at Thu Feb 08 07:40:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.