Right now, we put all of the tpch test cases (q1.slt.part, q3.slt.part......) in one test file(tpch.slt) as we want to share all the common tables from tpch.

However, a failure in q1.slt.part would abort the whole test file. But q3 has nothing to do Q1 and checking its results(if wrong) would save an extra run.

Should we abort in a finer granularity, i.e. slt.part level? Or we should introduce some primitives for specifying different dependencies between test cases?

exits (and clean-up) on first error

Sometimes, we want to know how long does some specific statement takes. The sqllogictest cli may support ways to visualize this.

One proposal is to add +timing to statement ok. e.g.,

statement ok +timing +label="flush at the first time"

Then we will see:

flush at the first time ... 1.0s

We can also simply print time for every statement.

Hi, is there a plan to support dynamive query like the following?

SELECT now();

I noticed CLI only supports Postgres currently.

I want to support Databend for it.

Currently it's put in bin, but maybe it can be put in runner, so that users who implement custom engines can use that feature 🤔

In distributed environment, the order of query result set is uncertain, which is also not required in SQL without "ORDER BY".

To simplify the test script, we can support syntax like SET DEFAULT [GLOBAL] $SortMode to set the default sort mode required by query statement in the current file or all files, instead of specify sort mode after each. query statement.

We may support the defer statement to do some clean-ups. There're several cases:

• We've created too many materialized views, and the dependency becomes too complex to resolve and write the drop statements correctly. With defer, that'll be natural.
• We may create tables with the same name in multiple tests. If one fails, others may also fail because of not dropping the table: we may also allow defer to clean-up on failures.
• We may not want to make control mode or session variable configurations bring side effects for other parts in this session.

Currently, if a tester fails, we cannot know which test fails in console -- users will need to manually export RUST_LOG=info to see the result.

We should have some interface to let user know which statement is being executed. I propose a interface like:

for next_statement in tester.run_script(&script) {
   println!("running {:?}", next_statement);
}

When next is called on iterator, next_statement will be running in sqllogictest internally.

We can also go a step further to expose all our internal states:

for (statement, verifier) in tester.parse_script(&script) {
   let result = db.run(statement).await;
   verifier.verify(result.to_string()).unwrap();
}

n values hashing to ... where n is num_rows * num_cols 🤡

If sqllogictest finds multiple files to run, it can run them in parallel.

It's hard to manually check which line is different 🤣

so that it can be used in a pipeline's fasion, or multiple globs (expanded by shell).

Currently it accepts a single glob string argument, so it also need to be quoted.

e.g.,

❯ sqllogictest ./**/*.slt  
error: Found argument './examples/condition/condition.slt' which wasn't expected, or isn't valid in this context

As a testing framework, we need to ensure our correctness. Currently we can be relatively confident whether previously passed tests will fail after upgrading sqllogictest by testing agains real usage in e.g., risingwave. But it would definitely be better to add a test suite in our repo. #123

A more dangerous problem which is also harder to detect is that we won't pass previously failed tests. Imagine that after some changes, we allow all test cases to pass. 😇 We need to test errors.

Here's an example of error that won't be run:

Maybe it's better to refactor some of examples into regular integration tests.

I'm thinking of using +label after each statement ok and query to do some custom extensions over the original sqllogictest syntax. An example here:

https://github.com/cmu-db/bustub/blob/85477ace4eb3ff6531ccfb075dbc283ff99dbdf1/test/sql/p3.14-topn.slt#L354-L359

What we can do:

• +explain print the query plan
• +repeat:10 repeat for 10 times
• +ensure:plan_node ensure there's some plan node in the plan
• +session:name run this query in a given session, e.g., to test the behavior of txn

One more thing. Do you feel set VARIABLE in sqllogictest is a file-level or statement or a statement affecting global state? Now it's implemented as latter, so if someone do:

include a.slt
include b.slt

If a.slt sets a row sort mode, b.slt will also be affected. If you feel this is counter-intuitive, maybe we can change the behavior of sqllogictest.

Originally posted by @skyzh in risingwavelabs/risingwave#3582 (comment)

Currently if a query is expected to return an error, we have to use command statement error, which does not support checking error message or error number.

Here I propose to add support for statement error <regex> and query error <regex> syntax like cockroach did.

For example:

statement error syntax error
CREAT TABLE t(a INT);
# Error: Parser Error: syntax error at or near "CREAT"

query error Overflow
SELECT 2147483647 + 1;
# Error: Out of Range Error: Overflow in addition of INT32 (2147483647 + 1)!

Just checked how sqllogictest variants handle separators in the results:

• sqlite: each row is one value instead of one row, so spaces are compared, but result cannot contain newline
• duckdb: tab separated, so spaces are compared, but result cannot contain newline and tab
• cockroach: compare "normalized rows", so whitespace characters are squashed. e.g., the following test will pass

query ITI
SELECT 1, E'a \t\n b', 2
----
1 a      b 2

The README.md file just provides a one-line description of the repository, which is pretty unclear to the audience. I would suggest improving the documentation. It would be great if we could include the following information:

• better description of this repository
• how to install the program, e.g., any dependencies, etc
• how to use the program
• others

statement error string::interval: context-dependent operators are not allowed in computed column\nHINT: STRING to INTERVAL casts depend on session IntervalStyle; use parse_interval\(string\) instead
CREATE TABLE invalid_table (
  invalid_col interval AS ('1 hour'::string::interval) STORED
)

Statement error is also a part of the testing. In some circumstances, we may expect the system to produce errors as expected.

For now, sqllogictest only support simple query mode. For introducing e2e test of extend query mode in risingwave, we should add extend query in the sqllogictest and using a opt '--extend' to choice 'simple query' or 'extended query'.

This project was originally created along with the RisingLight project, so I think we can transfer it to risinglightdb.

Currently sqllogictest returns empty string rendered as is:

query T
select concat(NULL, NULL);
----

It should render it as:

query T
select concat(NULL, NULL);
----
(empty)

As per sqllogictest docs - https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki:

Empty strings are rendered as "(empty)"

For example, given a test statement:

query I
select a from t;

Is is possible to generate a test file as:

query I
select a from t;
----
1
2
3

@BugenZhao raises an idea of printing all session variables after failure. Maybe we can support it by adding a hook on_failure for runner. (And a corresponding option for bin`

As titled, the following code

query I
insert into t values (2+2) returning *;
----
4

will produce the following error:

query result mismatch:
[SQL] insert into t values (2+2) returning *;
[Diff] (-excepted|+actual)
-   4

Sometimes, the test files are maintained by different people and they may use the same name for a table or view. This is reasonable as we may view these test files as independent units.

When some test case in one test file failed, sqllogictest proceeds to execute another test file. Without dropping all of the tables and views, it can cause table exists failure in the coming tests.

Therefore, this issue proposes that we may have a finally statement like mechanism to clean up things. Statement in the finally code block would be always executed at last.

Is this mechanism worth implementing? Not necessarily design and implement it in this way, though.

risingwavelabs/risingwave#2435

It's used in sqlite's test suite e.g.,

https://github.com/risingwavelabs/sqlite-sqllogictest/blob/aa3e4345dff63b65ef0d0969872ffa2b4a685225/test/evidence/in1.test#L39-L43

Because users generally will return Result in tests, and there's no chance to call display(true) to get colorful error message. If it's printed, it will be duplicate with the Err.

Maybe we can refer to https://docs.rs/pretty_assertions/latest/pretty_assertions/ about how can we opt-in colorful message in library usage.

https://duckdb.org/dev/sqllogictest/result_verification#row-wise-vs-value-wise-result-ordering

# row-wise
query II
SELECT 42, 84 UNION ALL SELECT 10, 20;
----
42	84
10	20

# value-wise
query II
SELECT 42, 84 UNION ALL SELECT 10, 20;
----
42
84
10
20

It would be greate to support include ./basic/*.slt.part in include statement.

If we set a session variable in a.slt, and then it will be still be set in b.slt. I think this is very error-prone, although we can do clean-up for session variable like for tables.

include problem is related but different: Currenly include's semantic is just copy-paste. Isolation for include subtests is another problem, as in #55, #81. This issue says different slts should have isolated sessions.

If so, would serial mode the same as -j1? No, -j1 will have isolated DBs. Serial mode isolates sessions, but do not isolates DBs, so clean-up tables is still needed

 query II rowsort
 select sum(v1), v2 + 1 as a, count(*) from t group by a
 ----
 3 2 2
 7 3 2
 5 4 1

We should give an error in this case, where we only specify II, but provide 3 column input.

Motivation

When writing tests for complex queries, we need to prepare data before executing queries. The most general approach is to execute insert into statements. However, mixing a lot of insert statements with queries in one file may lead to a large file which is difficult to maintain and understand.

Proposal

A good practice is to split these statements into small files and use include statements to merge them together. For example, tpch e2e tests may consist of several files: one file for creating tables, one file for each table's data, and one file for all queries, and eventually we use include command to concat them together.

include "create.slt"
include "insert_nation.slt"
include "insert_lineitem.slt"

query R
select * from lineitem;

example_basic should be t? or t should be example_basic?

It's becoming more and more complex, so it would be good to have a separate crate.

cc @xxchan do you want to work on this? If so feel free to assign yourself. Otherwise I'll do the refactor maybe this month.

Use case: a user wants to setup a dataset (e.g., tpch), then tests different set of queries. What's more, they want to test it under different session configurations. e.g., risingwavelabs/risingwave#3629 (comment)

Previously, they will:

# A.slt
include prepare.slt.part
set CONFIG=A;  
include test1.slt.part
include test2.slt.part

# B.slt
include prepare.slt.part
set CONFIG=B;  
include test1.slt.part
include test2.slt.part

The biggest problem is that the tests cannot be parallized.

I came up with another syntax:

include prepare.slt.part
session {
  statement ok
  set CONFIG=A;  
  include test1.slt.part
}
session {
  statement ok
  set CONFIG=A;  
  include test2.slt.part
}
session {
  statement ok
  set CONFIG=B;  
  include test1.slt.part
}
session {
  statement ok
  set CONFIG=B;  
  include test2.slt.part
}

In this way, we can have:

• parallism
• isolated session config. This also solves another problem: config side-effects in a subtest .slt.part #55
• isolated failure
• reuse test cases

session can be alternatived called as e.g., subtest or run.

By the way, can we go a step further? e.g., add a Result associated type for DB, so that we can pass any type apart from String as query result. (Not needed in this PR)

Also, I would recommend adding an example for validator in examples folder.

Originally posted by @skyzh in #15 (review)

    I think it makes sense to support different file extension in `sqllogictest-rs`, we can submit an issue and implement it.

Originally posted by @Xuanwo in datafuselabs/databend#9150 (comment)

We are willing to making contribution for this, any ideas?

.. which can be confusing

Current behavior is: run_file will handle include while run_script won't.

Background

I'm trying to introduce INTERVAL type in Postgres-extend engine and find a problem caused by the way we test result.

In sqllogictest::AsyncDB, our run interface requires to return a string to compare with the expect result.
async fn run(&mut self, sql: &str) -> Result<String, Self::Error>

But there are some case in which the String is different but the semantics is the same.
Such asinterval '30 days'and interval ' 720:00:00', there are different string but the same semantics.

For some type(such as interval), there are different string format to express a same thing in same semantics. So do we need to add a way to compare the 'semantics' rather than compare the 'string'. I try to think a way to fix it but I don't wether it's worth or necessary. Because this case only exist in 'Interval', 'timstamptz' and other time-related type. So we can also declare the format must be equal to result from psql.

Maybe we can fix it by...

make the result can be multi-format. Such as:

async fn run(&mut self, sql: &str) -> Result<Ans, Self::Error>
enum Ans{
  str(String),
  Interval(...)
}

We can provide a macro like sqllogictest::test_harness!("*.slt") to generate test binary using libtest-mimic. Therefore, users can integrate this library with their use cases more easily.

Reference: https://github.com/nextest-rs/datatest-stable