Wildcard Rules in StreamSQL SELECT Statements

Introduction

A StreamSQL SELECT statement begins with a target list that identifies fields in the result set. Each field in the target list can be extracted from a stream or connected data source such as a tuple field, a table entry in a data construct, or the results of a simple or aggregate function. A target list entry can contain one or more asterisk wildcards in place of explicit targets, providing a convenient shorthand selection method.

The SELECT Statement topic introduces wildcard rules in the context of the whole statement, including its general syntax: 

wildcard_expression [AS wildcard_target]

This topic provides more details about and examples of using wildcard rule semantics.

Wildcard Rule Expansion

Every wildcard rule expands to a list of standard (non-wildcard) rules, containing one entry for each field in the wildcard rule's context. That is, each asterisk token in the wildcard expression and wildcard target clauses is replaced with a field name in the referenced stream. 

CREATE INPUT STREAM FOO (x int, y int, z int);
SELECT f(*) AS f_* FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM FOO (x int, y int, z int);
SELECT f(x) AS f_x, f(y) AS f_y, f(z) AS f_z FROM foo INTO bar;

Notice in this example:

  • The wildcards in the expression and target select for all the fields in the input stream.

  • The expanded target list contains multiple, comma-separated target list items, one for each field that the expression selected. The expanded list items are standard rules.

This example demonstrates the implicit wildcard target, where we omit the AS keyword and target: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT f(*) + g(*) FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT f(x) + g(x) AS x, f(y) + g(y) AS y, f(z) + g(z) AS z 
  FROM foo INTO bar;

Notice how the expansion automatically creates targets by default.

Using Wildcards to Pass Arguments to Functions

Wildcard rules are legal anywhere that a list of expressions is legal. Thus they can also be used to pass arguments to functions. To force an expression list to expand as function arguments, enclose the expression wildcard, AS keyword, and target wildcard in parentheses. 

CREATE INPUT STREAM foo (int x, int y, int z);
SELECT min(* AS *) AS min FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT min(x AS x, y AS y, z AS z) AS min FROM foo INTO bar;

Caution

You must specify the wildcard target when you pass arguments to functions. If you omit it, the implicit wildcat target always appears at the highest possible level, which is not the intended result.

Consider this example: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT min(*) AS min FROM foo INTO bar;

Equivalent to: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT min(*) AS min AS * FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM foo (x int, y int, z int);
SELECT min(x) AS min AS x, min(y) AS min AS y, min(z) AS min AS z 
  FROM foo INTO bar;

Because the min function does not take one argument, this expansion results in an error.

Using Wildcards with Hierarchical Data

Hierarchical data includes nested tuples and anonymous schemas. When you use wildcards with streams that contain hierarchical data, the result list will contain one entry for each field in the qualified context. It is illegal to have a wildcard rule with multiple qualifiers, even if the qualified contexts for them contain the same field names. 

CREATE INPUT STREAM tagged_point (tag string, point (x double, y double));
SELECT f(point.*) AS f_* FROM tagged_point INTO bar;

Expands to: 

CREATE INPUT STREAM tagged_point (tag string, point (x double, y double));
SELECT f(point.x) AS f_x, f(point.y) AS f_y FROM tagged_point INTO bar;

CREATE INPUT STREAM tagged_point (tag string, point (x double, y double));
SELECT point.* AS * FROM tagged_point INTO bar;

Expands to: 

CREATE INPUT STREAM tagged_point (tag string, point (x double, y double));
SELECT point.x AS x, point.y AS y FROM tagged_point INTO bar;

This example is not valid: 

CREATE INPUT STREAM double_point (p1 (x double, y double), p2 (x double, y double));
SELECT p1.* + p2.* AS * FROM double_point INTO bar;

Target Binding and Precedence

The asterisk token in a wildcard target binds more tightly than multiplication. For example: 

x AS foo * y

binds as: 

x AS (foo*y)

This is an error because the wildcard expression x does not contain any * variables. To use multiplication, use parentheses: 

(x AS foo) * y

Note

The AS foo expression illustrates our point, but in fact does nothing.

The comma tokens in function argument lists and SELECT target lists have different precedence levels with respect to insertion of default wildcard targets. To see the difference, compare the expansion in the next two examples: 

CREATE INPUT STREAM foo (x int, y int, z int); 
SELECT *, 1 AS a FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM foo (x int, y int, z int); 
SELECT x AS x, y AS y, z AS z, 1 AS a FROM foo INTO bar; 

CREATE INPUT STREAM foo (x int, y int, z int); 
SELECT f(*, 1 AS a) FROM foo INTO bar;

Expands to: 

CREATE INPUT STREAM foo (x int, y int, z int); 
SELECT f(x, 1 AS a) AS x, f(y, 1 AS a) AS y, f(z, 1 AS a) AS Z 
  FROM foo INTO bar;

Related Topics

Back to Top ^