Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1(EOL), 10.2(EOL)
-
10.1.24
Description
|
TEST CASE |
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,1),(2,2),(3,3); |
|
|
SELECT
|
i,
|
( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt |
FROM t1 AS outer_t1; |
|
|
SELECT
|
( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
GROUP_CONCAT(i)
|
FROM t1 AS outer_t1 |
GROUP BY cnt; |
|
|
DROP TABLE t1; |
On 10.1 before revision 8d4871a95340dc, the first query returned the correct result, but the second query did not:
|
10.1 cd494f4cefb36f (old) |
MariaDB [test]> SELECT |
-> i,
|
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt |
-> FROM t1 AS outer_t1; |
+------+------+ |
| i | cnt |
|
+------+------+ |
| 1 | 3 |
|
| 2 | 3 |
|
| 3 | 3 |
|
+------+------+ |
3 rows in set (0.00 sec) |
MariaDB [test]> SELECT |
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
-> GROUP_CONCAT(i)
|
-> FROM t1 AS outer_t1 |
-> GROUP BY cnt; |
+------+-----------------+ |
| cnt | GROUP_CONCAT(i) |
|
+------+-----------------+ |
| 0 | 1,2,3 |
|
+------+-----------------+ |
1 row in set (0.00 sec) |
(note 0 in the cnt field, it should be 3).
Now the result for the 2nd query is different, but also wrong:
|
10.1 3bb32e8682f849 (new) |
MariaDB [test]> SELECT |
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
-> GROUP_CONCAT(i)
|
-> FROM t1 AS outer_t1 |
-> GROUP BY cnt; |
+------+-----------------+ |
| cnt | GROUP_CONCAT(i) |
|
+------+-----------------+ |
| 3 | 1 |
|
| 3 | 2,3 |
|
+------+-----------------+ |
2 rows in set (0.00 sec) |
Naturally, there shouldn't be two rows with cnt=3.
However, on 10.2 until recently the result was correct:
|
10.2.5 |
MariaDB [test]> SELECT |
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
-> GROUP_CONCAT(i)
|
-> FROM t1 AS outer_t1 |
-> GROUP BY cnt; |
+------+-----------------+ |
| cnt | GROUP_CONCAT(i) |
|
+------+-----------------+ |
| 3 | 1,2,3 |
|
+------+-----------------+ |
1 row in set (0.00 sec) |
But now it is not:
|
10.2 54a995cd2206 (new) |
MariaDB [test]> SELECT |
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
-> GROUP_CONCAT(i)
|
-> FROM t1 AS outer_t1 |
-> GROUP BY cnt; |
+------+-----------------+ |
| cnt | GROUP_CONCAT(i) |
|
+------+-----------------+ |
| 3 | 1 |
|
| 3 | 2,3 |
|
+------+-----------------+ |
2 rows in set (0.00 sec) |
So, for 10.2 it's certainly a regression.
The difference appeared after merges, likely from this change in 10.0:
commit 57a699b0a0f3300404948775356d31fb478e80c6
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Fri Jun 17 16:51:11 2016 +0200
|
|
|
MDEV-8642: WHERE Clause not applied on View - Empty result set returned
|
|
An attempt to mark reference as dependent lead to transfering this property to
|
original view field and through it to other references of this field which
|
can't be dependent.
|
Although in 10.0, at least for the test case above, it did good.