Functions and Operators

This document describes built-in functions and operators.

Logical 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'

Boolean Cast

boolean(x)

Convert x to Boolean.

The result of the conversion depends on the type of the argument:

untyped

The literal 'false' is converted to FALSE, the literal 'true' is converted to TRUE, any other literals generate an error.

boolean

The value is unchanged.

string

NULL and an empty string are converted to FALSE, other values are converted to TRUE.

other data types

null values are converted to FALSE, other values are converted to TRUE.

◥/{boolean('false'), boolean('true')}

‘false’	‘true’
false	true

◥/{boolean(null()), boolean(false()), boolean(true())}

null()	false()	true()
—	false	true

◥/{boolean(string(null())), boolean(string('')), boolean(string('HTSQL'))}

null()	‘’	‘HTSQL’
false	false	true

◥/{boolean(integer(null())), boolean(0.0), boolean(date('2010-04-15'))}

null()	0.0	‘2010-04-15’
false	true	true

Logical Values

true()

Logical TRUE value.

false()

Logical FALSE value.

◥/{true(), false()}

true()	false()
true	false

Logical Operators

p | q

Logical OR operator.

p & q

Logical AND operator.

! p

Logical NOT operator.

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()	false()|true()	false()|false()
true	true	true	false

◥/{true()&true(), true()&false(), false()&true(), false()&false()}

true()&true()	true()&false()	false()&true()	false()&false()
true	false	false	false

◥/{!true(), !false()}

!true()	!false()
false	true

◥/{true()&null(), false()&null(), null()&null(),
  true()|null(), false()|null(), null()|null(),
  !null()}

true()&null()	false()&null()	null()&null()	true()|null()	false()|null()	null()|null()	!null()
—	false	—	true	—	—	—

◥/school?exists(program)&exists(department)|!campus

code	name	campus
art	School of Art and Design	old
bus	School of Business	south
edu	College of Education	old
…	…	…

NULL Checking

null()

Untyped NULL value.

is_null(x)

TRUE if x is NULL, FALSE otherwise.

if_null(x,y)

x if x is not NULL, y otherwise.

null_if(x,y)

x if x is not equal to y, NULL otherwise.

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)}

is_null(null())	is_null(0)
true	false

◥/{if_null('SQL','HTSQL'), if_null(null(),'HTSQL')}

if_null(‘SQL’,’HTSQL’)	if_null(null(),’HTSQL’)
SQL	HTSQL

◥/{null_if('HTSQL','SQL'), null_if('SQL','SQL')}

null_if(‘HTSQL’,’SQL’)	null_if(‘SQL’,’SQL’)
HTSQL	—

◥/course{title, credits}?is_null(credits)

title	credits
Classroom Visit	—
Spring Basket Weaving Workshop	—

◥/course{title, credits}?(credits :if_null 0)=0

title	credits
Classroom Visit	—
Spring Basket Weaving Workshop	—
Peer Portfolio Review	0

◥/course{title, credits}?!(credits :null_if 0)

title	credits
Classroom Visit	—
Spring Basket Weaving Workshop	—
Peer Portfolio Review	0

Equality Operators

x = y

TRUE if x is equal to y, FALSE otherwise. Returns NULL if any of the operands is NULL.

x != y

TRUE if x is not equal to y, FALSE otherwise. Returns NULL if any of the operands is NULL.

x == y

TRUE if x is equal to y, FALSE otherwise. Treats NULL as a regular value.

x !== y

TRUE if x is not equal to y, FALSE otherwise. Treats NULL as a regular value.

x = {a,b,c,...}

TRUE if x is equal to some value among a,b,c,..., FALSE otherwise.

x != {a,b,c,...}

TRUE if x is not equal to all values among a,b,c,..., FALSE otherwise.

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'}

1=1.0	‘HTSQL’!=’SQUARE’
true	true

◥/{0!=null(), null()=null(), 0!==null(), null()==null()}

0!=null()	null()=null()	0!==null()	null()==null()
—	—	true	true

◥/'HTSQL'!={'ISBL','SQUARE','QUEL'}

‘HTSQL’!={‘ISBL’,’SQUARE’,’QUEL’}
true

◥/school?campus='old'

code	name	campus
art	School of Art and Design	old
edu	College of Education	old
la	School of Arts and Humanities	old
…	…	…

◥/school?campus!={'north','south'}

code	name	campus
art	School of Art and Design	old
edu	College of Education	old
la	School of Arts and Humanities	old
…	…	…

◥/school{code, campus=='old', campus=='north', campus=='south'}

code	campus==’old’	campus==’north’	campus==’south’
art	true	false	false
bus	false	false	true
edu	true	false	false
…	…	…	…

Comparison Operators

x < y

TRUE if x is less than y, FALSE otherwise.

x <= y

TRUE if x is less than or equal to y, FALSE otherwise.

x > y

TRUE if x is greater than y, FALSE otherwise.

x >= y

TRUE if x is greater than or equal to y, FALSE otherwise.

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')}

23<=17.5	‘HTSQL’<’SQUARE’	date(‘2010-04-15’)>=date(‘1991-08-20’)
false	true	true

◥/school?count(department)>=4

code	name	campus
eng	School of Engineering	north
la	School of Arts and Humanities	old
mus	School of Music & Dance	south
…	…	…

Branching Functions

if(p1,c1,p2,c2,...,pn,cn[,o])

This function takes N logical expressions p1,p2,...,pN interleaved with N values c1,c2,...,cN, followed by an optional value o. The function returns the value ck corresponding to the first predicate pk evaluated to TRUE. If none of the predicates are evaluated to TRUE, the value of o is returned, or NULL if o is not specified.

switch(x,y1,c1,y2,c2,...,yn,cn[,o])

This function takes a control expression x followed by N variant values y1,y2,...,yN interleaved with N resulting values c1,c2,...,cN, and concluded with an optional default value o. The function returns the value ck corresponding to the first variant yk equal to x. If none of the variants are equal to the control value, o is returned, or NULL if o is not specified.

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().

◥/course{title, if(credits>=5, 'hard',
                  credits>=3, 'medium',
                              'easy') :as level}
       ?department.code='astro'

title	level
General Astronomy I	hard
General Astronomy I Lab	easy
Stars and Planets	medium
…	…

◥/student{name, switch(gender, 'm', 1,
                              'f', -1) :as sex_code}
        ?program.code='gedu'

name	sex_code
Sheri Sanchez	-1
Anna Carroll	-1
Alphonse Gilmore	1
…	…

Numeric Functions

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

Numeric Cast

integer(x)

Convert x to integer.

decimal(x)

Convert x to decimal.

float(x)

Convert x to float.

The argument of a conversion function can be of one of the following types:

untyped

An untyped literal must be a valid number. The integer() function accepts only integer literals, decimal() and float() accepts untyped literals written in integer, decimal or scientific notation.

numeric

Numeric cast functions convert numbers between different storage forms. Behavior on range overflow and rounding rules are backend-dependent.

string

A string value must contain a valid number. The set of allowed input values depends on the backend.

◥/{integer(2.125), decimal('271828e-5'), float(string(60))}

2.125	‘271828e-5’	60
2	2.71828	60.0

Arithmetic Expressions

+ x

Return x.

- x

Negate x.

x + y

Add x to y.

x - y

Subtract y from x.

x * y

Multiply x by y.

x / y

Divide x by y.

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}

(2+4)*7	-(98-140)	21/5
42	42	4.2

Rounding Functions

round(x)

Round x to the nearest integer value.

round(x,n)

Round x to n decimal places.

trunc(x)

Round x to an integer, towards zero.

trunc(x,n)

Round x to n decimal places, towards zero.

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)}

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)}

trunc(3272.78125)	trunc(3272.78125,2)	trunc(3272.78125,-2)
3272	3272.78	3200

◥/school{code, avg(department.count(course)) :round 2}

code	avg(department.count(course)):round(2)
art	19.5
bus	14.67
edu	17.5
…	…

◥/department^avg_credits {avg_credits, count(department)}
 :where department.avg_credits := avg(course.credits) :trunc(1)

avg_credits	count(department)
2.9	1
3.0	4
3.1	1
…	…

String Functions

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'

String Cast

string(x)

Convert x to a string.

HTSQL permits any value to be converted to a string; the conversion respects the format for literals of the original type.

◥/{string('HTSQL'), string(true()), string(2.125), string(datetime('2010-04-15 20:13'))}

‘HTSQL’	true()	2.125	‘2010-04-15 20:13’
HTSQL	true	2.125	2010-04-15 20:13:00

◥/department{'Department of '+name+' offers '
            +string(count(course))+' courses' :as text}
           ?exists(course)

text
Department of Accounting offers 12 courses
Department of Art History offers 20 courses
Department of Astronomy offers 22 courses
…

String Length

length(s)

Number of characters in s.

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())}

length(‘HTSQL’)	length(‘’)	length(null())
5	0	0

Concatenation

s + t

Concatenate s and t.

The concatenation operator treats a NULL operand as an empty string.

◥/{'HT'+'SQL', null()+'SQL'}

‘HT’+’SQL’	null()+’SQL’
HTSQL	SQL

◥/course{department_code+'.'+string(no) :as code, title}

code	title
acc.100	Practical Bookkeeping
acc.200	Introduction to Accounting
acc.234	Accounting Information Systems
…	…

Substring Search

s ~ t

TRUE if t is a substring of s, FALSE otherwise.

s !~ t

TRUE if t is a substring of s, FALSE otherwise.

The search functions are case-insensitive; exact rules for case-insensitivity depend on the backend.

◥/{'HTSQL'~'sql', 'sql'!~'HTSQL'}

‘HTSQL’~’sql’	‘sql’!~’HTSQL’
true	true

◥/school?code~'art'

code	name	campus
art	School of Art and Design	old

Substring Extraction

head(s)

The first character of s.

head(s,n)

The first n characters of s.

tail(s)

The last character of s.

tail(s,n)

The last n characters of s.

slice(s,i,j)

The i-th to j-th (exclusive) characters of s.

at(s,k)

The k-th character of s.

at(s,k,n)

n characters of s starting from the k-th.

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)}

‘HTSQL’:head	‘HTSQL’:head(2)	‘HTSQL’:head(-3)
H	HT	HT

◥/{'HTSQL' :tail, 'HTSQL': tail(3), 'HTSQL': tail(-2)}

‘HTSQL’:tail	‘HTSQL’:tail(3)	‘HTSQL’:tail(-2)
L	SQL	SQL

◥/{'HTSQL' :slice(1,-1), 'HTSQL' :slice(1,null()), 'HTSQL' :slice(null(),-1)}

‘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)}

‘HTSQL’:at(2)	‘HTSQL’:at(1,3)	‘HTSQL’:at(-1,-3)
S	TSQ	TSQ

Case Conversion

upper(s)

Convert s to upper case.

lower(s)

Convert s to lower case.

The conversion semantics is backend-dependent.

◥/{'htsql' :upper, 'HTSQL' :lower}

‘htsql’:upper	‘HTSQL’:lower
HTSQL	htsql

String Trimming

trim(s)

Strip leading and trailing spaces from s.

ltrim(s)

Strip leading spaces from s.

rtrim(s)

Strip trailing spaces from s.

◥/{'  HTSQL  ' :trim :replace(' ','!'),
  '  HTSQL  ' :ltrim :replace(' ','!'),
  '  HTSQL  ' :rtrim :replace(' ','!')}

‘  HTSQL  ’:trim:replace(‘ ’,’!’)	‘  HTSQL  ’:ltrim:replace(‘ ’,’!’)	‘  HTSQL  ’:rtrim:replace(‘ ’,’!’)
HTSQL	HTSQL!!	!!HTSQL

Search and Replace

replace(s,t,r)

Replace all occurences of substring t in s with r.

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())}

‘HTTP’:replace(‘TP’,’SQL’)	‘HTTP’:replace(null(),’SQL’)	‘HTTP’:replace(‘TP’,null())
HTSQL	HTTP	HT

Date/Time Functions

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

Date/Time Cast

date(x)

Convert x to a date value.

