[Epic] Data Driven Tests

[alamb]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
As described in #4248 and implemented in #4395 we now have a sqlogictest based test framework in DataFusion 🎉

This ticket tracks the various follow on work we would like to do

Describe the solution you'd like

Basically we would like to follow the DuckDB variant of sqllogictest as much as possible: https://duckdb.org/dev/sqllogictest/intro

The current rules for verification can be found in: https://duckdb.org/dev/sqllogictest/result_verification

Items

• Make a data driven SQL testing tool (so we can reuse duckdb test suite, example) #4248
• Add sqllogictests (v0) #4395
• Drive sqllogictest runner on directory contents rather than hard coded list #4471
• Support CREATE TABLE table_name(...schema_fields) #4396
• Support INSERT INTO statement #4397
• Implement column number / column type verification for sqllogictest #4499
• Implement null / empty string handling for sqllogictest #4500
• Remove tests from sql_integration that were ported to sqllogictest #4498
• Replace python based integration test with sqllogictest #4462
• Fix the sqllogictest runner on Windows #4494
• Port sql tests written in Rust to sqllogictests #4495
• Port existing sqllogictests from other systems to DataFusion
• Port remaining sql integration tests from rust to sqllogictest #6195
• Support writing lists in the arrow csv writer #4502
• Allow ArrowCSV writer to control the display of NULL values arrow-rs#3268
• sqllogictest: A logging and command line filter #4497
• Remove sqllogictests CI run #4504
• Make it easier to update sqltestlogic test expected output ("test script completion mode") #4570
• Support sub directories in sqllogictest runner #4709
• Run all sqllogictest, even if there are test failures in between #4734
• Use Clap for sqllogictest #4769
• Port other sql_integration test cases to sqllogictest #4870
• [sqllogictest] Don't orchestrate the postgres containers with rust / docker #5009
• [sqllogictest] Consolidate normalization code for the postgres and non-postgres paths  #5010
• [sqllogictest] Remove integration-tests directory #5011
• Add casting from arbitrary arrow types #5016
• Faster sqllogictest runs #6392
• Move tpch tests into sqllogictest. #6405

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
cc @mvanschellebeeck @xudong963

[xudong963]

I'll add more TODOs tomorrow.

[alamb]

@xudong963 I think a fun project might be to figure out how to support a minimal implementation of INSERT to insert data. Maybe by implementing something entirely in the sqllogictest harness using ArrayBuilders 🤔 It doesn't have to handle errors well initially or be performant.

I wonder if something crazy like forming a single row record batch from the input, and then concat that batch with any existing batches would be good enough 🤔

[xudong963]

@alamb Today I have a more crazy idea: we can use Postgres to run create table and insert into ... for DF.

To implement the idea, we can register two DBs for sqllogictest runner, PG and DF.

For pg: we can register Postgres client in our logictest by use tokio_postgres::Client and start Postgres server in docker.

Here is a draft PR to verify if my idea is feasible in sqllogictest-rs: xudong963/sqllogictest-rs#1

[alamb]

🤔 I wonder @xudong963 how will inserting data into postgres allow us to insert data into DataFusion 🤔

[alamb]

Also, heads up I have some ideas on how to improve the sqllogic framework #4492 (comment)

[xudong963]

🤔 I wonder @xudong963 how will inserting data into postgres allow us to insert data into DataFusion 🤔

Seems it's impossible, my mistake.

[xudong963]

I wonder if something crazy like forming a single row record batch from the input, and then concat that batch with any existing batches would be good enough 🤔

Make sense to me. But let me think more and find out if there are some other ways.

[mvanschellebeeck]

hey @alamb could you also add #4495 and #4494 as issues under the epic?

[xudong963]

hey @alamb could you also add #4495 and #4494 as issues under the epic?

added

[alamb]

Update: check out the insert proposal from @xudong963 here #4496 🥳 🦜

[mvanschellebeeck]

@xudong963 / @alamb - could we also add #4502

[liurenjie1024]

@alamb Please help to add #6405 and assign it to me.

[alamb]

@alamb Please help to add #6405 and assign it to me.

Done @liurenjie1024 -- thank you

[alamb]

I think I am going to claim this epic is substantially done so I am closing it out (we have pretty good data driven testing now) The rest of the stragglers I think are tracked in their own tickets without issue