Select values without table

I want to use multiple pair values for my query to JOIN.
I tried this:
(SELECT 1 as a,2 as b UNION ALL SELECT 3,4) vals WHERE …

There are 2 issues -

  1. query is limited to ~256 “unions” (can be replaced by a union of unions)
  2. unions are creating new compiling plans - when I use thousands of queries with this, it is killing cluster

Is there any way, how to solve this. I want to avoid using some temporary table (insert, select, delete) - if it is possible. (MySQL supports something like this: SELECT * FROM (VALUES (1 ,2),(3,4)) t )

Thanks, Jan

Hi Jan,

We currently don’t support the MySQL row constructor syntax (VALUES (1,2) used in SELECT queries). That is a feature we are tracking for the medium term as it does come up now and then.

One option maybe is to populate a temp table and then join vs that. Another option is to try out the TABLE() builtin. Depending on values you want to generate that maybe the best way.

One other thing,

If you give all the projection list elements aliases in your UNIONs you should avoid issues with code-gen triggering when ever the values changes (project list elements with aliases get parameterized out. Those without aliases are left hard coded into the generated code).

(SELECT 1 as a,2 as b UNION ALL SELECT 3 as a, 4 as b) vals WHERE …