And if you want lots of (boringly identical) rows: WITH x AS ( SELECT * FROM ( VALUES 1,1,1,1,1,1,1,1,1,1 ) AS MyTable( key ) ) SELECT COUNT(*) FROM x AS x1 INNER JOIN x AS x2 on x2.key = x1.key INNER JOIN x AS x3 on x3.key = x2.key INNER JOIN x AS x4 on x4.key = x3.key INNER JOIN x AS x5 on x5.key = x4.key INNER JOIN x AS x6 on x6.key = x5.key INNER JOIN x AS x7 on x7.key = x6.key;
Also, it doesn't even need to appear as a subquery in a FROM clause, so it works for simply evaluating an expression (without needing to refer to an existing table or file). Try: VALUES 1; VALUES 1/3; VALUES CAST(1 AS FLOAT) / 3; VALUES USER; Daniel Jacques Nadeau wrote:
I just realized that Drill now supports a new construct that we hadn't previously tried out. We can use the VALUES operator in the FROM clause thereby generating our own data. Nice that Calcite already gave this to us when we fixed the large in clause problem: Example: SELECT * FROM (VALUES (1, 1.0, DATE '2008-2-23', TIME '12:23:34', TIMESTAMP '2008-2-23 12:23:34.456', INTERVAL '1' YEAR, INTERVAL '2' DAY), (1, 1.0, DATE '2008-2-23', TIME '12:23:34', TIMESTAMP '2008-2-23 12:23:34.456', INTERVAL '1' YEAR, INTERVAL '2' DAY) ) AS Example(myInt, myFloat, myDate, myTime, myTimestamp, int1, int2); +--------+----------+-------------+-----------+--------------------------+-------+-------+ | myInt | myFloat | myDate | myTime | myTimestamp | int1 | int2 | +--------+----------+-------------+-----------+--------------------------+-------+-------+ | 1 | 1.0 | 2008-02-23 | 04:23:34 | 2008-02-23 04:23:34.456 | P12M | P2D | | 1 | 1.0 | 2008-02-23 | 04:23:34 | 2008-02-23 04:23:34.456 | P12M | P2D | +--------+----------+-------------+-----------+--------------------------+-------+-------+
-- Daniel Barclay MapR Technologies
