[DISCUSSION] More SqlLogicTest test coverage for queries, including join queries

[findepi]

During SDF's upgrade to DataFusion 43 we found a bug #13425 . This was possible thanks to extensive test coverage for certain query shapes that we have internally, which is good. The bad part is that the bug could be caught at the PR stage, or before the release, should similar tests exist in DataFusion project.

Would it be useful if there were more SLT query tests, including the ones that allowed to catch #13425?

cc @alamb @schulte-lukas @gliga

[alamb]

Yes, 100% I think adding additional test coverage in all areas would be super helpful

We / I also regularly hit bugs that are caught by InfluxData's test suite during upgrades of DataFusion but were not caught during the DataFusion PR

Other Areas that would benefit from improved testing from my experience:

1. Metadata handling (like attaching field level metadata, etc)
2. UNION coverage (which we use extensively)
3. Dictionary handling (which we also use a lot)

[Dandandan]

One way to extend join coverage is to have some more "answer checking" for different benchmarks, such as:

• Verify TPC-DS answers #13073

(Earlier doing this for TPC-H found many issues in joins handling, decimal issues, etc.)

We can do the same for the imdb join order benchmark, clickbench, etc.

[Omega359]

I have been looking over the sqllogictests this weekend and I think first of all that they need a good reorganization to clean them up, split them into smaller chunks testing just specific portions of functionality and to reduce the size of some of the files.

I also have started looking at other projects's tests to see how they've done their testing. I am unsure about actually incorporating some of those tests directly into DF (duckdb for example, MIT license vs Apache) but they could be used as a basis for seeing where DF's test suite is falling short and expanding the test suite some more.

[Omega359]

I came across this paper which I found interesting for join testing. It won't work for DF yet since DF doesn't support query hints (at least as best as I know).

[schulte-lukas]

I would also love to see a separate test-runner (maybe nightly) of the official SQLLite SLTs (where applicable).

At this point DF seems to be able to pass the majority without modification (except delete). Since they are (a) pre-vetted by sqllite and (b) test foundational capability it would make sense to me that they are included in datafusion

[Omega359]

I would also love to see a separate test-runner (maybe nightly) of the official SQLLite SLTs (where applicable).

At this point DF seems to be able to pass the majority without modification (except delete). Since they are (a) pre-vetted by sqllite and (b) test foundational capability it would make sense to me that they are included in datafusion

I actually am part way through porting those slt's to something that df's slt test runner can run

[alamb]

Can't wait to see what happens here

[comphead]

We / I also regularly hit bugs that are caught by InfluxData's test suite during upgrades of DataFusion but were not caught during the DataFusion PR

I'm wondering what are test strategies in Influx? is it something we can reuse in DF as well to avoid regression

[alamb]

I'm wondering what are test strategies in Influx? is it something we can reuse in DF as well to avoid regression

We have a bunch of our own basic tests for end to end functionality in our system (some is SQL based, some is "InfluxQL" based)

We have often seen issues where features we use heavily (metadata, and UnionExec, for example) are not as heavily covered in the existing DataFusion test suites 🤔

[comphead]

I'm wondering what are test strategies in Influx? is it something we can reuse in DF as well to avoid regression

We have a bunch of our own basic tests for end to end functionality in our system (some is SQL based, some is "InfluxQL" based)

We have often seen issues where features we use heavily (metadata, and UnionExec, for example) are not as heavily covered in the existing DataFusion test suites 🤔

is it real world use cases ?

if its not I found something used for MariaDB and get investigate it https://mariadb.com/kb/en/random-query-generator-tests/

[Omega359]

After the slt work I was thinking of taking a look at the work that @2010YOUY01 has done with sqlancer to see if there are areas that I could add into it. I noticed that the datafusion-contrib for that was merged into sqlancer main a few months back - https://github.com/sqlancer/sqlancer/tree/main/src/sqlancer/datafusion.

sqlancer has quite a few options wrt test oracles that might be useful to look at. The one I was looking at (DQP) I think would need support for query hints (or corresponding set options) to function but if we could get it to work would allow a lot of join testing to happen.

[alamb]

I agree -- I think using sqlancer is a great idea -- I suspect it simply needs someone to maintain and file tickets