Data Types In Malloy

VSCode

Because Malloy writes SQL expressions, when queries are actually executed, all data will be in types understood by the SQL engine which is running the query.

Malloy does have a set of data types and there is a mapping between the types of the SQL engine and the Malloy datatypes, mostly this mapping is invisible.

Number

An engine will have a large variety of storage formats for numeric data. Malloy doesn't distinguish between these, and simply folds them all into one type, number.

Although Malloy can use columns of any numberic type, there is no way to write a pure Malloy query to generate columns in a specific engine type.

Numeric Literals

Malloy has a fairly basic syntax for numeric literals

123
123.4
.4
0.4
123E4
123E+4
123E-4

String

Strings in Malloy are wrapped in either single quotes ('), double quotes (") or what we call the triple quote (""").

A triple quote string can extend over more than one line, as in

dimension: my_poem is """
  I wrote some new code in Malloy
  and experienced boundless joy.
  It worked right away,
  so I just have to say
  'This language is more than a toy.'
"""

Single and double quoted strings must be written on one line. They can contain a number of special characters through the \X syntax.

\b - Backspace
\f - Formfeed
\n - Newline
\r - Return
\t - Tab
\' - Single quote
\" - Double quote

Boolean

Boolean Comparison Nullability

Malloy has one interesting difference from most SQL engines in how it handles null values.

For expressions which return a boolean, e.g. might_be_null > 0:

In SQL, the result is a boolean column where the value could be TRUE, FALSE, or NULL,
In Malloy, the result will only be true or false

This is true for all the comparison operators (>, =, etc.) as well as functions which return a boolean (e.g. starts_with, is_inf, etc.).

Timestamp

A timestamp represents an instant in time.

Malloy's approach to timezone related computations relating to timestamp data is explained in the Timezones section.

Timestamp literals

Timestamp literals are specified in Malloy with the @ character. Seconds, subsecond resolution, and locale are optional.

@2001-02-03 04:05:06.001[America/Mexico_City]
@2001-02-03 04:05:06.001
@2001-02-03 04:05:06
@2001-02-03 04:05

In addition, in any of the above, a T can be used instead of a space between the date and time portion of the timestamp string, as in

@2001-02-03T04:05:06.001

A date literal, when used in an expression with a timestamp, also functions as a timestamp literal. That is

my_timestamp > @2003 is equivalent to my_timestamp > @2003-01-01 00:00

Date

A date represents combination of year, month, and day into a single data item.

Date Literals

Date literals are specified in Malloy with the @ character. A literal can specify a date, a week, a month, a quarter or a year.

Date: @2001-02-03
Week: @2001-02-04-WK ( the sunday week containing 2001-02-04 )

@2001-02-03-WK will generate an error because that day does not start a week. The week containing 2001-02-03 that would be @2001-02-03.week
Month: @2001-02
Quarter: @2001-Q2
Year: @2001

Unsupported SQL native data

Malloy may not have a corresponding data type for every data type which the SQL engine supports.

Columns in sources which Malloy does not have a datatype for are considered "unsupported." The following operations are legal on unsupported SQL native types

Two expressions of the same type can be compared
An unsupported SQL native type can be compared to NULL
An expression of unsupported SQL native type can be cast to a supported type

The following types are not assignable to fields, and are therefore considered intermediate types, in that they are primarily used to represent part of a computation that yields a regular scalar type, often boolean.

Regular Expressions

Literal regular expressions are enclosed in single quotation marks ' and preceded by either / or r, e.g. /'.*' or r'.*'. Both syntaxes are semantically equivalent.

In the future, the literal regular expressions will likely be simply slash-enclosed, e.g /.*/.

Values of type string may be compared against regular expressions using either the apply operator, name ? r'c.*' or the like operator, name ~ r'c.*'.

Ranges

There are three types of ranges today: number ranges, date ranges, and timestamp ranges. The most basic ranges are of the form start to end and represent the range from start up to, but not including, end, e.g. 10 to 20 or @2004-01 to @2005-05.

Ranges may be used in conjunction with the apply operator to test whether a value falls within a given range.

Alternations and Partials

Partials represent a "part of" a comparison. Specifically, a partial is a comparison missing its left-hand side, and represents the condition of the comparison yielding true if a given value were to be filled in for that missing left-hand side. For example, > 10 is a partial that represents the condition "is greater than ten." Likewise, != 'CA' is a partial that represents the condition of not being equal to 'CA'.

Alternations are combinations of partials representing either the logical union ("or") or conjunction ("and") of their conditions. Alternations are represented using the union alternation operator | and the conjunction alternation operator &.

For example, = 'CA' | = 'NY' represents the condition of being equal to 'CA' or alternatively being equal to 'NY'. On the other hand, != 'CA' & != 'NY' represents the condition of being not equal to 'CA' as well as being not equal to 'NY'.

Scalar values, regular expressions, and ranges may also be used in alternations, in which case the condition is assumed to be that of equality, matching, and inclusion respectively.

For example, 'CA' | r'N.*' represents the condition of being equal to 'CA' or starting with 'N', and 10 to 20 | 20 to 30 represents the condition of being either between 10 and 20 or 20 and 30.

Alternations and partials may be used in conjunction with the apply operator to test whether a value meets the given condition.

Nullability

Today, all Malloy types include the value null.