empty query after mysql upgrade from 5.5 to 5.7

Question

I'm working on a laravel 4.2 app that was running on a mysql 5.5. This app migrated to a new server, now running a mysql 5.7.

After the upgrade some queries are now returning empty.

I believe this as something to do with the sql_mode mysql 5.7 has introduce.

Example query

SELECT * FROM `table1` WHERE `col1` = 'val1' and 
(SELECT count(*) FROM `table2` 
WHERE `table2`.`table1_id` = `table1`.`id` 
   and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1

This is a query that laravel eloquent produces. It returns empty. On the old mysql 5.5 it returns one line as expected.

If fixed the id from the main query it works.

... WHERE `table2`.`table1_id` = 86 ...

As I said before I believe this as something to do with the sql_mode.

Any thoughts?

Answer 1

There's nothing wrong with the query, it's true that you can change the query to avoid the problem but that doesn't solve the real issue.

The solution is to disable index_merge_intersection in the optimizer_switch configuration.

It's a bug found on mysql 5.7 https://bugs.mysql.com/bug.php?id=79675

It worked for me.

Answer 2

In most databases that support it, count(*) doesn't actually retrieve all records and count them -- instead it fetches some metadata field that just tracks the number of rows

Of course, it's reasonable to expect that, regardless of how it's implemented, the result of count(*) would be the same as more a complex but equivalent query.

SELECT * FROM `table1` WHERE `col1` = 'val1' and 
(SELECT count(`table2`.`id`) FROM `table2` 
WHERE `table2`.`table1_id` = `table1`.`id` 
and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1

or for getting boolean try this

SELECT * FROM `table1` WHERE `col1` = 'val1' and 
(SELECT 1 FROM `table2` 
WHERE `table2`.`table1_id` = `table1`.`id` 
and `driver_id` = '39') >= 1 and `table1`.`id` = '86' LIMIT 1