Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.2, 5.5.31, 5.3.12
-
None
-
None
Description
Since 5.3 MariaDB supports this optimization:
This optimization, although correct in strict SQL standard sense, does not always work when the subquery uses non-aggregated columns in the SELECT clause. This is non-standard MariaDB extension, but as long as it is supported, the optimizer should take it into account.
MariaDB [test]> create table t1 (a int, b int);
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> insert t1 values (1,1),(2,1);
|
Query OK, 2 rows affected (0.00 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select a from t1 group by b;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.01 sec)
|
|
|
MariaDB [test]> select count(*) from t1 where a in (select a from t1);
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select count(*) from t1 where a in (select a from t1 group by b);
|
+----------+
|
| count(*) |
|
+----------+
|
| 2 | <-- this is wrong!
|
+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select count(*) from t1 where a in (select min(a) from t1 group by b);
|
+----------+
|
| count(*) |
|
+----------+
|
| 1 |
|
+----------+
|
1 row in set (0.00 sec)
|
As a possible fix, the optimizer could disable this optimization when non-aggregated columns are selected. It already performs all necessary checks for the ONLY_FULL_GROUP_BY sql mode.