SQL Functions and Operators

This page describes the difference in behaviour of SQL functions and operators when using them within a Cube Query Rewrite. Outside a Cube Query Rewrite, the behaviour is the same as described in the PostgreSQL documentation.

datatype < datatype

Returns true if the first datatype is less than the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
<

The example below uses < in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.number < 1000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses < in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.number < 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

datatype > datatype

Returns true if the first datatype is greater than the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
>

The example below uses > in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.number > 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses > in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.number > 1000;
 COUNT(UInt8(1))
-----------------
               0
(1 row)

datatype <= datatype

Returns true if the first datatype is less than or equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
<=

The example below uses <= in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.number <= 1000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses <= in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.number <= 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

datatype >= datatype

Returns true if the first datatype is greater than or equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
>=

The example below uses >= in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.number >= 1000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses >= in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.number >= 1000;
 COUNT(UInt8(1))
-----------------
               0
(1 row)

datatype = datatype

Returns true if the first datatype is equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
=

The example below uses = in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.status = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses = in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.status = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

datatype = datatype

Returns true if the first datatype is equal to the second.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
!= or <>

The example below uses != in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.status != 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses <> in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.status <> 'completed';
 COUNT(UInt8(1))
-----------------
            6654
(1 row)

datatype BETWEEN datatype AND datatype

Returns true if the first datatype is between the second and third.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
BETWEEN

The example below uses BETWEEN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE Orders.number BETWEEN(Orders.status) 1 AND 100000;
 COUNT(UInt8(1))
-----------------
(0 rows)

The example below uses BETWEEN in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE orders.number BETWEEN 1 AND 100000;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

TRUNC(numeric)

Truncates a number to an integer (towards zero).

SelectionsProjectionsOuter Query
TRUNC

The example below uses TRUNC in a projection within a Cube query rewrite:

SELECT TRUNC(Orders.number) FROM Orders LIMIT 1;
 trunc(Orders.number)
----------------------
               504690
(1 row)

The example below uses TRUNC in post-processing with an outer query:

SELECT TRUNC(Orders.number)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 trunc(Orders.number)
----------------------
               504690
(1 row)

Concatenation

string || string

Concatenates the first string with the second.

SelectionsProjectionsOuter Query
||

The example below uses || in a selection within a Cube query rewrite:

SELECT COUNT(*)
FROM Orders
WHERE status = 'comple' || 'ted';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses || in post-processing with an outer query:

