IN clause not working on mysql 8.0.17
1
vote
2
answers
1845
views
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.
Asked by user3803241
(21 rep)
Jul 24, 2019, 04:13 PM
Last activity: May 2, 2021, 12:01 AM
Last activity: May 2, 2021, 12:01 AM