[MDEV-4925] Wrong result - count(distinct), Using index for group-by (scanning) - JIRA

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.0.4, 5.5.32
Fix Version/s: 5.5.38, 10.0.12
Component/s: None
Labels:
- upstream

Description

Taken from: http://bugs.mysql.com/bug.php?id=70038

test case:

--source include/have_innodb.inc

CREATE TABLE tmp (
  id int NOT NULL AUTO_INCREMENT,
  a int NOT NULL,
  b int NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ba (b, a)
) ENGINE=InnoDB;

INSERT INTO tmp (a, b) VALUES(1,101),(1,102),(1,103),(1,104),(1,105),(1,106),(1,107),(1,108),(1,109),(1,110);

SELECT COUNT(DISTINCT b) FROM tmp WHERE a = 1;

DROP TABLE tmp;

Select returns 5, should be 10. Myisam works correctly.
I think the problem is because in function QUICK_GROUP_MIN_MAX_SELECT::get_next() call to file->ha_index_read_map() fetches next row when it shouldn't. Happens when index_next_different() is called with is_index_scan = true.

Gliffy Diagrams

Attachments

Issue Links

links to

Bug #70038 - Wrong select count distinct with a field included in two-column unique key

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Timour Katchaounov added a comment - 2013-09-09 14:40

The only difference between the two test cases below is the presence of a PK. The test case with the PK produces wrong result, the other one works correctly:

– Wrong result
CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT, a char(3) NOT NULL, b char(3) NOT NULL,
PRIMARY KEY (id),
KEY ba (b, a)
) ENGINE=InnoDB;
INSERT INTO t1 (a, b) VALUES('777','101'),('777','102');

EXPLAIN EXTENDED
SELECT COUNT(DISTINCT b) FROM t1 WHERE a = '777';
-------------------------------------------------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

-------------------------------------------------------------------------------------------------------------------

1

SIMPLE

t1

range

NULL

ba

6

NULL

3

66.67

Using where; Using index for group-by (scanning)

-------------------------------------------------------------------------------------------------------------------

SELECT COUNT(DISTINCT b) FROM t1 WHERE a = '777';
-------------------

COUNT(DISTINCT b)

-------------------

1

-------------------

– Correct result
CREATE TABLE t2 (a char(3) NOT NULL, b char(3) NOT NULL,
KEY ba (b, a)) ENGINE=InnoDB;
INSERT INTO t2 (a, b) VALUES('777','101'),('777','102');

EXPLAIN EXTENDED
SELECT COUNT(DISTINCT b) FROM t2 WHERE a = '777';
-------------------------------------------------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

-------------------------------------------------------------------------------------------------------------------

1

SIMPLE

t2

range

NULL

ba

6

NULL

3

66.67

Using where; Using index for group-by (scanning)

-------------------------------------------------------------------------------------------------------------------

SELECT COUNT(DISTINCT b) FROM t2 WHERE a = '777';
-------------------

COUNT(DISTINCT b)

-------------------

2

-------------------

Show

Timour Katchaounov added a comment - 2013-09-09 14:40 The only difference between the two test cases below is the presence of a PK. The test case with the PK produces wrong result, the other one works correctly: – Wrong result CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT, a char(3) NOT NULL, b char(3) NOT NULL, PRIMARY KEY (id), KEY ba (b, a) ) ENGINE=InnoDB; INSERT INTO t1 (a, b) VALUES('777','101'),('777','102'); EXPLAIN EXTENDED SELECT COUNT(DISTINCT b) FROM t1 WHERE a = '777'; ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- id select_type table type possible_keys key key_len ref rows filtered Extra ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- 1 SIMPLE t1 range NULL ba 6 NULL 3 66.67 Using where; Using index for group-by (scanning) ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- SELECT COUNT(DISTINCT b) FROM t1 WHERE a = '777'; ------------------- COUNT(DISTINCT b) ------------------- 1 ------------------- – Correct result CREATE TABLE t2 (a char(3) NOT NULL, b char(3) NOT NULL, KEY ba (b, a)) ENGINE=InnoDB; INSERT INTO t2 (a, b) VALUES('777','101'),('777','102'); EXPLAIN EXTENDED SELECT COUNT(DISTINCT b) FROM t2 WHERE a = '777'; ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- id select_type table type possible_keys key key_len ref rows filtered Extra ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- 1 SIMPLE t2 range NULL ba 6 NULL 3 66.67 Using where; Using index for group-by (scanning) ----- ----------- ----- ----- ------------- ---- ------- ---- ---- -------- ------------------------------------------------- SELECT COUNT(DISTINCT b) FROM t2 WHERE a = '777'; ------------------- COUNT(DISTINCT b) ------------------- 2 -------------------

5 older comments

Hide

Permalink

Guangpu Feng added a comment - 2013-09-18 05:04

Glad to see the progress!

Show

Guangpu Feng added a comment - 2013-09-18 05:04 Glad to see the progress!

Hide

Permalink

Timour Katchaounov added a comment - 2013-10-09 19:10

Suggestion - add a storage engine property that tells the server if the engine supports this handler call sequence.

Show

Timour Katchaounov added a comment - 2013-10-09 19:10 Suggestion - add a storage engine property that tells the server if the engine supports this handler call sequence.

Hide

Permalink

Guangpu Feng added a comment - 2013-11-19 12:12

any progress?

Show

Guangpu Feng added a comment - 2013-11-19 12:12 any progress?

Hide

Permalink

Patryk Pomykalski added a comment - 2014-05-12 12:31

It was fixed in mysql 5.5.35 and the fix was merged. No test in mysql though.

Show

Patryk Pomykalski added a comment - 2014-05-12 12:31 It was fixed in mysql 5.5.35 and the fix was merged. No test in mysql though.

Hide

Permalink

Sergei Golubchik added a comment - 2014-05-12 13:57

Thanks! I've added the test case, and will now close this bug report.

Show

Sergei Golubchik added a comment - 2014-05-12 13:57 Thanks! I've added the test case, and will now close this bug report.

Wrong result - count(distinct), Using index for group-by (scanning)

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile