doc/developer/sqllogictest.md
This page assumes that you have already read and understood the basics of testing with Materialize and SQLite sqllogictest documentation.
If you have made a new function, be sure to test both select func(NULL) and
select func(col) from atable where col is a column that contains NULL. The
null in select func(NULL) is treated as type `` whereas the null in col is
treated as the same type as col. If the function has more than one argument,
be sure to test with NULL and a column that contains NULL for each argument.
If you have made a new function with numeric arguments, make sure that you have tested what happens if each argument is 0. Other important numbers to remember testing are negative numbers, the number 1 (for certain tests), and +/- infinity.
Do not use expected data types to verify the data type of resulting columns
because sqllogictest will try its best to coerce the results to the type
expected. Instead, use SHOW COLUMNS....
While we support coercing boolean results to T(ext), B(ool), and I(nt), we recommend using T for new tests involving selecting on booleans.
You can test that a statement or query will fail (with optionally a particular error message) like this:
statement error <optional expected error message here>
<your statement here>
or this:
query error <optional expected error message here>
<your query here>
query error will only run the statement if it is SELECT, CREATE VIEW, and
SHOW INDEXES statements. Otherwise, it will just skip running the statement
and declare the test as passing.
statement error will run any type of statement.
While you can't create tables with primary keys in Materialize yet, you can create tables with primary keys in sqllogictest, and the optimizer will take the primary keys into account.
The syntax is either:
CREATE TABLE foo (a INT PRIMARY KEY, b INT)
or
CREATE TABLE bar (
a smallint,
b integer,
c char(10),
d char(20),
PRIMARY KEY (a, b)
)
If you use Visual Studio Code, @benesch has made a syntax highlighter for sqllogictest, which you can find here.
If you run
bin/sqllogictest -- --rewrite-results <path_to_test_file>
sqllogictest will automatically replace the expected results with the actual
results. The main time you use this argument is if you have made an improvement to
the query planner and need to update the expected plan for every EXPLAIN query.
If you want to debug a sqllogictest run via debugger, use one of the following
bin/sqllogictest --wrapper 'rust-lldb --' -- <paths_to_test_files_to_run>
(lldb) run
(lldb) bt
bin/sqllogictest --wrapper 'rust-gdb --args' -- <paths_to_test_files_to_run>
(gdb) run
(gdb) bt
See GDB to LLDB command map for more details. Note:
Do not use rust-gdb to debug sqllogictest on MacOS because gdb will print
a bunch of error messages and then freeze.
By default, sqllogictest will create a single Mz instance and run
all the SLT tests against it. For tests that require special treatment,
the reset-server can be used to bring about a new, pristine Mz instance.
For tests that involve object Ids (such as certain EXPLAIN tests and
those that consult the introspection tables), reset-server should
be used at the start of the .slt file.
simple extensionIn addition to statement and query, we have added our own directive
simple. It is followed by multiple lines until ---- and executes
these lines as a simple query over pgwire. This is needed because the
other directives use the extended protocol, and we needed a way to test
multi-statement queries and implicit transactions.
An optional conn=<name> can be added to execute the message on a
named connection. It will be created on first use. This can be used to
test transaction isolation or otherwise do things that require multiple
connections.
If conn is specified, an additional ,user=<name> can be added to specify the
connecting user. If the user is mz_system, the internal SQL port will be used
on which system commands like ALTER SYSTEM can be used.
simple conn=mz_system,user=mz_system <SQL>
The output is one line per row, one "COMPLETE X" (where X is the number of affected rows) per statement, or an error message.
When using the multiline keyword it is possible to have output consisting of
multiple lines, up to the EOF keyword, which is still followed by COMPLETE:
simple multiline,conn=materialize,user=materialize EXPLAIN MATERIALIZED VIEW mv;
materialize.public.mv: Constant - (1)
EOF COMPLETE 1
copy extensionThe copy directive executes a COPY FROM
statement. For example, the following directive
copy table path/to/file.csv
pipes the contents of file.csv to the following SQL statement:
COPY table FROM STDIN
We have extended sqllogictest to have the concept of the "mode." There are two extant modes:
mode standard (default): Supports running tests written by SQLite.mode cockroach: Supports running tests written by CockroachDB.In our Materialize-specific sqllogictests, we use both modes, sometimes in the
same file. Most of the time, we use mode cockroach. The times when we use
mode standard are:
EXPLAIN PLAN queries.In mode standard, spaces in the query result are treated as column
delimiters only when it comes to column names. In mode cockroach, spaces in
the query result are treated as column delimiters except when the expected
result has only one column. Thus, if you are querying multiple columns of a
table,
mode standard:
query TT colnames
select col1 col2 from atable
----
col1 col2
row1col1
row1col2
row2col1
row2col2
mode cockroach:
query TT colnames
select col1 col2 from atable
----
col1 col2
row1col1 row1col2
row2col1 row2col2
In mode cockroach, if your expected column name or row entry contains a
space, to have the space not treated as a column delimiter, replace the
space with the symbol ␠, which is U+2420, the Unicode "SYMBOL FOR SPACE".
Example:
query TT colnames
select "col 1" "col 2" from atable
----
col␠1 col␠2
row1␠col1 row1␠col2
row2␠col1 row2␠col2
As mentioned earlier, in mode cockroach, replacing the space with the
symbol ␠ is optional when there is only one column of expected output. This
will work fine:
query T colnames
select "col 1" from atable
----
col 1
row1 col1
row2 col1
In mode standard, it does not seem to possible to indicate that the
expected column name contains a space even if only one column of output is
expected.
In mode standard, a Decimal in the query result is treated as having a
scale of 4 if its actual scale is 0 and as having a scale of 3 otherwise. A
floating point number is always treated as having a scale of 3.
mode standard
query R
SELECT 384::decimal(38, 5)
----
384.000
query R
SELECT 1::decimal(38, 0)
----
1.0000
query R
SELECT 2::float
----
2.000
query R
SELECT 3.143892789739843::float
----
3.144
query R
SELECT 314.3820897445435::float
----
314.382
mode cockroach, on the other hand, respects the scale of a Decimal type
and the precision of a floating point number.
mode cockroach
query R
SELECT 384::decimal(38, 5)
----
384.00000
query R
SELECT 1::decimal(38, 0)
----
1
query R
SELECT 2::float
----
2
query R
SELECT 3.143892789739843::float
----
3.143892789739843
query R
SELECT 314.3820897445435::float
----
314.3820897445435
In mode standard when interpreting a Numeric as an Int we round down to the nearest whole
number.
mode standard
query I
SELECT '1.9'::numeric
----
1
query I
SELECT '1.5'::numeric
----
1
query I
SELECT '1.1'::numeric
----
1
mode cockroach on the otherhand uses a strategy of rounding half up. That is we round to the
nearest integer and halfway values are rounded up.
mode cockroach
query I
SELECT '1.9'::numeric
----
2
query I
SELECT '1.5'::numeric
----
2
query I
SELECT '1.1'::numeric
----
1
As you can see above, generally sqllogictest
treats newlines as column or row delimiters. We have extended sqllogictest to
have some primitive support for text output that contains newlines with the
multiline keyword. If you add the multiline keyword, all text between ---- and EOF is treated as one entry with newlines. Here's an example.
query T multiline
select 'hello
world'
----
hello
world
EOF
The sqllogictest binary will panic if you specify any other options together
with multiline.
Our sqllogictest will skip running the following commands and return an Ok:
CREATE UNIQUE INDEX ...REINDEX ...These commands are present in the SQLite tests, but we do not support them yet, and we don't want failures associated with these commands to show up on our dashboard yet.
Our sqllogictest will interpret the CockroachDB extension sort-mode
partialsort(...) as rowsort instead. Do not add new tests involving
partialsort until partialsort is fixed.
It may be necessary to make modifications to our sqllogictest-related code in order to support and test changes to our API, such as:
You can look for sqllogictest-related code in the following directories:
Plan::CreateTable and Plan::SendDiffs.