Use of Multiple Columns within IN

Help
1

I am using multiple column as primary key. So, When I am running below query in singlestore it is giving me syntax error but that same query is working fine on MySQL.
SELECT * FROM products WHERE (price,qty) IN ((380,1), (350,5))

Is anyone can help me on that? Thanks

2

Hi Munir! :wave: We’re sorry you are experiencing this issue. Happy to help. Are you running on the managed or self-hosted service & what version number?

3

Hi Maria,
We are using managed Database. Version is 7.6.13

1 Like
4

We don’t support this syntax, but you can rewrite it to use a correlated subquery, or put the tuples in your IN list into a temp table and use a join or EXISTS subquery instead. For example:

create table t(a int, b int);
insert t values(380,1), (350,5);

select * 
from t 
where exists 
  (select 1
   from (select 380 as a, 1 as b 
         union all
         select 350, 5) as x
   where t.a=x.a and t.b=x.b);

-- or

create temporary table tmp like t;
insert into tmp select * from t;
select *
from t
where exists 
  (select 1
   from tmp
   where t.a=tmp.a and t.b=tmp.b);
1 Like
5

relates to Row value expressions

1 Like