Skip to main content

Keyword-Led Argument Lists

Some constructs are spelled like a function call but are not one: a keyword (or a keyword phrase) followed directly by a parenthesised argument list. STRUCT(1 AS a), WEEK(MONDAY), VARCHAR(MAX), IDENTITY(1, 1), FixedString(16). The idiom in every one of these is a tight paren, exactly as for count(*), and dfmt keeps it tight.

Behavior: The paren glues to the keyword that owns it. The argument list inside lays out as any other argument list — it may still break across lines when it is too long.

Not affected: keywords whose parenthesised operand reads with a space in ordinary SQL keep the space: IN (a, b), VALUES (1, 2), OVER (PARTITION BY x), FILTER (WHERE …), WITHIN GROUP (ORDER BY …), PRIMARY KEY (id), USING (id), LIKE ANY (…).

BigQuery STRUCT constructor​

select struct(1 as a, 2 as b) as s, array_agg(struct(id, ts)) as ids from t
select struct(1 as a, 2 as b) as s, array_agg(struct(id, ts)) as ids from t

BigQuery typed and empty-typed STRUCT constructor​

select struct<int64, string>(1, "x") as t, struct<>() as e from t
select struct<int64, string>(1, "x") as t, struct<>() as e from t

BigQuery WEEK(weekday) date part​

The bare week(monday) is only ever seen as a date part inside another function, so the two forms below are the same node in two positions.

select date_trunc(d, week(monday)), extract(week(sunday) from d) from t
select date_trunc(d, week(monday)), extract(week(sunday) from d) from t

BigQuery WEEK(weekday) as a function argument​

select date_diff(a, b, week(monday)) as w3 from t
select date_diff(a, b, week(monday)) as w3 from t

BigQuery NEW proto constructor​

select new proto.Type(1 as a, 2 as b) as p from t
select new proto.Type(1 as a, 2 as b) as p from t

BigQuery TABLE argument with a call​

select * from my_tvf(table my_table(1, 2))
select * from my_tvf(table my_table(1, 2))

BigQuery WITH REPORT options​

select anon_count(* with report(format = json)) from t
select anon_count(* with report(format = json)) from t

T-SQL string type lengths, including MAX​

select cast(x as varchar(50)), cast(y as nvarchar(max)) from t
select cast(x as varchar(50)), cast(y as nvarchar(max)) from t

T-SQL binary type lengths, including MAX​

select cast(x as varbinary(max)), cast(y as varbinary(16)) from t
select cast(x as varbinary(max)), cast(y as varbinary(16)) from t

T-SQL numeric type precision​

select cast(x as decimal(10, 2)), cast(y as float(53)), cast(z as numeric(max))
select cast(x as decimal(10, 2)), cast(y as float(53)), cast(z as numeric(max))

T-SQL IDENTITY seed and increment​

create table t (id int identity(1, 1) not null, name varchar(50))
create table t(id int identity(1, 1) not null, name varchar(50))

Redshift VARCHAR length and MAX​

select cast(null as varchar(255)) as a, cast(x as varchar(max)) as b from t
select cast(null as varchar(255)) as a, cast(x as varchar(max)) as b from t

Redshift GENERATED AS IDENTITY seed and step​

create table t (id int generated by default as identity(0, 1), n varchar(20))
create table t(id int generated by default as identity(0, 1), n varchar(20))

MariaDB CONVERT target types​

select convert(x, char(10)), convert(x, decimal(10, 2)), convert(x, datetime(6))
select convert(x, char(10)), convert(x, decimal(10, 2)), convert(x, datetime(6))

MariaDB WEIGHT_STRING AS type​

select weight_string(s as char(10)), weight_string(b as binary(4)) from t
select weight_string(s as char(10)), weight_string(b as binary(4)) from t

ClickHouse FixedString length​

create table t (s fixedstring(8), n Nullable(fixedstring(4))) engine = Memory
create table t(s fixedstring(8), n Nullable(fixedstring(4))) engine = Memory

Doris sized numeric and datetime types​

create table t (a largeint(20), b decimalv3(10, 2), c datetimev2(3), d decimal128(20, 4)) distributed by hash(a) buckets 10
create table t (
    a largeint(20),
    b decimalv3(10, 2),
    c datetimev2(3),
    d decimal128(20, 4)
)
distributed by hash(a) buckets 10

TiDB AUTO_RANDOM and VECTOR arguments​

create table t (id bigint auto_random(5) primary key, v vector(3))
create table t(id bigint auto_random(5) primary key, v vector(3))

Db2 CURRENT TIMESTAMP precision​

select current timestamp(12), current timestamp, current date from sysibm.sysdummy1
select current timestamp(12), current timestamp, current date
from sysibm.sysdummy1

Angle-bracket type parameters hug their brackets​

A generic type's parameter list is bracketed by < and >, and it hugs them exactly as an argument list hugs its parens: struct<a int64>, never struct < a int64 >.

select cast(x as struct<a int64, b string>) as s, cast(y as array<int64>) as a
select cast(x as struct<a int64, b string>) as s, cast(y as array<int64>) as a

Typed and untyped ARRAY literals hug their brackets​

select array<int64>[1, 2] as a, array[1, 2] as b, array<struct<x int64>>[] as c
select array<int64>[1, 2] as a, array[1, 2] as b, array<struct<x int64>>[] as c

Nested generic types​

select cast(x as map<string, array<struct<a: int, b: string>>>) as m from t
select cast(x as map<string, array<struct<a: int, b: string>>>) as m from t

Comparison operators are not brackets​

The same two characters as operators keep their spaces on both sides.

select a<b, c>d, e <= f from t where x<y and y>z
select a < b, c > d, e <= f from t where x < y and y > z

A not-equal written as two tokens is still an operator​

Oracle accepts < > for <>. The two characters are one operator, not a bracket pair around nothing, so the comparison keeps a space on each side and the operator closes up.

select 1 from t where a < > b and c <> d
select 1 from t where a <> b and c <> d