Description:
We have a table with a 4 column composite PK that we are querying against and getting mixed results; however, the mixed results are consistently reproducible and predictable.
How to repeat:
1. Ran the following script (note- all four columns in this script comprise the composite PK):
SELECT *
FROM schema.table
WHERE
column1 = 31000753368
and column2 IN ('130','131','132')
and column3 = 49
and column4 = 2;
We get 3 results.
2. Ran a similar query which should provide at least the 3 results from the previous query:
SELECT *
FROM schema.table
WHERE
(column1 = 31000753368 and column2 = '130' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '131' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '132' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '135' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '136' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '137' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '138' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '139' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '142' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '143' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '145' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '146' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '147' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '148' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '149' and column3 = 49 and column4 = 2) OR
(column1 = 31000753368 and column2 = '150' and column3 = 49 and column4 = 2);
we get 1 result (should have gotten at least 3).
3. Re-ran the query from step 1 above:
SELECT *
FROM schema.table
WHERE
column1 = 31000753368
and column2 IN ('130','131','132')
and column3 = 49
and column4 = 2;
We get 1 result.
4. Ran optimize on the table, re-ran steps 1 through 3 from above and the results were the same: at first, query 1 produces 3 results, but after running query 2, which gets 1 result, query 1 reverts to 1 result.
It appears to me that the query plan/statistics are getting bungled when the script from step 2 is run. We tried to reproduce the behavior on other MySQL server builds and here are the results:
-On a MySQL 5.1.69 server, the results are correct and consistent for both queries
-On a MySQL 5.5.8 server, the results are correct and consistent for both queries
-On a MySQL 5.5.46 server, same incorrect results as detailed above
-On a MySQL 5.5.47 server, same incorrect results as detailed above
-On a MySQL 5.6.28 server- the results are correct and consistent for both queries
Additionally:
-I tried the above on a Windows 7 computer and got the same results as on Windows 2008 R2 Datacenter- no change in behavior.
-I replaced our modified my.ini with a clean my.ini and this did not affect the behavior.
-I tried changing innodb_stats_sample_pages to various other values and this did not affect the behavior.
-I tried changing the column order to match the PK and this did not affect the behavior.
Workarounds:
-If we run:
SELECT *
FROM schema.table
FORCE INDEX (PRIMARY)
WHERE
column1 = 31000753368
and column2 IN ('130','131','132')
and column3 = 49
and column4 = 2;
We get the correct results even after running query 2 from above.
-If we reduce the composite PK to a 1 column PK and use a 4 column index, we get the correct results.
Suggested fix:
Not sure, since this appears to me to be an issue with the query planner and/or statistics only on certain MySQL builds.
Description: We have a table with a 4 column composite PK that we are querying against and getting mixed results; however, the mixed results are consistently reproducible and predictable. How to repeat: 1. Ran the following script (note- all four columns in this script comprise the composite PK): SELECT * FROM schema.table WHERE column1 = 31000753368 and column2 IN ('130','131','132') and column3 = 49 and column4 = 2; We get 3 results. 2. Ran a similar query which should provide at least the 3 results from the previous query: SELECT * FROM schema.table WHERE (column1 = 31000753368 and column2 = '130' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '131' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '132' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '135' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '136' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '137' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '138' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '139' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '142' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '143' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '145' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '146' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '147' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '148' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '149' and column3 = 49 and column4 = 2) OR (column1 = 31000753368 and column2 = '150' and column3 = 49 and column4 = 2); we get 1 result (should have gotten at least 3). 3. Re-ran the query from step 1 above: SELECT * FROM schema.table WHERE column1 = 31000753368 and column2 IN ('130','131','132') and column3 = 49 and column4 = 2; We get 1 result. 4. Ran optimize on the table, re-ran steps 1 through 3 from above and the results were the same: at first, query 1 produces 3 results, but after running query 2, which gets 1 result, query 1 reverts to 1 result. It appears to me that the query plan/statistics are getting bungled when the script from step 2 is run. We tried to reproduce the behavior on other MySQL server builds and here are the results: -On a MySQL 5.1.69 server, the results are correct and consistent for both queries -On a MySQL 5.5.8 server, the results are correct and consistent for both queries -On a MySQL 5.5.46 server, same incorrect results as detailed above -On a MySQL 5.5.47 server, same incorrect results as detailed above -On a MySQL 5.6.28 server- the results are correct and consistent for both queries Additionally: -I tried the above on a Windows 7 computer and got the same results as on Windows 2008 R2 Datacenter- no change in behavior. -I replaced our modified my.ini with a clean my.ini and this did not affect the behavior. -I tried changing innodb_stats_sample_pages to various other values and this did not affect the behavior. -I tried changing the column order to match the PK and this did not affect the behavior. Workarounds: -If we run: SELECT * FROM schema.table FORCE INDEX (PRIMARY) WHERE column1 = 31000753368 and column2 IN ('130','131','132') and column3 = 49 and column4 = 2; We get the correct results even after running query 2 from above. -If we reduce the composite PK to a 1 column PK and use a 4 column index, we get the correct results. Suggested fix: Not sure, since this appears to me to be an issue with the query planner and/or statistics only on certain MySQL builds.