|
|
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.
|