Select values without table

Help
1

Hello,
I want to use multiple pair values for my query to JOIN.
I tried this:
SELECT * FROM tab JOIN
(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

2

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.

3

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 * FROM tab JOIN
(SELECT 1 as a,2 as b UNION ALL SELECT 3 as a, 4 as b) vals WHERE …