Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.30
-
None
-
None
Description
--source include/have_xtradb.inc
|
|
CREATE TABLE t1 (id INTEGER) ENGINE=InnoDB; |
CREATE TABLE t2 (id INTEGER) ENGINE=InnoDB; |
|
INSERT INTO t1 (id) VALUES (1), (1), (1),(1); |
INSERT INTO t1 (id) SELECT id FROM t1; |
INSERT INTO t1 (id) SELECT id FROM t1; |
INSERT INTO t1 (id) SELECT id FROM t1; |
INSERT INTO t1 (id) SELECT id FROM t1; |
INSERT INTO t1 (id) SELECT id FROM t1; |
INSERT INTO t1 SELECT id+1 FROM t1; |
INSERT INTO t1 SELECT id+2 FROM t1; |
INSERT INTO t1 SELECT id+4 FROM t1; |
INSERT INTO t1 SELECT id+8 FROM t1; |
INSERT INTO t1 SELECT id+16 FROM t1; |
INSERT INTO t1 SELECT id+32 FROM t1; |
INSERT INTO t1 SELECT id+64 FROM t1; |
INSERT INTO t1 SELECT id+128 FROM t1; |
INSERT INTO t1 SELECT id+256 FROM t1; |
INSERT INTO t1 SELECT id+512 FROM t1; |
INSERT INTO t1 SELECT id+1024 FROM t1; |
INSERT INTO t1 SELECT id+2048 FROM t1; |
INSERT INTO t1 SELECT id+4096 FROM t1; |
INSERT INTO t1 SELECT id+8192 FROM t1; |
|
INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); |
|
SET @tmp_table_size_saved = @@tmp_table_size; |
SET @max_heap_table_size_saved = @@max_heap_table_size; |
|
--echo # With default tmp_table_size / max_heap_table_size
|
SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2; |
|
SET @@tmp_table_size=524288; |
|
--echo # With reduced tmp_table_size
|
SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2; |
|
SET @@tmp_table_size=@tmp_table_size_saved; |
SET @@max_heap_table_size=524288; |
|
--echo # With reduced max_heap_table_size
|
SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2; |
|
SET @@max_heap_table_size=@max_heap_table_size_saved; |
|
--echo # Back to default tmp_table_size / max_heap_table_size
|
SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2; |
|
DROP TABLE t1, t2; |
Results:
|
MariaDB [test]> --echo # With default tmp_table_size / max_heap_table_size
|
MariaDB [test]> SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
|
+-------+
|
| sm |
|
+-------+
|
| 16384 |
|
+-------+
|
1 row in set (14.81 sec)
|
|
MariaDB [test]> SET @@tmp_table_size=524288;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> --echo # With reduced tmp_table_size
|
MariaDB [test]> SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
|
+----+
|
| sm |
|
+----+
|
| 0 |
|
+----+
|
1 row in set (14.85 sec)
|
|
MariaDB [test]> SET @@tmp_table_size=@tmp_table_size_saved;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> SET @@max_heap_table_size=524288;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> --echo # With reduced max_heap_table_size
|
MariaDB [test]> SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
|
+----+
|
| sm |
|
+----+
|
| 0 |
|
+----+
|
1 row in set (14.84 sec)
|
|
MariaDB [test]> SET @@max_heap_table_size=@max_heap_table_size_saved;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> --echo # Back to default tmp_table_size / max_heap_table_size
|
MariaDB [test]> SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
|
+-------+
|
| sm |
|
+-------+
|
| 16384 |
|
+-------+
|
1 row in set (14.84 sec)
|
revision-id: sergii@pisem.net-20130317104125-yyp99euwpir5ueho
|
revno: 3700
|
branch-nick: 5.5
|
Please note that the test case (if used in MTR) contains have_xtradb. This is to exclude the InnoDB-plugin combination, since on some reason I'm getting the wrong result only with XtraDB. In the final test case it should be replaced with have_innodb as it's more universal.
Attachments
Issue Links
- relates to
-
MDEV-4063 SUM(distinct) gives wrong result when reducing max_heap_table_size
- Closed