[MDEV-4311] Wrong result of COUNT(distinct) with low tmp_table_size / max_heap_table_size, part #2 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
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.

Attachments

Issue Links

relates to

MDEV-4063 SUM(distinct) gives wrong result when reducing max_heap_table_size

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2013-03-21 12:56

Elena Stepanova made changes - 2013-03-21 12:56

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-4063~~ [ ~~MDEV-4063~~ ]

Elena Stepanova made changes - 2013-03-21 13:00

Description

{code:sql}
--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; /* 8 */
INSERT INTO t1 (id) SELECT id FROM t1; /* 12 */
INSERT INTO t1 (id) SELECT id FROM t1; /* 16 */
INSERT INTO t1 (id) SELECT id FROM t1; /* 20 */
INSERT INTO t1 (id) SELECT id FROM t1; /* 24 */
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;
{code}

Results:
{noformat}

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)
{noformat}

{noformat}
revision-id: sergii@pisem.net-20130317104125-yyp99euwpir5ueho
revno: 3700
branch-nick: 5.5
{noformat}

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

{code:sql}
--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;
{code}

Results:
{noformat}

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)
{noformat}

{noformat}
revision-id: sergii@pisem.net-20130317104125-yyp99euwpir5ueho
revno: 3700
branch-nick: 5.5
{noformat}

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

Sergei Golubchik made changes - 2013-03-21 14:02

Assignee

Sergei Golubchik [ serg ]

Igor Babaev [ igor ]

Igor Babaev (Inactive) added a comment - 2013-03-22 17:39

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.

Igor Babaev (Inactive) added a comment - 2013-03-22 17:39 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.

Igor Babaev (Inactive) added a comment - 2013-03-23 00:59

A patch fixing the problem was sent for a review to Sergei Golubchik.

Igor Babaev (Inactive) added a comment - 2013-03-23 00:59 A patch fixing the problem was sent for a review to Sergei Golubchik.

Igor Babaev (Inactive) added a comment - 2013-03-28 21:05

On 2013-03-22 I reported bug http://bugs.mysql.com/bug.php?id=68749 for mysql-5.5/5.6

Igor Babaev (Inactive) added a comment - 2013-03-28 21:05 On 2013-03-22 I reported bug http://bugs.mysql.com/bug.php?id=68749 for mysql-5.5/5.6

Igor Babaev (Inactive) added a comment - 2013-03-28 21:07

The fix is pushed into the 5.5 tree, will appear in 5.5.31

Igor Babaev (Inactive) added a comment - 2013-03-28 21:07 The fix is pushed into the 5.5 tree, will appear in 5.5.31

Igor Babaev (Inactive) made changes - 2013-03-28 21:07

Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:07

Workflow

defaullt [ 26725 ]

MariaDB v2 [ 46481 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 46481 ]

MariaDB v3 [ 67186 ]

Sergei Golubchik made changes - 2021-12-06 21:38

Workflow

MariaDB v3 [ 67186 ]

MariaDB v4 [ 146542 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2013-03-21 12:56

Updated:: 2013-03-28 21:07

Resolved:: 2013-03-28 21:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration