This document describes built-in functions and operators.
Function | Description | Example Input | Output |
---|---|---|---|
boolean(x) | cast x to Boolean | boolean('true') | true |
boolean('false') | false | ||
boolean(string('HTSQL')) | true | ||
boolean(string('')) | false | ||
true() | logical TRUE value | true() | |
false() | logical FALSE value | false() | |
p & q | logical AND operator | true()&true() | true |
true()&false() | false | ||
false()&false() | false | ||
p | q | logical OR operator | true()|true() | true |
true()|false() | true | ||
false()|false() | false | ||
!p | logical NOT operator | !true() | false |
!false() | true | ||
null(x) | NULL value | null() | |
is_null(x) | x is null | is_null(null()) | true |
if_null(x,y) | x if x is not null; y otherwise | if_null(1,0) | 1 |
if_null(null(),0) | 0 | ||
null_if(x,y) | x if x is not equal to y; null otherwise | null_if(1,0) | 1 |
null_if(0,0) | null | ||
x = y | x is equal to y | 'HTSQL'='QUEL' | false |
x != y | x is not equal to y | 'HTSQL'!='QUEL' | true |
x == y | x is equal to y; treats nulls as regular values | 'HTSQL'=='QUEL' | false |
2==null() | false | ||
x !== y | x is not equal to y; treats nulls as regular values | 'HTSQL'!=='QUEL' | true |
2!==null() | true | ||
x = {a,b,c,...} | x is among a, b, c, ... | 5={2,3,5,7}' | true |
x != {a,b,c,...} | x is not among a, b, c, ... | 5!={2,3,5,7}' | false |
x < y | x is less than y | 1<10 | true |
'omega'<'alpha' | false | ||
x <= y | x is less than or equal to y | 1<=10 | true |
'omega'<='alpha' | false | ||
x > y | x is greater than y | 1>10 | false |
'omega'>'alpha' | true | ||
x >= y | x is greater than or equal to y | 1>=10 | false |
'omega'>='alpha' | true | ||
if(p1,c1,...,pn,cn) | first ck such that pk is TRUE; o or null otherwise | if(true(),'up','down') | 'up' |
if(p1,c1,...,pn,cn,o) | if(false(),'up','down') | 'down' | |
switch(x,y1,c1,...,yn,cn) | first ck such that x is equal to yk; o or null otherwise | switch(1,1,'up',0,'down') | 'up' |
switch(x,y1,c1,...,yn,cn,o) | switch(0,1,'up',0,'down') | 'down' |
The result of the conversion depends on the type of the argument:
‘false’ | ‘true’ |
---|---|
false | true |
null() | false() | true() |
---|---|---|
— | false | true |
null() | ‘’ | ‘HTSQL’ |
---|---|---|
false | false | true |
null() | 0.0 | ‘2010-04-15’ |
---|---|---|
false | true | true |
Arguments of a logical operators that are not of a Boolean type automatically converted to Boolean (see boolean() function).
true()|true() | true()|false() | false()|true() | false()|false() |
---|---|---|---|
true | true | true | false |
true()&true() | true()&false() | false()&true() | false()&false() |
---|---|---|---|
true | false | false | false |
!true() | !false() |
---|---|
false | true |
true()&null() | false()&null() | null()&null() | true()|null() | false()|null() | null()|null() | !null() |
---|---|---|---|---|---|---|
— | false | — | true | — | — | — |
code | name | campus |
---|---|---|
art | School of Art and Design | old |
bus | School of Business | south |
edu | College of Education | old |
… | … | … |
The arguments of if_null() and null_if() should be of the same type; if not, the arguments are coerced to the most general type.
[-]/{null()}
null() |
---|
— |
is_null(null()) | is_null(0) |
---|---|
true | false |
if_null(‘SQL’,’HTSQL’) | if_null(null(),’HTSQL’) |
---|---|
SQL | HTSQL |
null_if(‘HTSQL’,’SQL’) | null_if(‘SQL’,’SQL’) |
---|---|
HTSQL | — |
title | credits |
---|---|
Classroom Visit | — |
Spring Basket Weaving Workshop | — |
title | credits |
---|---|
Classroom Visit | — |
Spring Basket Weaving Workshop | — |
Peer Portfolio Review | 0 |
title | credits |
---|---|
Classroom Visit | — |
Spring Basket Weaving Workshop | — |
Peer Portfolio Review | 0 |
The form x = {a,b,c,...} is a short-cut syntax for x=a|x=b|x=c|.... Similarly, the form x != {a,b,c,...} is a short-cut syntax for x!=a|x!=b|x!=c|....
The operands of equality operators are expected to be of the same time. If the types of the operands are different, the operands are coerced to the most general type; it is an error if the operand types are not compatible to each other.
1=1.0 | ‘HTSQL’!=’SQUARE’ |
---|---|
true | true |
0!=null() | null()=null() | 0!==null() | null()==null() |
---|---|---|---|
— | — | true | true |
‘HTSQL’!={‘ISBL’,’SQUARE’,’QUEL’} |
---|
true |
code | name | campus |
---|---|---|
art | School of Art and Design | old |
edu | College of Education | old |
la | School of Arts and Humanities | old |
… | … | … |
code | name | campus |
---|---|---|
art | School of Art and Design | old |
edu | College of Education | old |
la | School of Arts and Humanities | old |
… | … | … |
code | campus==’old’ | campus==’north’ | campus==’south’ |
---|---|---|---|
art | true | false | false |
bus | false | false | true |
edu | true | false | false |
… | … | … | … |
The result is NULL if any of the operands is NULL.
An operand of a comparison operator must be of a string, numeric, enumeration, or date/time type. Both operands are expected to be of the same type; if not, the operands are coerced to the most general type.
23<=17.5 | ‘HTSQL’<’SQUARE’ | date(‘2010-04-15’)>=date(‘1991-08-20’) |
---|---|---|
false | true | true |
code | name | campus |
---|---|---|
eng | School of Engineering | north |
la | School of Arts and Humanities | old |
mus | School of Music & Dance | south |
… | … | … |
These functions expect all the resulting values c1,c2,...,cN as well as the default value o to be of the same type. If the value types are different, all values are coerced to the most general type. Same is true for the control expression x and variant values y1,y2,...,yN of the function switch().
title | level |
---|---|
General Astronomy I | hard |
General Astronomy I Lab | easy |
Stars and Planets | medium |
… | … |
name | sex_code |
---|---|
Sheri Sanchez | -1 |
Anna Carroll | -1 |
Alphonse Gilmore | 1 |
… | … |
Function | Description | Example Input | Output |
---|---|---|---|
integer(x) | cast x to integer | integer('60') | 60 |
integer(17.25) | 17 | ||
integer(string('60')) | 60 | ||
decimal(x) | cast x to decimal | decimal('17.25') | 17.25 |
decimal(223607e-5) | 2.23607 | ||
decimal(string('17.25')) | 17.25 | ||
float(x) | cast x to float | float('223607e-5') | 223607e-5 |
float(60) | 6e1 | ||
float(string('223607e-5')) | 223607e-5 | ||
+ x | x | +60 | |
- x | negate x | -7 | |
x + y | add x to y | 13+7 | 20 |
x - y | subtract y from x | 13-7 | 6 |
x * y | multiply x by y | 13*7 | 91 |
x / y | divide x by y | 13/7 | 1.85714285714286 |
round(x) | round x to the nearest integer | round(17.25) | 17 |
round(x,n) | round x to n decimal places | round(17.25,1) | 17.3 |
trunc(x) | round x to an integer, towards zero | trunc(17.25) | 17 |
trunc(x,n) | round x to n decimal places, towards zero | trunc(17.25,1) | 17.2 |
The argument of a conversion function can be of one of the following types:
2.125 | ‘271828e-5’ | 60 |
---|---|---|
2 | 2.71828 | 60.0 |
Arithmetic operators expect operands of a numeric type. If the operands are of different types, they are coerced to the most general type, in the order: integer, decimal, float. For instance, adding an integer value to a decimal value converts the integer operand to decimal; multiplying a decimal value to a float value converts the decimal operand to float.
In general, the type of the result coincides with the type of the operands. The only exception is the division operator: when applied to integer operands, division produces a decimal value.
The behavior of arithmetic expressions on range overflow or division by zero is backend-dependent: different backends may raise an error, return a NULL value or generate an incorrect result.
Note that some arithmetic operators are also defined for string and date values; they are described in respective sections.
(2+4)*7 | -(98-140) | 21/5 |
---|---|---|
42 | 42 | 4.2 |
If called with one argument, the functions accept values of decimal or float types and return a value of the same type.
When called with two arguments, the functions expects a decimal argument and produces a decimal value. The second argument should be an integer; negative values are permitted.
round(3272.78125) | round(3272.78125,2) | round(3272.78125,-2) |
---|---|---|
3273 | 3272.78 | 3300 |
trunc(3272.78125) | trunc(3272.78125,2) | trunc(3272.78125,-2) |
---|---|---|
3272 | 3272.78 | 3200 |
code | avg(department.count(course)):round(2) |
---|---|
art | 19.5 |
bus | 14.67 |
edu | 17.5 |
… | … |
avg_credits | count(department) |
---|---|
2.9 | 1 |
3.0 | 4 |
3.1 | 1 |
3.2 | 3 |
3.3 | 2 |
3.4 | 1 |
3.5 | 5 |
3.6 | 1 |
3.7 | 1 |
4.0 | 1 |
By convention, string functions take a string as its first parameter. When an untyped literal, such as 'value' is used and a string is expected, it is automatically cast. Hence, for convenience, we write string typed values using single quotes in the output column.
Function | Description | Example Input | Output |
---|---|---|---|
string(x) | cast x to string | string('Hello') | 'Hello' |
string(1.0) | '1.0' | ||
string(date('2010-04-15')) | '2010-04-15' | ||
length(s) | number of characters in s | length('HTSQL') | 5 |
s + t | concatenate s and t | 'HT' + 'SQL' | 'HTSQL' |
s ~ t | s contains t; case-insensitive | 'HTSQL' ~ 'sql' | true |
s !~ t | s does not contain t; case-insensitive | 'HTSQL' !~ 'sql' | false |
head(s) | first character of s | head('HTSQL') | 'H' |
head(s,n) | first n characters of s | head('HTSQL',2) | 'HT' |
head('HTSQL',-3) | 'HT' | ||
tail(s) | last character of s | tail('HTSQL') | 'L' |
tail(s,n) | last n characters of s | tail('HTSQL',3) | 'SQL' |
tail('HTSQL',-2) | 'SQL' | ||
slice(s,i,j) | i-th to j-th characters of s; null or missing index means the beginning or the end of the string | slice('HTSQL',1,4) | 'TSQ' |
slice('HTSQL',-4,-1) | 'TSQ' | ||
slice('HTSQL',null(),2) | 'HT' | ||
slice('HTSQL',2,null()) | 'SQL' | ||
at(s,k) | k-th character of s | at('HTSQL',2) | 'S' |
at(s,k,n) | n characters of s starting with k-th character | at('HTSQL',1,3) | 'TSQ' |
at('HTSQL,-4,3) | 'TSQ' | ||
at('HTSQL,4,-3) | 'TSQ' | ||
upper(s) | upper case of s | upper('htsql') | 'HTSQL' |
lower(s) | lower case of s | lower('HTSQL') | 'htsql' |
trim(s) | strip leading and trailing spaces from s | trim(' HTSQL ') | 'HTSQL' |
ltrim(s) | strip leading spaces from s | ltrim(' HTSQL ') | 'HTSQL ' |
rtrim(s) | strips trailing spaces from s | rtrim(' HTSQL ') | ' HTSQL' |
replace(s,t,r) | replace all occurences of t in s with r | replace('HTSQL','SQL','RAF') | 'HTRAF' |
HTSQL permits any value to be converted to a string; the conversion respects the format for literals of the original type.
‘HTSQL’ | true() | 2.125 | ‘2010-04-15 20:13’ |
---|---|---|---|
HTSQL | true | 2.125 | 2010-04-15 20:13:00 |
text |
---|
Department of Accounting offers 12 courses |
Department of Art History offers 20 courses |
Department of Astronomy offers 22 courses |
… |
The exact meaning of a string length depends on the backend and the underlying SQL type. The function returns 0 if the argument is NULL.
length(‘HTSQL’) | length(‘’) | length(null()) |
---|---|---|
5 | 0 | 0 |
The concatenation operator treats a NULL operand as an empty string.
‘HT’+’SQL’ | null()+’SQL’ |
---|---|
HTSQL | SQL |
code | title |
---|---|
acc.100 | Practical Bookkeeping |
acc.200 | Introduction to Accounting |
acc.234 | Accounting Information Systems |
… | … |
The search functions are case-insensitive; exact rules for case-insensitivity depend on the backend.
‘HTSQL’~’sql’ | ‘sql’!~’HTSQL’ |
---|---|
true | true |
code | name | campus |
---|---|---|
art | School of Art and Design | old |
In HTSQL, characters of a string are indexed from 0.
Extraction functions permit negative or NULL indexes. head() (tail()), when given a negative n, produces all but the last (first) -n characters of s; if n is NULL, it is assumed to be 1.
For slice(), a negative index i or j indicates to count (-i-1)-th ((-j-1)-th) character from the end of s. NULL value for i or j indicates the beginning (the end) of the string.
For at(), a negative n produces -n characters of s ending at the k-th character; if n is NULL, it is assumed to be 1.
‘HTSQL’:head | ‘HTSQL’:head(2) | ‘HTSQL’:head(-3) |
---|---|---|
H | HT | HT |
‘HTSQL’:tail | ‘HTSQL’:tail(3) | ‘HTSQL’:tail(-2) |
---|---|---|
L | SQL | SQL |
‘HTSQL’:slice(1,-1) | ‘HTSQL’:slice(1,null()) | ‘HTSQL’:slice(null(),-1) |
---|---|---|
TSQ | TSQL | HTSQ |
‘HTSQL’:at(2) | ‘HTSQL’:at(1,3) | ‘HTSQL’:at(-1,-3) |
---|---|---|
S | TSQ | TSQ |
The conversion semantics is backend-dependent.
‘htsql’:upper | ‘HTSQL’:lower |
---|---|
HTSQL | htsql |
‘ HTSQL ‘:trim:replace(‘ ‘,’!’) | ‘ HTSQL ‘:ltrim:replace(‘ ‘,’!’) | ‘ HTSQL ‘:rtrim:replace(‘ ‘,’!’) |
---|---|---|
HTSQL | HTSQL!! | !!HTSQL |
Case-sensitivity of the search depends on the backend; NULL values for t and r are interpreted as an empty string.
‘HTTP’:replace(‘TP’,’SQL’) | ‘HTTP’:replace(null(),’SQL’) | ‘HTTP’:replace(‘TP’,null()) |
---|---|---|
HTSQL | HTTP | HT |
Function | Description | Example Input | Output |
---|---|---|---|
date(x) | cast x to date | date('2010-04-15') | |
time(x) | cast x to time | time('20:13') | |
datetime(x) | cast x to datetime | datetime('2010-04-15T20:13') | |
date(yyyy,mm,dd) | date yyyy-mm-dd | date(2010,4,15) | date('2010-04-15') |
datetime(yyyy,mm,dd [,HH,MM,SS]) | datetime yyyy-mm-dd HH:MM:SS | datetime(2010,4,15,20,13) | datetime('2010-04-15T20:13') |
datetime(d,t) | datetime from date and time | datetime( date('2010-04-15'), time('20:13') ) | datetime('2010-04-15T20:13') |
today() | current date | today() | |
now() | current date and time | now() | |
date(dt) | date of dt | date( datetime('2010-04-15T20:13') ) | date('2010-04-15') |
time(dt) | time of dt | time( datetime('2010-04-15T20:13') ) | time('20:13') |
year(d) | year of d | year(date('2010-04-15')) | 2010 |
month(d) | month of d | month(date('2010-04-15')) | 4 |
day(d) | day of d | day(date('2010-04-15')) | 15 |
hour(t) | hours of t | hour(time('20:13')) | 20 |
minute(t) | minutes of t | minute(time('20:13')) | 13 |
second(t) | seconds of t | second(time('20:13')) | 0.0 |
d + n | increment d by n days | date('1991-08-20')+6813 | date('2010-04-15') |
d - n | decrement d by n days | date('2028-12-09')-6813 | date('2010-04-15') |
d1 - d2 | number of days between d1 and d2 | date('2028-12-09') - date('1991-08-20') | 13626 |
Conversion functions accept untyped literals and string expressions. An untyped literal must obey the literal format of the respective target type. Conversion from a string value is backend-specific.
‘2010-04-15’ | ‘20:13’ | ‘2010-04-15 20:13’ |
---|---|---|
2010-04-15 | 20:13:00 | 2010-04-15 20:13:00 |
id | name | gender | dob | school_code | program_code | start_date | is_active |
---|---|---|---|---|---|---|---|
1036 | Jonathan Bouchard | m | 1982-02-12 | art | ustudio | 2007-08-15 | false |
1041 | Lowell Cooper | m | 1982-01-05 | art | uhist | 2007-08-15 | false |
1113 | John Miller | m | 1982-05-14 | art | uhist | 2007-08-15 | false |
… | … | … | … | … | … | … | … |
Construction functions accept and normalize component values outside the regular range.
date(2010,4,15) | datetime(2010,4,15,20,13) | datetime(date(‘2010-04-15’),time(‘20:13’)) |
---|---|---|
2010-04-15 | 2010-04-15 20:13:00 | 2010-04-15 20:13:00 |
date(2010,4,15) | date(2010,3,46) | date(2011,-8,15) |
---|---|---|
2010-04-15 | 2010-04-15 | 2010-04-15 |
The extracted values are integers except for second(), where the extracted value is a float number.
$dt | $dt | year($d) | month($d) | day($d) | hour($t) | minute($t) | second($t) |
---|---|---|---|---|---|---|---|
2010-04-15 | 20:13:00 | 2010 | 4 | 15 | 20 | 13 | 0.0 |
date(‘1991-08-20’)+6813 | datetime(‘1991-08-20 02:01’)+6813.75833333333 |
---|---|
2010-04-15 | 2010-04-15 20:13:00 |
date(‘2028-12-09’)-6813 | datetime(‘2028-12-10 14:25’)-6813.75833333333 |
---|---|
2010-04-15 | 2010-04-15 20:13:00 |
date(‘2028-12-09’)-date(‘1991-08-20’) |
---|
13626 |
name | age |
---|---|
Linda Wright | 18.9 |
Beth Thompson | 19.6 |
Sheri Sanchez | 22.3 |
… | … |
Function | Description | Example Input |
---|---|---|
exists(ps) | TRUE if ps contains at least one TRUE value; FALSE otherwise | exists(course.credits>5) |
every(ps) | TRUE if ps contains only TRUE values; FALSE otherwise | every(course.credits>5) |
count(ps) | number of TRUE values in ps | count(course.credits>5) |
min(xs) | smallest element in xs | min(course.credits) |
max(xs) | largest element in xs | max(course.credits) |
sum(xs) | sum of elements in xs | sum(course.credits) |
avg(xs) | average value of elements in xs | avg(course.credits) |
Aggregate functions accept a plural argument, which, when evaluated, produces a flow of values, and generates a single aggregating value from it.
Boolean aggregates expect a Boolean argument; a non-Boolean argument is converted to Boolean first (see function boolean()).
department_code | no | title | credits | description |
---|---|---|---|---|
astro | 105 | General Astronomy I | 5 | Overview of the current astronomy, its goals and problems, basic instruments, space objects classification. |
astro | 106 | General Astronomy I Lab | 2 | Laboratory studies that complement the lecture course. |
astro | 108 | Stars and Planets | 3 | Basics of planet and star formation, their lifecycle from birth to death. |
… | … | … | … | … |
exists(astro_course.credits>=5) | every(astro_course.credits>=5) | count(astro_course.credits>=5) |
---|---|---|
true | false | 2 |
department_code | no | title | credits | description |
---|
exists(pia_course.credits>=5) | every(pia_course.credits>=5) | count(pia_course.credits>=5) |
---|---|---|
false | true | 0 |
The functions accept numeric, string, enumeration and date/time arguments. NULL values in the flow are ignored; if the flow is empty, NULL is returned.
min(astro_course.credits) | max(astro_course.credits) |
---|---|
1 | 5 |
min(pia_course.credits) | max(pia_course.credits) |
---|---|
— | — |
The functions accept a numeric argument. sum() returns a result of the same type as the argument, avg() returns a decimal result for an integer or a decimal argument, and float result for a float argument.
sum(astro_course.credits) | avg(astro_course.credits) |
---|---|
66 | 3.0 |
sum(pia_course.credits) | avg(pia_course.credits) |
---|---|
0 | — |
Function | Description | Example Input |
---|---|---|
flow ? p | records from flow satisfying condition p | school?code='edu' |
filter(p) | school.filter(code='edu') | |
flow ^ x | unique values of x as it runs over flow | school^campus |
distinct(flow{x}) | distinct(school{campus}) | |
flow {x,...} | select output columns x, ... for flow | school{code,name} |
select(x,...) | school.select(code,name) | |
sort(x,...) | reorder records in flow by x, ... | course.sort(credits-) |
limit(n) | first n records from flow | course.limit(10) |
limit(n,k) | n records from flow starting from k-th | course.limit(10,20) |
x -> xs | traverse an ad-hoc link | school.(campus -> school) |
fork([x]) | traverse a self-referential link | course.fork(credits) |
The condition is expected to be of Boolean type. If the argument p is not Boolean, it is implicitly converted to Boolean (see boolean()).
code | name | campus |
---|---|---|
bus | School of Business | south |
mus | School of Music & Dance | south |
code | name | campus |
---|---|---|
bus | School of Business | south |
mus | School of Music & Dance | south |
The projection operation flow ^ x creates a new naming scope, which may contain the following names:
code | name | campus | count(department) |
---|---|---|---|
art | School of Art and Design | old | 2 |
bus | School of Business | south | 3 |
edu | College of Education | old | 2 |
… | … | … | … |
campus | count(school) |
---|---|
north | 1 |
old | 4 |
south | 2 |
campus | count(^) |
---|---|
north | 1 |
old | 4 |
south | 2 |
campus | count(school) |
---|---|
north | 1 |
old | 4 |
south | 2 |
num_dept | count(school) |
---|---|
0 | 2 |
2 | 2 |
3 | 1 |
… | … |
campus:if_null(‘’) | count(department) | count(school) |
---|---|---|
0 | 2 | |
north | 4 | 1 |
old | 2 | 2 |
… | … | … |
The selector expression admits two forms of short-cut syntax:
count(school) | count(program) | count(department) |
---|---|---|
9 | 40 | 27 |
count(school) | count(program) | count(department) |
---|---|---|
9 | 40 | 27 |
code | count(program) |
---|---|
art | 3 |
bus | 6 |
edu | 7 |
… | … |
code | count(program) |
---|---|
art | 3 |
bus | 6 |
edu | 7 |
… | … |
code | count(program) |
---|---|
la | 9 |
eng | 8 |
edu | 7 |
… | … |
code | num_prog |
---|---|
art | 3 |
mus | 0 |
ph | 1 |
… | … |
code | $avg_credits | count(course?credits>$avg_credits) |
---|---|---|
acc | 3.5 | 3 |
arthis | 3.5 | 6 |
astro | 3.0 | 5 |
… | … | … |
Function | Description | Example Input |
---|---|---|
define(x:=...) | add names to the current scope | define(num_prog:=count(program)) |
where(expr,x:=...) | evaluate an expression with extra names in the current scope | count(course?credits>$c) :where $c:=avg(course.credits) |
@ x | evaluate an expression in the initial scope | |
root() | root scope | |
this() | current scope |
These functions add calculated attributes and references to the current scope.
Function | Description | Example Input |
---|---|---|
as(x,title) | set the column title | count(program) :as '# of programs' |
x + | indicate ascending order | credits+ |
x - | indicate descending order | credits- |
The title could be either an identifier or a quoted literal. This function should be used only when specifying output columns using a selection operator.
ID | # of Programs |
---|---|
art | 3 |
bus | 6 |
edu | 7 |
… | … |
This decorators should be used only on arguments of sort() or in a selection operator.
code | name | campus |
---|---|---|
ph | Public Honorariums | — |
sc | School of Continuing Studies | — |
eng | School of Engineering | north |
… | … | … |
Function | Description |
---|---|
/:html | HTML tabular output |
/:txt | plain text tabular output |
/:csv | CSV (comma-separated values) output |
/:tsv | TSV (tab-separated values) output |
/:json | JSON-serialized output |
These functions specify the format of the output data.
[-]/school/:csv
code,name,campus
art,School of Art and Design,old
bus,School of Business,south
…