Start migration of stateless use cases to logic test

[ZeaLoVe]

Background

RFC ref：#5048
Code：https://github.com/datafuselabs/databend/tree/main/tests/logictest

The original stateless test cannot clearly reflect the expectation of each sql execution in the use case, mainly testing the mysql protocol, not covering different protocols such as http and subsequent clickhouse. the goal of the logic test is to ensure that the sql execution result meets the expectation and performs consistently under different handlers.

Logic Test Usage

A demo ref https://github.com/datafuselabs/databend/blob/main/tests/logictest/suites/select_0 and RFC documentation

Three main use case types are supported

• ok
  • Returns no error, don't care about the result
• error
  • Returns the result with error and expected error message, usually with an error code, but also with a message; the way to determine whether the specified string is in the returned message
• query
  • B Boolean
  • T text
  • F floating point
  • I integer

Attention

• Semi-structured is not supported in the test for now, so ignore the result or move to stateful tests.
• Since null in python is None type, it is handled as NULL (case-sensitive and requires full consistency)
• HTTP handler for bool type is different from MySQL, MySQL is 0, 1; http is true, false
• Some of the use cases have output, but the data is not captured when generated, downgraded to ok statements, needs to manually revise (note the current unsupported cases)
• Some use cases use simple library table names such as t1, db1, need to pay attention to the test before adding drop if exist to clean up the library table or use the use case related names such as t_00_0001, db_01_0002 (pay attention to maintain the idempotency of the use case)

Use Case Generator

Basic concept

• The use cases in the original /tests/suites/0_stateless/ directory can be automatically generated using scripts for the logic test, and then placed under /tests/logictest/suites/gen/.
• The generator needs to run databend locally (or set environment variables to connect to a remote environment, see Environment Variables in Docker Usage[https://github.com/datafuselabs/databend/blob/main/tests/logictest/README.md]) and generate the corresponding use case file in the gen/ directory by reading the stateless test case file, and the query results are obtained from the runtime environment. So it is possible that a certain version of the result itself is wrong. Manual calibration of the results is required.

Generated in the following way.

• ok statements with no returned content or statements that are temporarily unable to process the returned content (semi-structured)
• error statements with comments in the original use case -- {ErrorCode XXX} statements, which determine the error code by default
• query statement with return content

Usage:

Go to the logic test directory
Execute : python3 gen_suites.py .*00_0000.* (The parameter is a regular expression to filter the test file, the default is . *, means all)

Imgration Plan

• All use cases should be migrated, and those not edited after May 10 can be found in the gen directory (only use cases of .sql are processed automatically), since the auto generator can't meet the requirements precisely, please check the revised use cases by yourself.
• If the new use cases are added to the logic test, you can consider moving to stateful if the logic test cannot meet some scenarios, such as use cases written in sh or py.
• Set a point of time to close the stateless test (tentatively the second week of July to delete the original stateless CI and use cases) to maintain coexistence during the period, and then new handlers will be added, which can automatically generate a wave.

[leiysky]

cc @ZeaLoVe

It will be very useful if we can generate a full test file with a prototype test file.

For example, we can write an original prototype test file as:

statement ok
create table t(a int);

statement ok
insert into t values(1), (2), (3);

statement query I
select * from t;
----
(left empty here)

And then we can run the test and fill the result back to test file as:

python main.py --generate

This will generate a full test file:

statement ok
create table t(a int);

statement ok
insert into t values(1), (2), (3);

statement query I
select * from t;
----
1
2
3

Sometimes, developers may need to modify some test file, this mechanism will be very helpful.

[leiysky]

I found it looks unclear if we specify a complicated error pattern, for example:

statement error must appear in the GROUP BY clause or be used in an aggregate function
select number from numbers(10) group by number+1;

Maybe we should allow user to quote the pattern, or write some delimiters(e.g. :)?

cc @ZeaLoVe

[ZeaLoVe]

I found it looks unclear if we specify a complicated error pattern, for example:

statement error must appear in the GROUP BY clause or be used in an aggregate function
select number from numbers(10) group by number+1;

Maybe we should allow user to quote the pattern, or write some delimiters(e.g. :)?

cc @ZeaLoVe

It is true. Current implements with regex and plaintext compare have minimal cost, but it is hard to add more grammar(hard-code). Other sides, the error code is less mutable than the error message. I will consider it later but not now.

[ZeaLoVe]

In following days, I will start to delete test in stateless dir, and make sure the logic test completely take place of this cases.

track tasks

• 00_dummy ci: migrate stateless test 00,01 to logic test #6757
• 01_system ci: migrate stateless test 00,01 to logic test #6757
• 02_function ci: migration stateless test in 02_function #6795
• 03_dml ci: migration stateless test 03_dml and 04_explain #6839
• 04_explain ci: migration stateless test 03_dml and 04_explain #6839
• 05_ddl ci: migration stateless test 05_ddl and 06_show #6802
• 06_show ci: migration stateless test 05_ddl and 06_show #6802
• 07_use ci: migrate stateless test(07-12) #6861
• 08_optimizer ci: migrate stateless test(07-12) #6861
• 09_fuse_engine ci: migrate stateless test(07-12) #6861
• 10_drivers ci: migrate stateless test(07-12) #6861
• 11_data_type ci: migrate stateless test(07-12) #6861
• 12_time_travel ci: migrate stateless test(07-12) #6861
• 13_tpch
• 14_clickhouse_http_hander
• 20+_others ci: migration stateless test 20_others #6870

[flaneur2020]

how about adding sqlogic into the stateless test folder?

like:

stateless-tests
0_stateless/00_dummy/00_0000_dummy_select_1.sql -> 0_stateless/00_dummy/00_0000_dummy_select_1.sqlogictest

instead of:

0_stateless/00_dummy/00_0000_dummy_select_1.sql -> tests/logictest/suites/stateless/00_dummy/00_0000_dummy_select_1.sql

this approach have some benefits:

1. we can gradually replace the stateless tests without many coorination among the engineers
2. we can seperate the domains between test framework & test cases, instead of coupling them

also:

1. currently there're different formats in the stateless test including shell & python scripts, sqlogictest can replace the shell cases, but not easily replace the python scripts
2. sqlogictest may not only the replacement of stateless test, but also the tests in the stateful test, we still need seperate the stateless tests & stateful tests.