time(x)

Convert x to a time value.

datetime(x)

Convert x to a datetime value.

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.

◥/{date('2010-04-15'), time('20:13'), datetime('2010-04-15 20:13')}

‘2010-04-15’	‘20:13’	‘2010-04-15 20:13’
2010-04-15	20:13:00	2010-04-15 20:13:00

◥/student?dob<date('1982-06-01')

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
…	…	…	…	…	…	…	…

Date/Time Construction

date(yyyy,mm,dd)

Construct a date from the given year, month and day values.

datetime(yyyy,mm,dd[,HH,MM,SS])

Construct a datetime from the given year, month, day, hour, minute and second values.

datetime(d,t)

Construct a datetime from the given date and time.

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'))}

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)}

date(2010,4,15)	date(2010,3,46)	date(2011,-8,15)
2010-04-15	2010-04-15	2010-04-15

Component Extraction

date(dt)

Date of a datetime value.

time(dt)

Time of a datetime value.

year(d)

Year of a date or a datetime value.

month(d)

Month of a date or a datetime value.

day(d)

Day of a date or a datetime value.

hour(t)

Hours of a time or a datetime value.

minute(t)

Minutes of a time or a datetime value.

second(t)

Seconds of a time or a datetime value.

The extracted values are integers except for second(), where the extracted value is a float number.

◥/{date($dt), time($dt),
  year($d), month($d), day($d),
  hour($t), minute($t), second($t)}
 :where ($d := date('2010-04-15'),
         $t := time('20:13'),
         $dt := datetime($d,$t))

$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/Time Arithmetics

d + n

Increment a date or a datetime value by n days.

d - n

Decrement a date or a datetime value by n days.

d1 - d2

Number of days between two date values.

◥/{date('1991-08-20')+6813, datetime('1991-08-20 02:01')+6813.75833333333}

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}

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')

date(‘2028-12-09’)-date(‘1991-08-20’)
13626

◥/student{name, (start_date-dob)/365 :round(1) :as age}

name	age
Linda Wright	18.9
Beth Thompson	19.6
Sheri Sanchez	22.3
…	…

Aggregate Functions

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

exists(xs)

Produce TRUE if xs contains at least one TRUE value, FALSE otherwise. The aggregate returns FALSE on an empty flow.

every(xs)

Produce FALSE if xs contains only TRUE values, FALSE otherwise. The aggregate returns TRUE on an empty flow.

count(xs)

The number of TRUE values in xs; 0 if xs is empty.

Boolean aggregates expect a Boolean argument; a non-Boolean argument is converted to Boolean first (see function boolean()).

◥/course?department.code='astro'

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)}
 :where astro_course := course?department.code='astro'

exists(astro_course.credits>=5)	every(astro_course.credits>=5)	count(astro_course.credits>=5)
true	false	2

◥/course?department.code='pia'

department_code	no	title	credits	description

◥/{exists(pia_course.credits>=5),
  every(pia_course.credits>=5),
  count(pia_course.credits>=5)}
 :where pia_course := course?department.code='pia'

exists(pia_course.credits>=5)	every(pia_course.credits>=5)	count(pia_course.credits>=5)
false	true	0

Extrema

min(xs)

The smallest value in xs.

max(xs)

The largest value in xs.

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)}
 :where astro_course := course?department.code='astro'

min(astro_course.credits)	max(astro_course.credits)
1	5

◥/{min(pia_course.credits), max(pia_course.credits)}
 :where pia_course := course?department.code='pia'

min(pia_course.credits)	max(pia_course.credits)
—	—

Sum and Average

sum(xs)

The sum of values in xs; returns 0 if xs is empty.

avg(xs)

The average of values in xs.

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)}
 :where astro_course := course?department.code='astro'

sum(astro_course.credits)	avg(astro_course.credits)
66	3.0

◥/{sum(pia_course.credits), avg(pia_course.credits)}
 :where pia_course := course?department.code='pia'

sum(pia_course.credits)	avg(pia_course.credits)
0	—

Flow Operations

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)

Sieving

flow ? p

Emit records from flow that satisfy condition p.

filter(p)

