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