[FEATURE] Optional dialect- and version-aware parsing / validation

[minleejae]

Title: [FEATURE] Optional dialect- and version-aware parsing / validation

Hi, @manticore-projects

I'd like to ask whether there is any interest in supporting an optional dialect- and version-aware parsing/validation mode in JSqlParser.

As I understand it, JSqlParser is intentionally designed as a general, RDBMS-agnostic SQL parser. It uses a unified grammar that accepts syntax from multiple SQL dialects, which is very useful when the target database is unknown or when applications need to handle SQL from different systems.

However, this also means that successful parsing does not necessarily imply that a statement is syntactically valid for a specific DBMS, or for a particular version of that DBMS.

For example, syntax and keywords can differ between PostgreSQL, MySQL, SQL Server, Oracle, etc., and sometimes constructs from different dialects can be ambiguous when they are handled by the same grammar.

I wonder if JSqlParser would be interested in eventually supporting something along the lines of:

parse(sql, dialect = PostgreSQL, version = 16)

or, depending on the preferred architecture, a separate dialect-aware validation step after parsing.

The existing RDBMS-agnostic behavior could remain the default, while users who know their target database could optionally specify a dialect (and possibly a version).

I think this could have a few benefits:

• More deterministic parsing in ambiguous cases
  When the target dialect is known, constructs that have different meanings across dialects could be interpreted according to that dialect.

• Dialect-specific syntax validation
  A statement that happens to match the unified JSqlParser grammar could still be rejected if the syntax is not supported by the selected DBMS/version.

• Better handling of dialect evolution
  SQL features, reserved keywords, and grammar rules change between DBMS versions, so specifying a version could make parsing/validation more precise.

• Better tooling on top of JSqlParser
  Linters, migration tools, IDE integrations, static analysis tools, etc. could distinguish between "JSqlParser can parse this" and "this syntax is supported by PostgreSQL 16", for example.

I'm not suggesting that JSqlParser should stop being a general-purpose / RDBMS-agnostic parser. Rather, I'm wondering whether an optional strict dialect-aware mode would fit the project's direction.

I also realize that maintaining complete grammars for every DBMS and every version could be a significant amount of work. It might make sense to approach this incrementally, for example by starting with one dialect and defining an extensible mechanism for dialect/version-specific rules.

Would this kind of functionality be something the project maintainers would be interested in?

If so, I'd also be interested in contributing to the design and implementation, including tests and dialect-specific grammar/validation rules. I'd be happy to start with a smaller scope or a particular RDBMS if that would be more practical.

[manticore-projects]

Good to hear from you again @minleejae.

This is an old discussion and I can only offer my personal view:

1. there is already the VALIDATION frame work, which tried that -- technically, it is really good but practically it never really lifted off.

2. if we'd designed afresh from scratch, maybe based on SQL:2016 standard, then this would make sense

3. maintenance for Dialects will be an aweful lot of work, especially across versions

Would it be nice to have? Certainly! Do I think its feasible: No. JSQLParser just works "good enough" to justify this effort and there is no pressure or incentive to make this happen.

What we could do:

• give up on RDBMS specific Versions (e.g. focus on the latest know "stable" version only), then create DIALECTS as Features which we can use in semantic LOOKAHEAD
• however, as soon as a RDBMS catches up (e.g. DuckDB just recently introduced MERGE or H2 eventually implementing PIVOT) this will be messy to overhaul.

Sorry for having not a more positive answer so far.

[manticore-projects]

If you really WANT to go this route, maybe start with a clone of the Grammer and focus on one particular RDBMS (maybe Postgres) and rip out anything that is not Postgres compatible.
Ripping out and reducing the Grammar should always work. Then we could offer specific JSQLParser binaries per Gradle configuration.

Not that I was a big fan of this, but it could work.

The challenge will be again: maintenance, whenever the main Grammar changes you will need to follow up and you may be busy at that time and nobody else will care. This happened to the Validation Framework.