Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.30
-
Fix Version/s: 5.5.31
-
Component/s: None
-
Labels: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.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
A patch fixing the problem was sent for a review to Sergei Golubchik.
On 2013-03-22 I reported bug http://bugs.mysql.com/bug.php?id=68749 for mysql-5.5/5.6
The fix is pushed into the 5.5 tree, will appear in 5.5.31
This bug has nothing to do with mdev-4063.
Only MariaDB 5.5 (all releases) is affected. All 10.0 realeases are affected too.
Moreover the bug can be reproduced on any release of MySQL 5.5 and any release of MySQL 5.6
with a slightly changed test case:
it's enough to add
INSERT INTO t2 VALUES (NULL)
after
INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
The bug was introduced into MySQL 5.5 code line by the patch for WL#3220 in September 2009.
MariaDB 5.3 is not affected as the code was merged only onto MariaDB 5.5.
A fix for the bug will be submitted soon.