Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.36, 10.0.9
-
None
-
Centos 6.5 64bit base install
Description
Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query:
SELECT count(*) |
FROM `test` |
WHERE `col2` = '636146' AND `col1` = '7+'; |
result: 0
Same query while running after:
SET optimizer_switch='index_merge=off'; |
result: 73
I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems.
I was unable to reproduce same error on MySQL 5.5.35
Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | MariaDB_5.5 optimizer | optimizer |
| Description |
Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query: SELECT count(*) FROM `test` WHERE `col2` = '636146' AND `col1` = '7+'; result: 0 Same query while running after: SET optimizer_switch='index_merge=off'; result: 73 I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems. I was unable to reproduce same error on MySQL 5.5.35 Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system. |
Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query: {code:sql} SELECT count(*) FROM `test` WHERE `col2` = '636146' AND `col1` = '7+'; {code} result: 0 Same query while running after: {code:sql} SET optimizer_switch='index_merge=off'; {code} result: 73 I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems. I was unable to reproduce same error on MySQL 5.5.35 Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system. |
| Assignee | Elena Stepanova [ elenst ] |
| Affects Version/s | 5.5.35 [ 14000 ] |
| Fix Version/s | 10.0.10 [ 14500 ] | |
| Fix Version/s | 5.5.37 [ 15000 ] | |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Affects Version/s | 10.0.9 [ 14400 ] | |
| Assignee | Elena Stepanova [ elenst ] | Sergei Petrunia [ psergey ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
Elena, is it reproducible on MySQL 5.6.16 or on MySQL 5.6.15?
I'm using the top of the public mysql-server/5.6 bzr tree, which has revno 5732 (tag mysql-5.6.16), dated 2014-01-09.
Ok, I can repeat this bug when I try on mariadb-5.5 without fix for MDEV-5177, and I cannot repeat it when fix for MDEV-5177 is applied. Other things also hint this is the same issue.
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
Vojtech, thanks for taking time to report the issue. Although this one has turned out to be already known, we (MariaDB devs) appreciate getting input.
Thank you for fix. I'll be waiting for next release. In meantime I'll use 'index_merge=off' as workaround.
| Workflow | defaullt [ 36413 ] | MariaDB v2 [ 43397 ] |
| Workflow | MariaDB v2 [ 43397 ] | MariaDB v3 [ 63154 ] |
| Workflow | MariaDB v3 [ 63154 ] | MariaDB v4 [ 147607 ] |
Reproducible with the data from the attachment and the query from the description: on 5.3 (14/73 rows), 5.5, 10.0 (0/73 rows), mysql-5.6 (70/73 rows).
The bug is likely to be a duplicate of
MDEV-5177, but since the latter turned out to be tricky, I suggest to make sure the bugfix covers this test case as well before closing it.