Port spark sql tests to datafusion.

[liurenjie1024]

Spark sql has been widely used in large scale etl, and we have see
comet donated to apache, which acts as an execution layer of spark sql.
To further improve datafusion's compatibility with spark sql, I propose to introduce port spark sql tests.
This will help to bring datafusion to next level in etl processing.

[Omega359]

Spark functions do not yet map well to DataFusion functions so if those are included in the tests those will be an issue. I'm hoping that at some point in the (near?) future once the function migration is complete a new epic could be created to align function names, params and behaviour with the sql dialect being used (primarily I would expect PostgreSQL and Spark). That would generally be a pretty massive project though as anyone who has tried to mirror the behaviour of a different system can attest to - just talk to @andygrove.

[viirya]

For Comet, one major goal is fully compatibility with Spark, so we care about the necessary tests to make sure that. As I replied in the related ticket apache/datafusion-comet#123 (comment), porting existing Spark SQL tests to sqllogictest seems redundant because we have been running all Spark SQL tests with Comet enabled to make sure the compatibility. Not to mention that there are extra development and maintenance cost around these duplicate tests.

The idea of making DataFusion itself compatible with Spark/Postgres is another story, I think. It is not just porting tests from other engines but also much work are necessary to make DataFusion configurable to behave like/compatible with other engines.

[ozankabak]

I agree. Maybe the right place for Spark SQL tests is Comet and there is not much to gain from duplicating those here. It sounds sensible to me to have standard SQL conformity as the main goal at the upstream DataFusion level and have subprojects specialize on that, adding the tests necessary.

[liurenjie1024]

apache/datafusion-comet#123 (comment) is a different story, and at that time I didn't realize that spark already has similar data driven tests.

I may need to clarify my goal here. I think comet is a proof that datafusion is production ready. I'm thinking why not pushing datafusion/ballista to totally replace spark sql in large scale etl world. Porting spark sql tests is just first step of the goal.

[alamb]

I may need to clarify my goal here. I think comet is a proof that datafusion is production ready. I'm thinking why not pushing datafusion/ballista to totally replace spark sql in large scale etl world. Porting spark sql tests is just first step of the goal.

This is a noble goal, but it will require far more than just good code / features (docs, evangelism, etc) -- and is likely orthogonal to the goals of comet (aka it can be pursued in paralle)

[andygrove]

Another potential option here is for Comet to make spark-compatible operators and expressions available in a crate that Ballista and other projects could leverage, if they need Spark SQL support.

[alamb]

I think it is a great idea to use existing test suites to improve compatibility / correctness of DataFusion. Thank you @liurenjie1024

[ozankabak]

Let's just remain careful to follow standard SQL as default and not Spark SQL in terms of function names, syntaxes etc. We should obviously accept the latter if the user configures so, but there could be some work to do there to attain this level of flexibility.

[alamb]

I am not sure if there is any such thing as truly standard SQL

However, I think now DataFusion strives to support the Postgres dialect /semantics. Keeping these semantics are also critical for us at InfluxData

So perhaps we can strive for "postgres compatible function package" and "spark compatible function packages" 🤔

[ozankabak]

Well there is ISO SQL, which is what I look at when in doubt :) PostgreSQL is quite compliant to that, it differs every now and then but it is a very good proxy.

[alamb]

There is a standard, but I think it is basically a running joke in database implementation circles as no database actually implements the whole thing faithfully

[liurenjie1024]

Yeah, it's hard to totally follow ansi sql, especially for distributed engine. For example, spark sql's create table statement has some extension for partition, clustering, and I'm not sure if it exists in ansi sql.

[alamb]

to be clear my point is not that we shouldn't try to follow postgres faithfully, but more like since there is no universally agreed upon SQL spec, we need to be prepared for people to need different dialects and datafusion should let them implement those dialects