SQL dialect support

[tyt2y3]

One of the use case for an embedded database is to mock a 'bigger' database like MySQL or Postgres.

Shall we support multiple SQL dialects?

If so, we need to design an AST that is able to support different dialects.

Then, we'd translate from the sqlparser AST to our AST.

But SQL dialect support on the frontend does not necessarily means that we'd support the full feature set of a database engine. It's just better compatibility for simple things that happens to be different but in theory trivial to implement.

[joaoh82]

I like this idea of using it to mock bigger databases. But like you said, doesn't necessarily means we would support the entire feature set of all of them, that would be a gigantic job to be honest, and I don't think it is our goal.

But the idea of supporting multiple SQL Dialects and a subset of the features so it can used as a mock databases during test is interesting. I am all for it.

I think we can at least start with something like https://github.com/ballista-compute/sqlparser-rs, which is even already being used on the project. The library does a good job in dealing with different dialect, as you can see here in their docs. https://docs.rs/sqlparser/0.8.0/sqlparser/dialect/index.html.

This lib has good support and it is almost hitting release 1.0.0.

What do you think?

[tyt2y3]

Yes I agree that sqlparser is a good start for parsing SQL.
My actual question is should we have a lower level AST than the one produced by sqlparser-rs that desugar things and reduce its complexity (in terms of depthness and type variants).
For example, to one extreme, we can reduce the tree to an entirely linear representation (which is not a tree at all).

[joaoh82]

Ah yes, now I understand.
I have a couple of examples here to make it easier to visualize it. The depth of it is actually pretty annoying. I formatted this way to make easier to visualize it when I was developing the first version.

Anyway, a more linear and less complex is not a bad idea. Any idea on how to approach this? Or idea on how it could look like.

[tyt2y3]

Yes I have some thoughts, basically something like an IR.

For example

SELECT A.a, B.b from A LEFT JOIN B ON A.i = B.i WHERE A.a = 'a' and B.b = 'b'

Would be

TABLE_SOURCE A
TABLE_SOURCE B
JOIN_ON A.i = B.i
FILTER A.a = 'a'
FILTER B.b = 'b'
OUTPUT A.a
OUTPUT B.b

Though the exact semantics would depends on the design of the execution engine.

[joaoh82]

Man, this actually looks pretty cool. If we could simply the AST to something linear like that would be awesome.

I am just thinking if we can keep supporting complex queries with this model or if we are ok with giving up some things.

[tyt2y3]

What do you mean by 'complex queries'? If you are talking subquery, then yes our IR should handle that using the concept of 'temp table'.

The complexity has to be handled by some layer (above or below), but I think the goal of this IR is to help the execution engine analyse and perform the query.

[joaoh82]

Yeah, subqueries is an example.
A query like this is quite simple to represent linearly.
SELECT A.a, B.b from A LEFT JOIN B ON A.i = B.i WHERE A.a = 'a' and B.b = 'b'
As you add more nested queries and comparisons it might be more complicated to represent linearly.

Specially if we are dealing with multi dialect support.

Just raising the question.

[tyt2y3]

If you like the idea, the precise design of the IR will be more clear after we nailed the design of the execution engine.
Because it should roughly maps to the internal concepts (pipeline stages and operators etc) of it.

[joaoh82]

I like it. really do. :)
So the idea would be to translate the different SQL Dialects to a common linear IR mapping internal concepts and etc?