SELECT (status || status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 orders.status || orders.status
--------------------------------
 processingprocessing
(1 rows)

BIT_LENGTH(string)

Returns number of bits in the string (8 times the OCTET_LENGTH).

SelectionsProjectionsOuter Query
BIT_LENGTH

The example below uses BIT_LENGTH in post-processing with an outer query:

SELECT BIT_LENGTH(status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 bitlength(orders.status)
--------------------------
                       80
(1 row)

CHAR_LENGTH(string)
CHARACTER_LENGTH(string)

Returns the number of characters in the string.

SelectionsProjectionsOuter Query
CHAR_LENGTH

The example below uses CHAR_LENGTH in post-processing with an outer query:

SELECT CHAR_LENGTH(status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 characterlength(orders.status)
--------------------------------
                             10
(1 row)

LOWER(string)

Converts string to all lower case.

SelectionsProjectionsOuter Query
LOWER

The example below uses LOWER in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE LOWER(Orders.status) = 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses LOWER in post-processing with an outer query:

SELECT LOWER(Orders.status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 lower(Orders.status)
----------------------
 processing
(1 row)

OCTET_LENGTH(string)

Returns number of bytes in the string.

SelectionsProjectionsOuter Query
OCTET_LENGTH

The example below uses OCTET_LENGTH in post-processing with an outer query:

SELECT OCTET_LENGTH(status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 octetlength(orders.status)
----------------------------
                         10
(1 row)

SUBSTRING (string [ FROM start integer ] [ FOR count integer ])

Extracts the substring of string starting at the start character (if specified), and stopping after count characters (if specified). Either start or count must be specified.

SelectionsProjectionsOuter Query
SUBSTRING

The example below uses SUBSTRING in a projection within a Cube query rewrite:

SELECT SUBSTRING(Orders.status, 1, 3) FROM Orders LIMIT 1;
 substr(Orders.status,Int64(1),Int64(3))
-----------------------------------------
 com
(1 row)

The example below uses SUBSTRING in post-processing with an outer query:

SELECT SUBSTRING(Orders.status, 1, 3)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 substr(Orders.status,Int64(1),Int64(3))
-----------------------------------------
 com
(1 row)

TRIM ([ LEADING | TRAILING | BOTH ] [ characters text ] FROM string)

Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.

SelectionsProjectionsOuter Query
TRIM

The example below uses TRIM in post-processing with an outer query:

SELECT TRIM(LEADING 'p' FROM Orders.status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 ltrim(Orders.status,Utf8("p"))
--------------------------------
 rocessing
(1 row)

cube=>

UPPER(string)

Converts string to all upper case.

SelectionsProjectionsOuter Query
UPPER

The example below uses UPPER in post-processing with an outer query:

SELECT UPPER(Orders.status)
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 upper(Orders.status)
----------------------
 PROCESSING
(1 row)

starts_with (string, prefix)

Returns true if string starts with prefix.

SelectionsProjectionsOuter Query
STARTS_WITH

The example below uses STARTS_WITH in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE STARTS_WITH(Orders.status, 'complete');
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

The example below uses STARTS_WITH in post-processing with an outer query:

SELECT STARTS_WITH(Orders.status, 'complete')
FROM (SELECT * FROM Orders) orders
LIMIT 1;
 startswith(Orders.status,Utf8("complete"))
--------------------------------------------
 f
(1 row)

string LIKE pattern

Returns true if string matches the supplied pattern.

SelectionsProjectionsOuter Query
LIKE

The example below uses LIKE in a selection within a Cube query rewrite:

SELECT COUNT(*)
FROM Orders
WHERE Orders.status LIKE 'completed';
 COUNT(UInt8(1))
-----------------
            3346
(1 row)

REGEXP_SUBSTR(string, pattern)

Returns the substring of string that matches a POSIX regular expression pattern, or null if there is no match.

SelectionsProjectionsOuter Query
REGEXP_SUBSTR

The example below uses REGEXP_SUBSTR in post-processing with an outer query:

SELECT REGEXP_SUBSTR(Orders.status, '\w+')
FROM (SELECT * FROM Orders) orders
LIMIT 1;

COUNT(*)
COUNT(col)

Counts the number of rows. If * is specified, the count returned is the total number of rows.

SelectionsProjectionsOuter Query
COUNT1 2
  1. COUNT can only be used to query measures of type count inside Cube query rewrites.

  2. If * is specified, Cube will query the first measure of type count found in the cube

The example below uses COUNT in a projection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders;
 COUNT(UInt8(1))
-----------------
           10000
(1 row)

The example below uses COUNT with a measure of type count in a projection within a Cube query rewrite:

SELECT COUNT(Orders.count) FROM Orders;
 COUNT(Orders.count)
---------------------
               10000
(1 row)

MEASURE(measure)

MEASURE is only supported inside Cube query rewrites. It is a special function that can be only be used with cube measures.

The example below uses MEASURE in a projection within a Cube query rewrite:

SELECT MEASURE(Orders.count) FROM Orders;
 measure(Orders.count)
-----------------------
                 10000
(1 row)

SUM(col)

Computes the sum of the non-null input values.

SelectionsProjectionsOuter Query
SUM1
  1. SUM can only be used to query measures of type sum inside Cube query rewrites.

The example below uses SUM with a measure of type sum in a projection within a Cube query rewrite:

SELECT SUM(number) FROM Orders;
 SUM(Orders.number)
--------------------
             504690
(1 row)

AVG(col)

Computes the average (arithmetic mean) of all the non-null input values.

SelectionsProjectionsOuter Query
AVG1
  1. AVG can only be used to query measures of type avg inside Cube query rewrites.

The example below uses AVG with a measure of type avg in a projection within a Cube query rewrite:

SELECT AVG(avgNumber) FROM Orders;
 AVG(Orders.avgNumber)
-----------------------
                50.469
(1 row)

MIN(col)

Computes the minimum of the non-null input values. Available for any numeric, string or date/time value.

SelectionsProjectionsOuter Query
MIN1
  1. MIN can only be used to query measures of type min inside Cube query rewrites.

The example below uses MIN with a measure of type min in a projection within a Cube query rewrite:

SELECT MIN(minNumber) FROM Orders;
 MIN(Orders.minNumber)
-----------------------
                     1
(1 row)

MAX(col)

Computes the maximum of the non-null input values. Available for any numeric, string or date/time value.

SelectionsProjectionsOuter Query
MAX1
  1. MAX can only be used to query measures of type max inside Cube query rewrites.

The example below uses MAX with a measure of type max in a projection within a Cube query rewrite:

SELECT MAX(maxNumber) FROM Orders;
 MAX(Orders.maxNumber)
-----------------------
                   100
(1 row)

TO_TIMESTAMP(string, string)

Converts a string to a timestamp according to the given format.

SelectionsProjectionsOuter Query
TO_TIMESTAMP

Valid format patterns can be found here.

The example below uses TO_TIMESTAMP in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE TO_TIMESTAMP('2020-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') < Orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7945
(1 row)

LOCALTIMESTAMP()

Returns the current date and time.

SelectionsProjectionsOuter Query
LOCALTIMESTAMP

The example below uses LOCALTIMESTAMP in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE LOCALTIMESTAMP() > Orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7693
(1 row)

NOW()

Returns the current date and time with time zone.

SelectionsProjectionsOuter Query
NOW

The example below uses NOW in post-processing with an outer query:

SELECT COUNT(*)
FROM (SELECT * FROM Orders) orders
WHERE NOW() > Orders.createdAt;
 COUNT(UInt8(1))
-----------------
            7693
(1 row)

EXTRACT(field FROM source)

The EXTRACT function retrieves subfields such as year or hour from date/time values.

SelectionsProjectionsOuter Query
EXTRACT

source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well). field is an identifier or string that selects what field to extract from source. The EXTRACT function returns values of type numeric.

The example below uses EXTRACT in a projection within a Cube query rewrite:

SELECT EXTRACT(YEAR FROM Orders.createdAt)
FROM Orders
LIMIT 1;
 datepart(Utf8("year"),Orders.createdAt)
-----------------------------------------
                                    2019
(1 row)

DATE_TRUNC(field, source)

The DATE_TRUNC function is conceptually similar to the trunc function for numbers.

SelectionsProjectionsOuter Query
DATE_TRUNC

source is a value expression of type timestamp, timestamp with time zone, or interval. field selects to which precision to truncate the input value. The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).

The example below uses DATE_TRUNC in a projection within a Cube query rewrite:

SELECT DATE_TRUNC('month', Orders.createdAt)
FROM Orders
LIMIT 1;
 datetrunc(Utf8("month"),Orders.createdAt)
-------------------------------------------
 2019-01-01 00:00:00.000000
(1 row)

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

The CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.

Within a Cube Query Rewrite, this operator may behave differently. Use the table below for support:

SelectionsProjectionsOuter Query
CASE

The example below uses CASE in post-processing with an outer query:

SELECT
  CASE
    WHEN char_length(status) > 5 THEN 'big'
    ELSE 'small'
  END status,
  SUM(number) as totalValue
from (
  SELECT status, number FROM Orders
) orders
GROUP BY 1;
 status | totalValue
--------+------------
 big    |     504690
(1 row)

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null.

SelectionsProjectionsOuter Query
COALESCE

Null is returned only if all arguments are null. It is used to substitute a default value for null values when data is retrieved for display.

The example below uses COALESCE in post-processing with an outer query:

SELECT
  COUNT(*),
  COALESCE(status, 'unknown') AS state
FROM (SELECT * FROM Orders) orders
GROUP BY 2;
 COUNT(UInt8(1)) |   state
-----------------+------------
            3354 | processing
            3346 | completed
            3300 | shipped
(3 rows)

NULLIF(value1, value2)

The NULLIF function returns a null value if value1 equals value2, otherwise it returns value1.

SelectionsProjectionsOuter Query
NULLIF

The example below uses NULLIF in post-processing with an outer query:

SELECT NULLIF(Orders.status, 'completed'), COUNT(*)
FROM (SELECT * FROM Orders) orders
GROUP BY 1;
 nullif(Orders.status,Utf8("completed")) | COUNT(UInt8(1))
-----------------------------------------+-----------------
 processing                              |            3354
                                         |            3346
 shipped                                 |            3300
(3 rows)

expression IN (value [, ...])

The IN operator allows specifying multiple values for comparison in a WHERE clause, and returns true if the provided expression matches any of the values.

SelectionsProjectionsOuter Query
IN

The example below uses IN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE status IN ('shipped', 'completed');
 COUNT(UInt8(1))
-----------------
            6646
(1 row)

expression NOT IN (value [, ...])

The NOT IN operator is the reverse of the IN operator; it returns true if the provided expression matches none of the values.

SelectionsProjectionsOuter Query
NOT IN

The example below uses IN in a selection within a Cube query rewrite:

SELECT COUNT(*) FROM Orders WHERE status NOT IN ('shipped', 'completed');
 COUNT(UInt8(1))
-----------------
            3354
(1 row)

Did you find this page useful?