VALUES / VALUES CYCLE

VALUES uses expressions to calculate one or more row values, and is often used within a larger SQL command such as SELECT and INSERT. When creating more than one row, the VALUES clause must specify the same number of elements for every row. The resulting table-columns data-types are derived from the explicit or inferred types of the expressions appearing in that column. VALUES is allowed syntactically wherever SELECT is permitted. See also the discussion of VALUES as an operator, in the topic Query in this guide.

This topic covers:

Syntax

VALUES [ CYCLE ] ( expression [,...] ) [,...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [,...] ]

Usage

VALUES is a SQL operator, on a par with SELECT and UNION, enabling the following types of actions:

  • You can write VALUES (1), (2) to return two rows each with a single anonymous column.
  • You can write VALUES (1, ‘a’), (2, ‘b’) to return two rows of two columns.

SELECT … FROM (VALUES(…))

You can select from VALUES When you use VALUES in the FROM clause of a SELECT statement:

  • the entire VALUES clause must be enclosed in parentheses, consistent with the fact that it operates as a query, not a table expression. See additional examples in the topic Relations in this guide.
  • columns are named EXPR$0, EXPR$1, …
  • You can name the columns using AS, as in the following example:
    SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y);
    
  • or you can name the columns in the outer select:
    SELECT EXPR$0 AS X, EXPR$1 AS Y FROM (VALUES (1, 'a'), (2, 'b'));
    

Using VALUES to learn how SQLstream functions work

If you want to check how a SQL function works, you can test it using VALUES:

0: jdbc:sqlstream:sdp://sqlstream-base> values(substring('Hello World',2,5));
'EXPR$0'
'ello '
1 row selected (0.116 seconds)

This can be particularly useful when validating regular expression usage in functions like REGEX_REPLACE:


0: jdbc:sqlstream:sdp://sqlstream-base> values regex_replace('The pen is mightier than the sword', 'i[a-zA-Z]', 'HAHA', 1, 0);
'EXPR$0'
'The pen HAHA mHAHAhtHAHAr than the sword'
1 row selected (0.1 seconds)

VALUES Views

VALUES are often used to create reusable views - often for the OPTIONS_QUERY option associated with foreign streams.

CREATE OR REPLACE VIEW SALES.CSV_OPTIONS(SEPARATOR, QUOTE_CHARACTER, SKIP_HEADER) AS VALUES(',', '"', 'true');

!outputformat table             

select * from sales.csv_options;
+-----------+-----------------+-------------+
| SEPARATOR | QUOTE_CHARACTER | SKIP_HEADER |
+-----------+-----------------+-------------+
| ,         | "               | true        |
+-----------+-----------------+-------------+
1 row selected (0.221 seconds)

The view definition also provides a third way to relabel the columns.

INSERT … VALUES

An important use of VALUES is in an INSERT statement, to insert a single row:

INSERT INTO emps (empno, name, deptno, gender)
VALUES (107, 'Jane Costa', 22, 'F');

However, you can also insert multiple rows:

INSERT INTO Trades (ticker, price, amount)
VALUES ('MSFT', 30.5, 1000),
('ORCL', 20.25, 2000);

Note: Using INSERT with streams engages some additional considerations as to rowtimes, pumps, and INSERT EXPEDITED. See the topic INSERT in this guide.

VALUES CYCLE

As well as VALUES, SQLstream supports a continuously streaming source using VALUES CYCLE. This is an extension to the SQL standard.

0: jdbc:sqlstream:sdp://sqlstream-base> values cycle(1),(2);                                                                                                                          
+------------+
|   EXPR$0   |
+------------+
| 1          |
| 2          |
| 1          |
| 2          |
| 1          |
| 2          |
| 1          |
| 2          |
| 1          |
| 2          |
...

Rows are emitted as fast as possible, cycling the row values for ever or until the statement is cancelled.

This allows some simple performance testing of individual operations - especially aggregations:

1/1          select stream step(s.rowtime by interval '1' second),count(*) 
from (select stream * from (values cycle(1))) s 
group by step(s.rowtime by interval '1' second);

'EXPR$0','EXPR$1'
'2021-05-25 17:38:46.0','1834980'
'2021-05-25 17:38:47.0','4456380'
'2021-05-25 17:38:48.0','3669960'
'2021-05-25 17:38:49.0','4849590'
'2021-05-25 17:38:50.0','5111730'
'2021-05-25 17:38:51.0','5439405'
'2021-05-25 17:38:52.0','5308335'
'2021-05-25 17:38:53.0','4063170'
'2021-05-25 17:38:54.0','5177265'

This example on a late-2012 iMac (3.2 GHz Quad-Core Intel Core i5) is aggregating just under 5 million rows per second.

NOTE: VALUES CYCLE only supports literal values, not expressions (unlike VALUES which does support expressions).