Emit records from the input flow that satisfy condition p.

The condition is expected to be of Boolean type. If the argument p is not Boolean, it is implicitly converted to Boolean (see boolean()).

◥/school?campus='south'

code	name	campus
bus	School of Business	south
mus	School of Music & Dance	south

◥/school.filter(campus='south')

code	name	campus
bus	School of Business	south
mus	School of Music & Dance	south

Projection

flow ^ x

Emit all unique values of x as it ranges over flow. NULL values are ignored.

flow ^ {x,...}

Emit all unique values of the expressions x,.... NULL values are ignored.

distinct(flow{x,...})

Emit all unique values of the output columns of flow{x,...}. NULL values are ignored.

The projection operation flow ^ x creates a new naming scope, which may contain the following names:

flow

If flow is an identifier, then it is used to denote the plural link associating each value of x with respective records from the original flow. It is called the complement link of the projection. The symbol ^ is an alias for a complement link and could be used when flow is not an identifier and so cannot be used as a name.

x

If x is an identifier, then it refers to the value of x. It is called the kernel of the projection. When x is not an identifier, but an arbitrary expression, one may assign it a name using in-place selector assignment syntax.

◥/school{code, name, campus, count(department)}

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
…	…	…	…

◥/school^campus {campus, count(school)}

campus	count(school)
north	1
old	4
south	2

◥/school^campus {*, count(^)}

campus	count(^)
north	1
old	4
south	2

◥/distinct(school{campus}) {campus, count(school)}

campus	count(school)
north	1
old	4
south	2

◥/school^{num_dept := count(department)}
 {num_dept, count(school)}

num_dept	count(school)
0	2
2	2
3	1
…	…

◥/school^{campus :if_null '', count(department)}
 {*, count(school)}

campus:if_null(‘’)	count(department)	count(school)
	0	2
north	4	1
old	2	2
…	…	…

Selection

{x,...}

Define output columns in the input flow.

flow{x,...}

Define output columns in the given flow.

select(x,...)

Define output columns in the input flow.

The selector expression admits two forms of short-cut syntax:

in-place assignment

If an element of a selector is an assignment expression, the name defined by the assignment is added to the current scope. Only unqualified attribute and reference assignments are allowed.

sorting decorators

If an element of a selector contains a sort order indicators, the expression is used to reorder elements in the input flow.

◥/{count(school), count(program), count(department)}

count(school)	count(program)	count(department)
9	40	27

◥/select(count(school), count(program), count(department))

count(school)	count(program)	count(department)
9	40	27

◥/school{code, count(program)}

code	count(program)
art	3
bus	6
edu	7
…	…

◥/school.select(code, count(program))

code	count(program)
art	3
bus	6
edu	7
…	…

◥/school{code, count(program)-}

code	count(program)
la	9
eng	8
edu	7
…	…

◥/school{code, num_prog := count(program)}?num_prog<4

code	num_prog
art	3
mus	0
ph	1
…	…

◥/department{code, $avg_credits := avg(course.credits),
            count(course?credits>$avg_credits)}

code	$avg_credits	count(course?credits>$avg_credits)
acc	3.5	3
arthis	3.5	6
astro	3.0	5
…	…	…

Scope Operations

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

Calculated Attributes

define(x:=...)

Add a calculated attribute to the current scope.

where(expr,x:=...)

Evaluate an expression in a current scope with a calculated attribute.

These functions add calculated attributes and references to the current scope.

Scopes

@ x

Reset to the initial scope.

root()

The root scope.

this()

The current scope.

Decorators

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-

Title

as(x,title)

Specifies the title of the output column.

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.

◥/school{code :as ID, count(program) :as '# of Programs'}

ID	# of Programs
art	3
bus	6
edu	7
…	…

Direction Decorators

x +

Specifies ascending direction, NULL first.

x -

Specifies descending direction, NULL last.

This decorators should be used only on arguments of sort() or in a selection operator.

◥/school.sort(campus+)

code	name	campus
ph	Public Honorariums	—
sc	School of Continuing Studies	—
eng	School of Engineering	north
…	…	…

Formatters

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
…