I was on mysql:8.0.15 and I updated to mysql:8.0.17

  • On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect
  • On 8.0.17 the same query with same data return 0 results.

I thought it was our application, so I downgraded back to 8.0.15 and the query is working again...

I noticed ... and (col1, col2) in (('a', 'b'),('a', 'b')) is working in 8.0.17

I tried to search mysql bug list, but I can't find any information... In the changelog I see they optimized WHERE IN in 8.0.17, but it should not break my use case.

The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery. This is similar to the existing IS NULL (Not exists) outer join optimization; see EXPLAIN Extra Information, for further information. In addition, the semijoin materialization can now be used for a WHERE condition having IN (subquery) IS TRUE, or EXISTS (subquery) IS TRUE, or when the IN condition belongs to a left join such as SELECT * FROM t1 LEFT JOIN t2 ON t2.x IN (SELECT * FROM t3). See Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations. Also as a result of this work, MySQL is now able to simplify a co

It seems too big to be a bug in mysql. Am I missing something ?

Here is the full query

select `t1`.`user_id`    as `user_id`,
       `t1`.`created_at` as `created_at`,
       `t1`.`updated_at` as `updated_at`,
       `t3`.`node_id`    as `t3node_id`,
       `t3`.`node_type`  as `t3node_type`,
       `t2`.`node_id`    as `t2node_id`,
       `t2`.`node_type`  as `t2node_type`
from `table1` as `t1`
         inner join `table2` as `t2` on `t1`.`set_id` = `t2`.`set_id` and `t1`.`set_type` = `t2`.`set_type`
         left join `table2` as `t3`
                   on `t2`.`node_type` = 3 and `t3`.`set_type` = 5 and `t3`.`set_id` = `t2`.`node_id`
         left join `table3` as `ts` on COALESCE(t3.node_type, t2.node_type) = `ts`.`cache_node_type` and
                                                 COALESCE(t3.node_id, t2.node_id) = `ts`.`cache_node_id`
where `t1`.`tenant_id` = '825773475713154048'
and (`t1`.`user_id`, `t2`.`node_id`) in ( ('825773502556700160', '825773839074099712'))

On 8.0.17 I get 0 results. On 8.0.15 it works.

  • If, on 8.0.17, I replace the last line by and (t1.user_id, t2.node_id) in ( ('825773502556700160', '825773839074099712'), ('825773502556700160', '825773839074099712')) (notice it's the same tuple, two times) I get results.
  • It works with this and (t1.user_id, t2.node_id) = ('825773502556700160', '825773839074099712')
  • I tried with a simple query SELECT * FROM test.test as t1 WHERE (t1.col1,t1.col2) IN (('a','b')); it works on 8.0.17.
  • 3
    The quote talks about "subquery"; your abbreviated code does not have such. Which case are you talking about? The are probably optimized very differently. – Rick James Jul 24 '19 at 17:20
  • As for filing a bug -- do it. "Row constructors" are only rarely used, and it sounds like some new optimizations are being added. – Rick James Jul 24 '19 at 17:22
  • 2
    Meanwhile, let's see the whole query to discuss workarounds. – Rick James Jul 24 '19 at 17:23
  • @RickJames I added more infos, thank you – user3803241 Jul 25 '19 at 7:35
  • 2
    "It seems too big to be a bug in mysql" - well, MySQL was never known for making quality the highest priority. – a_horse_with_no_name Jul 25 '19 at 9:01

Have you tried a simple IN clause other than the one in your code? If not, I would write a couple simple ones and try them to see if they run. If they run successfully, it's either you copied it incorrectly, OR it's got to be something specific with how your code is using the IN statement.

If no IN statements run, something was changed, either by design or it's a bug.


(too messy for Comment)

If you change

and (`t1`.`user_id`, `t2`.`node_id`) in ( ('825773502556700160', '825773839074099712'))


and (`t1`.`user_id` = 825773502556700160
 AND `t2`.`node_id` = 825773839074099712)

does 8.0.17 work?

Note the difference: You are touching two different tables. Was that intentional?

  • yes it works but I rarely have only one tuple, most of the time I have more... – user3803241 Jul 27 '19 at 10:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.