Values & Functions
Expressions evaluated in isolation or inside larger queries.
Literals
Null
SQL
select null;
Result
null
Boolean
SQL
select true;
Result
true
SQL
select false;
Result
false
Number
SQL
select 1;
Result
1
SQL
select 1.5;
Result
1.5
SQL
select 9007199254740992;
Result
9007199254740992
String
SQL
select 'hello';
Result
"hello"
SQL
select '';
Result
""
SQL
select 'café';
Result
"café"
Array
SQL
select [];
Result
[]
SQL
select [1, 2, 3];
Result
[ 1, 2, 3 ]
SQL
select [1, 'a', null, true];
Result
[ 1, "a", null, true ]
SQL
select [[1, 2], [3, 4]];
Result
[ [ 1, 2 ], [ 3, 4 ] ]
Object
SQL
select {};
Result
{}
SQL
select {x: 1, y: 2};
Result
{ "x": 1, "y": 2 }
SQL
select {items: [1, 2], meta: {n: 2}};
Result
{ "items": [ 1, 2 ], "meta": { "n": 2 } }
Predicates
Is null True
Shows the result of is null true.
SQL
create table T;
insert into T ({});
select null is null from T;
Result
[
true
]
Is null False
Shows the result of is null false.
SQL
create table T;
insert into T ({});
select 1 is null from T;
Result
[
false
]
Is Not null
Shows the result of is not null on null.
SQL
create table T;
insert into T ({});
select null is not null from T;
Result
[
false
]
Is Not null
Shows the result of is not null on value.
SQL
create table T;
insert into T ({});
select 1 is not null from T;
Result
[
true
]
Is null On
Shows the result of is null on null column.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({"x": null});
select s.x is null from S as s;
Result
[
true
]
Is null On
Shows the result of is null on absent key.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({});
select s.x is null from S as s;
Result
[
true
]
Is Not null
Shows the result of is not null on present key.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({"x": 1});
select s.x is not null from S as s;
Result
[
true
]
True Is True
Shows the result of true is true.
SQL
create table T;
insert into T ({});
select true is true from T;
Result
[
true
]
False Is True
Shows the result of false is true.
SQL
create table T;
insert into T ({});
select false is true from T;
Result
[
false
]
null Is True
Shows the result of null is true.
SQL
create table T;
insert into T ({});
select null is true from T;
Result
[
false
]
True Is False
Shows the result of true is false.
SQL
create table T;
insert into T ({});
select true is false from T;
Result
[
false
]
False Is False
Shows the result of false is false.
SQL
create table T;
insert into T ({});
select false is false from T;
Result
[
true
]
null Is False
Shows the result of null is false.
SQL
create table T;
insert into T ({});
select null is false from T;
Result
[
false
]
True Is Unknown
Shows the result of true is unknown.
SQL
create table T;
insert into T ({});
select true is unknown from T;
Result
[
false
]
False Is Unknown
Shows the result of false is unknown.
SQL
create table T;
insert into T ({});
select false is unknown from T;
Result
[
false
]
null Is Unknown
Shows the result of null is unknown.
SQL
create table T;
insert into T ({});
select null is unknown from T;
Result
[
true
]
Not True
Shows the result of true is not true.
SQL
create table T;
insert into T ({});
select true is not true from T;
Result
[
false
]
Not True
Shows the result of null is not true.
SQL
create table T;
insert into T ({});
select null is not true from T;
Result
[
true
]
Not False
Shows the result of null is not false.
SQL
create table T;
insert into T ({});
select null is not false from T;
Result
[
true
]
Not Unknown
Shows the result of null is not unknown.
SQL
create table T;
insert into T ({});
select null is not unknown from T;
Result
[
false
]
Not True
Shows the result of not true.
SQL
create table T;
insert into T ({});
select not true from T;
Result
[
false
]
Not False
Shows the result of not false.
SQL
create table T;
insert into T ({});
select not false from T;
Result
[
true
]
Not null
Shows the result of not null.
SQL
create table T;
insert into T ({});
select not null from T;
Result
[
null
]
Double Not
Shows the result of double not.
SQL
create table T;
insert into T ({});
select not not true from T;
Result
[
true
]
And T T
Shows the result of and t t.
SQL
create table T;
insert into T ({});
select true and true from T;
Result
[
true
]
And T F
Shows the result of and t f.
SQL
create table T;
insert into T ({});
select true and false from T;
Result
[
false
]
And T N
Shows the result of and t n.
SQL
create table T;
insert into T ({});
select true and null from T;
Result
[
null
]
And F T
Shows the result of and f t.
SQL
create table T;
insert into T ({});
select false and true from T;
Result
[
false
]
And F F
Shows the result of and f f.
SQL
create table T;
insert into T ({});
select false and false from T;
Result
[
false
]
False-dominance, False
False-dominance, false AND null is false.
SQL
create table T;
insert into T ({});
select false and null from T;
Result
[
false
]
And N T
Shows the result of and n t.
SQL
create table T;
insert into T ({});
select null and true from T;
Result
[
null
]
False-dominance, Null
False-dominance, null AND false is false.
SQL
create table T;
insert into T ({});
select null and false from T;
Result
[
false
]
And N N
Shows the result of and n n.
SQL
create table T;
insert into T ({});
select null and null from T;
Result
[
null
]
Or T T
Shows the result of or t t.
SQL
create table T;
insert into T ({});
select true or true from T;
Result
[
true
]
Or T F
Shows the result of or t f.
SQL
create table T;
insert into T ({});
select true or false from T;
Result
[
true
]
True-dominance, True
True-dominance, true OR null is true.
SQL
create table T;
insert into T ({});
select true or null from T;
Result
[
true
]
Or F T
Shows the result of or f t.
SQL
create table T;
insert into T ({});
select false or true from T;
Result
[
true
]
Or F F
Shows the result of or f f.
SQL
create table T;
insert into T ({});
select false or false from T;
Result
[
false
]
Or F N
Shows the result of or f n.
SQL
create table T;
insert into T ({});
select false or null from T;
Result
[
null
]
True-dominance, Null
True-dominance, null OR true is true.
SQL
create table T;
insert into T ({});
select null or true from T;
Result
[
true
]
Or N F
Shows the result of or n f.
SQL
create table T;
insert into T ({});
select null or false from T;
Result
[
null
]
Or N N
Shows the result of or n n.
SQL
create table T;
insert into T ({});
select null or null from T;
Result
[
null
]
Than Or
A OR b AND c parses as a OR (b AND c).
SQL
create table T;
insert into T ({});
select false or true and false from T;
Result
[
false
]
NOT A
NOT a AND b parses as (NOT a) AND b.
SQL
create table T;
insert into T ({});
select not false and true from T;
Result
[
true
]
Parens Override Precedence
Shows the result of parens override precedence.
SQL
create table T;
insert into T ({});
select (false or true) and false from T;
Result
[
false
]
Between Inside
Shows the result of between inside.
SQL
create table T;
insert into T ({});
select 5 between 1 and 10 from T;
Result
[
true
]
Between Low Boundary
Shows the result of between low boundary.
SQL
create table T;
insert into T ({});
select 1 between 1 and 10 from T;
Result
[
true
]
Between High Boundary
Shows the result of between high boundary.
SQL
create table T;
insert into T ({});
select 10 between 1 and 10 from T;
Result
[
true
]
Between Below Range
Shows the result of between below range.
SQL
create table T;
insert into T ({});
select 0 between 1 and 10 from T;
Result
[
false
]
Between Above Range
Shows the result of between above range.
SQL
create table T;
insert into T ({});
select 11 between 1 and 10 from T;
Result
[
false
]
Asymmetric BETWEEN,
Asymmetric BETWEEN, a > b yields false.
SQL
create table T;
insert into T ({});
select 5 between 10 and 1 from T;
Result
[
false
]
Not Between Inside
Shows the result of not between inside.
SQL
create table T;
insert into T ({});
select 5 not between 1 and 10 from T;
Result
[
false
]
Not Between Outside
Shows the result of not between outside.
SQL
create table T;
insert into T ({});
select 11 not between 1 and 10 from T;
Result
[
true
]
Between On Column
Shows the result of between on column.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 5});
select n.x between 1 and 10 from N as n;
Result
[
true
]
Strings Order Lexicographically
Strings order lexicographically.
SQL
create table T;
insert into T ({});
select 'a' < 'b' from T;
Result
[
true
]
String Gt
Shows the result of string gt.
SQL
create table T;
insert into T ({});
select 'b' > 'a' from T;
Result
[
true
]
String Ge Equal
Shows the result of string ge equal.
SQL
create table T;
insert into T ({});
select 'a' >= 'a' from T;
Result
[
true
]
Cross-type Ordering
Cross-type ordering is total — a number sorts before a string.
SQL
create table T;
insert into T ({});
select 100 < '20' from T;
Result
[
true
]
Mirror Above
A string sorts after any number (the mirror of the above).
SQL
create table T;
insert into T ({});
select '20' > 100 from T;
Result
[
true
]
Bool Sorts
Bool sorts before number in the total order.
SQL
create table T;
insert into T ({});
select true < 0 from T;
Result
[
true
]
Equality Across
Equality across types is a definite false (not null), no coercion.
SQL
create table T;
insert into T ({});
select 1 = '1' from T;
Result
[
false
]
Arithmetic Coerces
Arithmetic coerces a numeric string operand (to_float).
SQL
create table T;
insert into T ({});
select '5' + 1 from T;
Result
[
6
]
String Errors
A non-numeric string operand is a runtime type error.
SQL
create table T;
insert into T ({});
select 'abc' + 1 from T;
Expected error: runtime
Arith null Propagates
A null operand propagates to null (SQL semantics).
SQL
create table T;
insert into T ({});
select null + 1 from T;
Result
[
null
]
Null Short-circuits
Null short-circuits before the divide-by-zero check.
SQL
create table T;
insert into T ({});
select null / 0 from T;
Result
[
null
]
String Between Inside
Shows the result of string between inside.
SQL
create table T;
insert into T ({});
select 'b' between 'a' and 'c' from T;
Result
[
true
]
String Between Outside
Shows the result of string between outside.
SQL
create table T;
insert into T ({});
select 'd' between 'a' and 'c' from T;
Result
[
false
]
BETWEEN Is
BETWEEN is three-valued — a null subject is unknown, like x >= a and x <= b.
SQL
create table T;
insert into T ({});
select null between 1 and 10 from T;
Result
[
null
]
Bound Unknown
A null bound makes BETWEEN unknown (null), not false.
SQL
create table T;
insert into T ({});
select 5 between 1 and null from T;
Result
[
null
]
In Hit
Shows the result of in hit.
SQL
create table T;
insert into T ({});
select 2 in (1, 2, 3) from T;
Result
[
true
]
IN Is
IN is three-valued — a null target is unknown (null), not false.
SQL
create table T;
insert into T ({});
select null in (1, 2, 3) from T;
Result
[
null
]
No Match
No match but a null element present yields unknown (null).
SQL
create table T;
insert into T ({});
select 9 in (1, null, 3) from T;
Result
[
null
]
Element True
A definite match wins even when a null element is present.
SQL
create table T;
insert into T ({});
select 1 in (1, null, 3) from T;
Result
[
true
]
In Miss
Shows the result of in miss.
SQL
create table T;
insert into T ({});
select 99 in (1, 2, 3) from T;
Result
[
false
]
In Single Element
Shows the result of in single element.
SQL
create table T;
insert into T ({});
select 1 in (1) from T;
Result
[
true
]
In String
Shows the result of in string.
SQL
create table T;
insert into T ({});
select 'bob' in ('alice', 'bob', 'carol') from T;
Result
[
true
]
Not In Hit
Shows the result of not in hit.
SQL
create table T;
insert into T ({});
select 2 not in (1, 2, 3) from T;
Result
[
false
]
Not In Miss
Shows the result of not in miss.
SQL
create table T;
insert into T ({});
select 99 not in (1, 2, 3) from T;
Result
[
true
]
In On Column
Shows the result of in on column.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 2});
select n.x in (1, 2, 3) from N as n;
Result
[
true
]
SQL Three-valued
SQL three-valued logic, null = null is unknown (null).
SQL
create table T;
insert into T ({});
select null = null from T;
Result
[
null
]
SQL Three-valued
SQL three-valued logic, null = 1 is unknown (null).
SQL
create table T;
insert into T ({});
select null = 1 from T;
Result
[
null
]
SQL Three-valued
SQL three-valued logic, null != 1 is unknown (null).
SQL
create table T;
insert into T ({});
select null != 1 from T;
Result
[
null
]
SQL Three-valued
SQL three-valued logic, null != null is unknown (null).
SQL
create table T;
insert into T ({});
select null != null from T;
Result
[
null
]
SQL Three-valued
SQL three-valued logic, ordering with null is unknown (null).
SQL
create table T;
insert into T ({});
select null < 1 from T;
Result
[
null
]
Where Boundary
A null predicate result excludes the row at the where boundary.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 1});
select n.x from N as n where null and n.x > 0;
Result
[]
Includes All
Shows the result of where true includes all.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 1}, {"x": 2});
select n.x from N as n where true or null;
Result
[
1,
2
]
Where And Filters
Shows the result of where and filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 2, "y": 5});
select n.x from N as n where n.x > 1 and n.y > 0;
Result
[
2
]
Where Or Filters
Shows the result of where or filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 3});
select n.x from N as n where n.x = 1 or n.x = 3;
Result
[
3
]
null Filters
Shows the result of where is null filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({"x": 1}, {"x": null}, {"x": 3});
select * from N where N.x is null;
Result
[
{ "x": null }
]
Two Identical
Two identical array literals are equal.
SQL
create table T;
insert into T ({});
select [1] = [1] from T;
Result
[
true
]
Two Distinct
Two distinct array literals are not equal.
SQL
create table T;
insert into T ({});
select [1] = [2] from T;
Result
[
false
]
Ne On
Ne on unequal arrays is true.
SQL
create table T;
insert into T ({});
select [1] != [2] from T;
Result
[
true
]
Ne On
Ne on equal arrays is false.
SQL
create table T;
insert into T ({});
select [1, 2] != [1, 2] from T;
Result
[
false
]
In Where
A non-empty array is truthy in a Where clause.
SQL
create table T;
insert into T ({});
create table W;
insert into W ({"v": 42});
select w.v from W as w where [1];
Result
[
42
]
Casts
Int(x) Over
Int(x) over int, float, string, and bool sources.
SQL
select int(7);
Result
[
7
]
SQL
select int(7);
select int(2.7);
Result
[
2
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
Result
[
-2
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
Result
[
2
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
Result
[
5
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
select int(' 5 ');
Result
[
5
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
select int(' 5 ');
select int('2.7');
Result
[
2
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
select int(' 5 ');
select int('2.7');
select int(true);
Result
[
1
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
select int(' 5 ');
select int('2.7');
select int(true);
select int(false);
Result
[
0
]
SQL
select int(7);
select int(2.7);
select int(-2.7);
select int(2.0);
select int('5');
select int(' 5 ');
select int('2.7');
select int(true);
select int(false);
select int('9007199254740993.0') = 9007199254740993;
Result
[
true
]
Float(x) Over
Float(x) over int, float, string, and bool sources.
SQL
select float(3);
Result
[
3
]
SQL
select float(3);
select float(2.5);
Result
[
2.5
]
SQL
select float(3);
select float(2.5);
select float('1.5');
Result
[
1.5
]
SQL
select float(3);
select float(2.5);
select float('1.5');
select float('4');
Result
[
4
]
SQL
select float(3);
select float(2.5);
select float('1.5');
select float('4');
select float(true);
Result
[
1
]
SQL
select float(3);
select float(2.5);
select float('1.5');
select float('4');
select float(true);
select float(false);
Result
[
0
]
String(x) Renders
String(x) renders a scalar as text.
SQL
select string(42);
Result
[
"42"
]
SQL
select string(42);
select string(1.5);
Result
[
"1.5"
]
SQL
select string(42);
select string(1.5);
select string(true);
Result
[
"true"
]
SQL
select string(42);
select string(1.5);
select string(true);
select string(false);
Result
[
"false"
]
SQL
select string(42);
select string(1.5);
select string(true);
select string(false);
select string('hi');
Result
[
"hi"
]
Bool(x) Over
Bool(x) over numbers and the true/false strings.
SQL
select bool(0);
Result
[
false
]
SQL
select bool(0);
select bool(1);
Result
[
true
]
SQL
select bool(0);
select bool(1);
select bool(5);
Result
[
true
]
SQL
select bool(0);
select bool(1);
select bool(5);
select bool(0.0);
Result
[
false
]
SQL
select bool(0);
select bool(1);
select bool(5);
select bool(0.0);
select bool(2.5);
Result
[
true
]
SQL
select bool(0);
select bool(1);
select bool(5);
select bool(0.0);
select bool(2.5);
select bool('true');
Result
[
true
]
SQL
select bool(0);
select bool(1);
select bool(5);
select bool(0.0);
select bool(2.5);
select bool('true');
select bool('false');
Result
[
false
]
SQL
select bool(0);
select bool(1);
select bool(5);
select bool(0.0);
select bool(2.5);
select bool('true');
select bool('false');
select bool('TRUE');
Result
[
true
]
Number(x) Keeps
Number(x) keeps the int/float-ness of the value.
SQL
select number(7);
Result
[
7
]
SQL
select number(7);
select number(2.5);
Result
[
2.5
]
SQL
select number(7);
select number(2.5);
select number('7');
Result
[
7
]
SQL
select number(7);
select number(2.5);
select number('7');
select number('1.5');
Result
[
1.5
]
SQL
select number(7);
select number(2.5);
select number('7');
select number('1.5');
select number(true);
Result
[
1
]
SQL
select number(7);
select number(2.5);
select number('7');
select number('1.5');
select number(true);
select number('5.0');
Result
[
5
]
Cast Compose
A cast is a normal call — it nests and combines with operators.
SQL
select int('5') + 1;
Result
[
6
]
SQL
select int('5') + 1;
select int(2.9) + 1;
Result
[
3
]
SQL
select int('5') + 1;
select int(2.9) + 1;
select int({"a": 2.9}.a);
Result
[
2
]
SQL
select int('5') + 1;
select int(2.9) + 1;
select int({"a": 2.9}.a);
select int(float('2.7'));
Result
[
2
]
Cast null
A null argument short-circuits to null.
SQL
select int(null);
Result
[
null
]
SQL
select int(null);
select float(null);
Result
[
null
]
SQL
select int(null);
select float(null);
select string(null);
Result
[
null
]
SQL
select int(null);
select float(null);
select string(null);
select bool(null);
Result
[
null
]
SQL
select int(null);
select float(null);
select string(null);
select bool(null);
select number(null);
Result
[
null
]
Typeof(t(x)) Reports
Typeof(t(x)) reports the target type.
SQL
select typeof(int(2.7));
Result
[
"int"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
Result
[
"float"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
Result
[
"int"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
Result
[
"float"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
Result
[
"string"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
select typeof(bool(1));
Result
[
"bool"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
select typeof(bool(1));
select typeof(float('4'));
Result
[
"float"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
select typeof(bool(1));
select typeof(float('4'));
select typeof(number('7'));
Result
[
"int"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
select typeof(bool(1));
select typeof(float('4'));
select typeof(number('7'));
select typeof(number('5.0'));
Result
[
"float"
]
SQL
select typeof(int(2.7));
select typeof(float(5));
select typeof(number(5));
select typeof(number(2.5));
select typeof(string(1));
select typeof(bool(1));
select typeof(float('4'));
select typeof(number('7'));
select typeof(number('5.0'));
select typeof(number(true));
Result
[
"int"
]
Bad Conversions
Bad conversions fail at runtime.
SQL
select int('abc');
Expected error: runtime
SQL
select int('abc');
select int([1]);
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
select int('inf');
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
select int('inf');
select float('nan');
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
select int('inf');
select float('nan');
select float('inf');
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
select int('inf');
select float('nan');
select float('inf');
select string([1, 2]);
Expected error: runtime
SQL
select int('abc');
select int([1]);
select bool('x');
select int(1e19);
select float([1, 2]);
select int('inf');
select float('nan');
select float('inf');
select string([1, 2]);
select string({"a": 1});
Expected error: runtime
Object/array/any Are
Object/array/any are not callable conversion functions.
SQL
select object(1);
Expected error: syntax
SQL
select object(1);
select array(1);
Expected error: syntax
SQL
select object(1);
select array(1);
select any(1);
Expected error: syntax
Functions
Typeof Reports
Typeof reports the value's runtime type name.
SQL
select typeof(1);
Result
[
"int"
]
SQL
select typeof(1);
select typeof(1.5);
Result
[
"float"
]
SQL
select typeof(1);
select typeof(1.5);
select typeof('x');
Result
[
"string"
]
SQL
select typeof(1);
select typeof(1.5);
select typeof('x');
select typeof(true);
Result
[
"bool"
]
SQL
select typeof(1);
select typeof(1.5);
select typeof('x');
select typeof(true);
select typeof(null);
Result
[
"null"
]
SQL
select typeof(1);
select typeof(1.5);
select typeof('x');
select typeof(true);
select typeof(null);
select typeof([1, 2]);
Result
[
"array"
]
SQL
select typeof(1);
select typeof(1.5);
select typeof('x');
select typeof(true);
select typeof(null);
select typeof([1, 2]);
select typeof({"a": 1});
Result
[
"object"
]
Coalesce Returns
Coalesce returns the first non-null argument.
SQL
select coalesce(null, null, 7);
Result
[
7
]
SQL
select coalesce(null, null, 7);
select coalesce(null, 'x');
Result
[
"x"
]
SQL
select coalesce(null, null, 7);
select coalesce(null, 'x');
select coalesce(1, 2);
Result
[
1
]
SQL
select coalesce(null, null, 7);
select coalesce(null, 'x');
select coalesce(1, 2);
select coalesce(null, null);
Result
[
null
]
Nullif Yields
Nullif yields null when the two arguments are equal.
SQL
select nullif(5, 5);
Result
[
null
]
SQL
select nullif(5, 5);
select nullif(5, 3);
Result
[
5
]
Ifnull /
Ifnull / nvl substitute a default for null.
SQL
select ifnull(null, 'd');
Result
[
"d"
]
SQL
select ifnull(null, 'd');
select ifnull('v', 'd');
Result
[
"v"
]
SQL
select ifnull(null, 'd');
select ifnull('v', 'd');
select nvl(null, 9);
Result
[
9
]
Iif Selects
Iif selects a branch on the truthiness of the condition.
SQL
select iif(true, 'y', 'n');
Result
[
"y"
]
SQL
select iif(true, 'y', 'n');
select iif(1 = 2, 'y', 'n');
Result
[
"n"
]
SQL
select iif(true, 'y', 'n');
select iif(1 = 2, 'y', 'n');
select iif(null, 'y', 'n');
Result
[
"n"
]
Abs Of
Abs of ints and floats.
SQL
select abs(0 - 5);
Result
[
5
]
SQL
select abs(0 - 5);
select abs(5);
Result
[
5
]
SQL
select abs(0 - 5);
select abs(5);
select abs(0.0 - 2.5);
Result
[
2.5
]
Ceil, Floor,
Ceil, floor, and trunc round floats; ints pass through.
SQL
select ceil(3.2);
Result
[
4
]
SQL
select ceil(3.2);
select ceiling(3.2);
Result
[
4
]
SQL
select ceil(3.2);
select ceiling(3.2);
select floor(3.8);
Result
[
3
]
SQL
select ceil(3.2);
select ceiling(3.2);
select floor(3.8);
select trunc(3.9);
Result
[
3
]
SQL
select ceil(3.2);
select ceiling(3.2);
select floor(3.8);
select trunc(3.9);
select ceil(7);
Result
[
7
]
Round To
Round to nearest integer, and to n decimals.
SQL
select round(3.7);
Result
[
4
]
SQL
select round(3.7);
select round(3.14159, 0);
Result
[
3
]
SQL
select round(3.7);
select round(3.14159, 0);
select round(5);
Result
[
5
]
Sign Returns
Sign returns -1, 0, or 1.
SQL
select sign(0 - 5);
Result
[
-1
]
SQL
select sign(0 - 5);
select sign(5);
Result
[
1
]
SQL
select sign(0 - 5);
select sign(5);
select sign(0);
Result
[
0
]
Sqrt And
Sqrt and pow (integer powers stay integers).
SQL
select sqrt(9);
Result
[
3
]
SQL
select sqrt(9);
select pow(2, 10);
Result
[
1024
]
SQL
select sqrt(9);
select pow(2, 10);
select power(3, 2);
Result
[
9
]
Exp, Ln,
Exp, ln, and log10 at exact points.
SQL
select exp(0);
Result
[
1
]
SQL
select exp(0);
select ln(1);
Result
[
0
]
SQL
select exp(0);
select ln(1);
select log10(1);
Result
[
0
]
Mod Is
Mod is the integer remainder.
SQL
select mod(7, 3);
Result
[
1
]
SQL
select mod(7, 3);
select mod(10, 5);
Result
[
0
]
Greatest And
Greatest and least over numbers and strings.
SQL
select greatest(1, 5, 3);
Result
[
5
]
SQL
select greatest(1, 5, 3);
select least(1, 5, 3);
Result
[
1
]
SQL
select greatest(1, 5, 3);
select least(1, 5, 3);
select greatest('a', 'c', 'b');
Result
[
"c"
]
Length Dispatches
Length dispatches on value — chars, elements, or members.
SQL
select length('hello');
Result
[
5
]
SQL
select length('hello');
select length([1, 2, 3]);
Result
[
3
]
SQL
select length('hello');
select length([1, 2, 3]);
select length({"x": 1, "y": 2});
Result
[
2
]
SQL
select length('hello');
select length([1, 2, 3]);
select length({"x": 1, "y": 2});
select length('café');
Result
[
4
]
Upper And
Upper and lower case strings.
SQL
select upper('hi');
Result
[
"HI"
]
SQL
select upper('hi');
select lower('HI');
Result
[
"hi"
]
Trim, Ltrim,
Trim, ltrim, and rtrim strip surrounding whitespace.
SQL
select trim(' hi ');
Result
[
"hi"
]
SQL
select trim(' hi ');
select ltrim(' hi');
Result
[
"hi"
]
SQL
select trim(' hi ');
select ltrim(' hi');
select rtrim('hi ');
Result
[
"hi"
]
Substr Is
Substr is 1-based, with an optional length.
SQL
select substr('hello', 2);
Result
[
"ello"
]
SQL
select substr('hello', 2);
select substr('hello', 2, 3);
Result
[
"ell"
]
SQL
select substr('hello', 2);
select substr('hello', 2, 3);
select substring('hello', 1, 1);
Result
[
"h"
]
Replace Swaps
Replace swaps every occurrence of a substring.
SQL
select replace('aXbXc', 'X', '-');
Result
[
"a-b-c"
]
Concat Joins
Concat joins arguments, skipping nulls, stringifying scalars.
SQL
select concat('a', 'b', 'c');
Result
[
"abc"
]
SQL
select concat('a', 'b', 'c');
select concat('a', null, 'b');
Result
[
"ab"
]
SQL
select concat('a', 'b', 'c');
select concat('a', null, 'b');
select concat('n', 42);
Result
[
"n42"
]
Concat_ws Joins
Concat_ws joins with a separator, skipping nulls.
SQL
select concat_ws('-', 'a', 'b', 'c');
Result
[
"a-b-c"
]
SQL
select concat_ws('-', 'a', 'b', 'c');
select concat_ws(',', 'a', null, 'b');
Result
[
"a,b"
]
Repeat Duplicates
Repeat duplicates a string; reverse is dynamic on value.
SQL
select repeat('ab', 3);
Result
[
"ababab"
]
SQL
select repeat('ab', 3);
select reverse('abc');
Result
[
"cba"
]
SQL
select repeat('ab', 3);
select reverse('abc');
select reverse([1, 2, 3]);
Result
[
[ 3, 2, 1 ]
]
Lpad And
Lpad and rpad pad to a target width with a fill string.
SQL
select lpad('5', 3, '0');
Result
[
"005"
]
SQL
select lpad('5', 3, '0');
select rpad('5', 3, '0');
Result
[
"500"
]
SQL
select lpad('5', 3, '0');
select rpad('5', 3, '0');
select lpad('x', 3);
Result
[
" x"
]
Repeat /
Repeat / lpad reject pathological sizes instead of exhausting memory.
SQL
select repeat('x', 99999999999);
Expected error: runtime
SQL
select repeat('x', 99999999999);
select lpad('x', 99999999999);
Expected error: runtime
Strpos /
Strpos / instr return a 1-based index, 0 if absent.
SQL
select strpos('hello', 'l');
Result
[
3
]
SQL
select strpos('hello', 'l');
select instr('hello', 'l');
Result
[
3
]
SQL
select strpos('hello', 'l');
select instr('hello', 'l');
select strpos('hello', 'z');
Result
[
0
]
Starts_with, Ends_with,
Starts_with, ends_with, contains (dynamic on value).
SQL
select starts_with('hello', 'he');
Result
[
true
]
SQL
select starts_with('hello', 'he');
select ends_with('hello', 'lo');
Result
[
true
]
SQL
select starts_with('hello', 'he');
select ends_with('hello', 'lo');
select contains('hello', 'ell');
Result
[
true
]
SQL
select starts_with('hello', 'he');
select ends_with('hello', 'lo');
select contains('hello', 'ell');
select contains([1, 2, 3], 2);
Result
[
true
]
Array_length, Array_contains, Array_position
Array_length, array_contains, array_position.
SQL
select array_length([1, 2, 3]);
Result
[
3
]
SQL
select array_length([1, 2, 3]);
select array_contains([1, 2, 3], 2);
Result
[
true
]
SQL
select array_length([1, 2, 3]);
select array_contains([1, 2, 3], 2);
select array_position([10, 20, 30], 20);
Result
[
2
]
SQL
select array_length([1, 2, 3]);
select array_contains([1, 2, 3], 2);
select array_position([10, 20, 30], 20);
select array_position([10, 20], 99);
Result
[
0
]
Array_append, Array_prepend, Array_concat
Array_append, array_prepend, array_concat.
SQL
select array_append([1, 2], 3);
Result
[
[ 1, 2, 3 ]
]
SQL
select array_append([1, 2], 3);
select array_prepend(0, [1, 2]);
Result
[
[ 0, 1, 2 ]
]
SQL
select array_append([1, 2], 3);
select array_prepend(0, [1, 2]);
select array_concat([1, 2], [3, 4]);
Result
[
[ 1, 2, 3, 4 ]
]
Array_reverse, Array_distinct,
Array_reverse, array_distinct, array_slice, array_to_string.
SQL
select array_reverse([1, 2, 3]);
Result
[
[ 3, 2, 1 ]
]
SQL
select array_reverse([1, 2, 3]);
select array_distinct([1, 2, 2, 3, 1]);
Result
[
[ 1, 2, 3 ]
]
SQL
select array_reverse([1, 2, 3]);
select array_distinct([1, 2, 2, 3, 1]);
select array_slice([1, 2, 3, 4, 5], 2, 4);
Result
[
[ 2, 3, 4 ]
]
SQL
select array_reverse([1, 2, 3]);
select array_distinct([1, 2, 2, 3, 1]);
select array_slice([1, 2, 3, 4, 5], 2, 4);
select array_to_string([1, 2, 3], '-');
Result
[
"1-2-3"
]
Object_keys, Object_values, Object_has_key
Object_keys, object_values, object_has_key.
SQL
select object_keys({"a": 1, "b": 2});
Result
[
[ "a", "b" ]
]
SQL
select object_keys({"a": 1, "b": 2});
select object_values({"a": 1, "b": 2});
Result
[
[ 1, 2 ]
]
SQL
select object_keys({"a": 1, "b": 2});
select object_values({"a": 1, "b": 2});
select object_has_key({"a": 1}, 'a');
Result
[
true
]
SQL
select object_keys({"a": 1, "b": 2});
select object_values({"a": 1, "b": 2});
select object_has_key({"a": 1}, 'a');
select object_has_key({"a": 1}, 'z');
Result
[
false
]
Fn null Propagation
A null argument to a strict function yields null.
SQL
select abs(null);
Result
[
null
]
SQL
select abs(null);
select upper(null);
Result
[
null
]
SQL
select abs(null);
select upper(null);
select length(null);
Result
[
null
]
SQL
select abs(null);
select upper(null);
select length(null);
select round(null, 2);
Result
[
null
]
SQL
select abs(null);
select upper(null);
select length(null);
select round(null, 2);
select array_length(null);
Result
[
null
]
Wrong Argument
Wrong argument count is a static error.
SQL
select abs(1, 2);
Expected error: static
SQL
select abs(1, 2);
select upper();
Expected error: static
An Undefined
An undefined function name is a static error.
SQL
select bogus(1);
Expected error: static
Fn Type Error
A wrong-typed argument is a runtime error.
SQL
select abs('x');
Expected error: runtime
SQL
select abs('x');
select upper(42);
Expected error: runtime