IN clause not working on mysql 8.0.17 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.
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.