Bug #93703 EXPLAIN SELECT returns inconsistent number of ROWS in main.group_by

Submitted: 20 Dec 2018 12:30 Modified: 22 Jan 2019 5:56
Reporter: Przemysław Skibiński (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Tests Severity:S7 (Test Cases)
Version:8.0.13, 8.0.14 OS:Any
Assigned to: CPU Architecture:Any

[20 Dec 2018 12:30] Przemysław Skibiński
Description:
EXPLAIN SELECT sometimes returns 4 or 5 as "rows" what is reported as errors for `main.group_by`:

--- /tmp/results/PS/mysql-test/r/group_by.result	2018-12-19 13:10:55.000000000 +0300
+++ /tmp/results/PS/mysql-test/var/4/log/group_by.reject	2018-12-19 15:29:24.416116648 +0300
@@ -1752,7 +1752,7 @@
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `SUM(b)` from `test`.`t2` group by `test`.`t2`.`a` limit 2
 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	NULL	ALL	a	NULL	NULL	NULL	5	100.00	Using temporary
+1	SIMPLE	t2	NULL	ALL	a	NULL	NULL	NULL	4	100.00	Using temporary

How to repeat:
The minimal MTR test to show the issue:

CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);

CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);

EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;

EXPLAIN SELECT a FROM t1 GROUP BY a;
EXPLAIN SELECT a FROM t2 GROUP BY a;
[16 Jan 2019 8:06] MySQL Verification Team
Hello Przemysław,

Thank you for the report and test case.
I'm not seeing any issues at my end using provided test case.

 cat t/bug93703.test
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);

CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);

EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;

EXPLAIN SELECT a FROM t1 GROUP BY a;
EXPLAIN SELECT a FROM t2 GROUP BY a;

./mtr --nocheck-testcases bug93703
Logging: ./mtr  --nocheck-testcases bug93703
MySQL Version 8.0.13
Checking supported features...
Using 'all' suites
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-8.0.13/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13019
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t1      NULL    ALL     a       NULL    NULL    NULL    4       100.00  Using temporary
Warnings:
Note    1003    /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a`
EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t2      NULL    ALL     a       NULL    NULL    NULL    4       100.00  Using temporary
Warnings:
Note    1003    /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a`
EXPLAIN SELECT a FROM t1 GROUP BY a;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t1      NULL    index   a       a       5       NULL    4       100.00  Using index
Warnings:
Note    1003    /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN SELECT a FROM t2 GROUP BY a;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t2      NULL    index   a       a       5       NULL    4       100.00  Using index
Warnings:
Note    1003    /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` group by `test`.`t2`.`a`
[100%] main.bug93703                            [ pass ]     48
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.048 of 8 seconds executing testcases

Completed: All 1 tests were successful.

Please let me know if anything I'm missing here. Thank you!

regards,
Umesh
[21 Jan 2019 14:58] Przemysław Skibiński
Hi Umesh,

Please try debug server with Ubuntu Bionic:
./mysql-test/mtr --debug-server bug93703

I have the following results at every run: 

CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	a	NULL	NULL	NULL	5	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a`
EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	a	NULL	NULL	NULL	4	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a`
[22 Jan 2019 5:56] MySQL Verification Team
Thank you, Przemysław.
In the second attempt observed the issue.

--
cat /etc/*release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04 LTS"
NAME="Ubuntu"
VERSION="18.04 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

-
ushastry@BionicBeaver:~/Downloads/mysql-8.0.14$ cat docs/INFO_SRC
commit: 775cb233ec89476150e7a040e372a39e4a64eb81
date: 2018-12-20 21:05:15 +0100
build-date: 2018-12-20 21:14:22 +0100
short: 775cb23
branch: mysql-8.0.14-release

MySQL source 8.0.14

ushastry@BionicBeaver:~/Downloads/mysql-8.0.14/mysql-test$ ./mtr --debug-server --nocheck-testcases bug93703 
Logging: ./mtr  --debug-server --nocheck-testcases bug93703
MySQL Version 8.0.14
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/ushastry/Downloads/mysql-8.0.14/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	a	NULL	NULL	NULL	5	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a`
EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	a	NULL	NULL	NULL	4	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a`
EXPLAIN SELECT a FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	a	a	5	NULL	5	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN SELECT a FROM t2 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	a	a	5	NULL	4	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` group by `test`.`t2`.`a`
[100%] main.bug93703                             [ pass ]    460
------------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.460 of 52 seconds executing testcases

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.