WITH clauses don't work with DMLs anymore

[manticore-projects]

WITH clauses don't work with DMLs anymore (since recent changes):

-- Fails now, but worked with H2 2.2.224
WITH b AS (
        SELECT  Min( valid_date ) valid_date
                , segment
                , scenario
                , id_probability_type
        FROM cfe.scenario_probability
        GROUP BY    segment
                    , scenario
                    , id_probability_type )
DELETE
FROM cfe.scenario_probability_hst a
WHERE valid_date >= (   SELECT valid_date
                        FROM b
                        WHERE segment = a.segment
                            AND scenario = a.scenario
                            AND id_probability_type = a.id_probability_type )


-- rewriting works
DELETE FROM cfe.scenario_probability_hst a
WHERE valid_date >= (   SELECT valid_date
                        FROM (  SELECT  Min( valid_date ) valid_date
                                        , segment
                                        , scenario
                                        , id_probability_type
                                FROM cfe.scenario_probability
                                GROUP BY    segment
                                            , scenario
                                            , id_probability_type ) b
                        WHERE segment = a.segment
                            AND scenario = a.scenario
                            AND id_probability_type = a.id_probability_type )
;

Please can this be resolved?
We can't rewrite 200+ valid SQL:2016 compliant statements, which have been working fine before.

[katzyn]

There is no with clause in DML in the SQL Standard.

Support of these invalid commands was intentionally removed, because correct execution of valid commands in more important for us and we can't fix everything at the same time.

WITH clause is a part of a query expression, so you need to transform your commands and move this clause to the query itself:

• WITH … INSERT … queryBody → INSERT … WITH … queryBody
• WITH … UPDATE … WHERE condition → UPDATE … WHERE … IN (WITH …) or maybe MERGE INTO … USING WITH … ON condition WHEN MATCHED THEN UPDATE …
• WITH … DELETE … WHERE condition → DELETE … WHERE … IN (WITH …) or maybe MERGE INTO … USING WITH … ON condition WHEN MATCHED THEN DELETE
• WITH … CREATE TABLE … AS queryBody -> CREATE TABLE … AS WITH … queryBody
  etc.

We can try to implement this transformation in the parser to re-invent support of this non-standard syntax in that way for related compatibility modes.

[manticore-projects]

There is no with clause in DML in the SQL Standard.

You are correct, WITH CLAUSE is only defined for a QUERY EXPRESSION https://manticore-projects.com/SQL2016Parser/syntax.html#query-expression. (I was wrong, because it is used on various RDBMS commonly.)

Support of these invalid commands was intentionally removed, because correct execution of valid commands in more important for us and we can't fix everything at the same time.

I actually do agree with this, especially as it is the standard compliant interpretation.

However, all major RDBMS are more lenient and support a WITH CLAUSE for DML statements (MERGE, INSERT, UPDATE, DELETE) and especially MS SQL Server expects the WITH Clause to show on top/at begin of the SQL.

So I would like to plead for bringing this back because (at least for us) H2 is a great "development database" which can be used for development and drafting before deploying to the large RDBMS.

[manticore-projects]

One more question please: How can I help with the documentation?
On the WebSite, still the old WITH is shown and I wonder if we want a second documentation on the latest "Snapshot".

Because right now, if someone tests the Git Snapshot and then verifies against the Documentation there would be discrepancy.

[grandinj]

We only update the website documentation when we do a release

[katzyn]

I reverted my changes in BNF of CTEs, because they break code completion, this is the same issue as #3372

[katzyn]

(It the worst case we can remove links to DML commands from the old grammar.)

[manticore-projects]

We only update the website documentation when we do a release

Yes, I do understand the status quo and I only wondered if it was not desirable to also show the current state of developing affairs. It encourages testing in practice and reduces surprised after publishing.

[katzyn]

We don't have a versioned documentation on website. There is a separate feature request for it somewhere, but we need to re-organize everything before any attempts to provide such documentation.

People who use historic versions of H2 can download old versions of documentation in PDF and HTML formats in ZIP archive with full distribution.

People who use own builds of H2 should also build a local copy of documentation with the same build script and use it.

But currently it is incorrect and we need to do something with it before the next release.

[manticore-projects]

Thank you, I do understand.
Question: did you ever consider moving to a static Markdown or AsciiDoctor or RST based web site generator like Sphinx (python based) or JBake (java based). Such tools can handle GIT based versioning including documentation. It would also make the Web Site more Mobile friendly (wich unfortunately is a SEO criterion).

If I started working on this (in a separate branch), would that effort be welcome and of help?

My understanding of the dependencies was:

1. adopt a static website builder
2. convert all existing documentation into the preferred dialect MarkDown or AsciiDoctor
3. adopt preferred H2 theming
4. adopt GIT based revisions
5. integrate into the build system (per Maven plugin)
6. fix the presentation of the Grammar and BNFs

[katzyn]

Documentation of H2 contains some static pages, but the main documentation is generated from the source data of built-in help system and Markdown is not an option here (at least for now).

We also don't use Maven and don't need any plugins for it. Maven build is an old unfinished experiment, I think it should be removed.

Currently we have only one real problem here: code completion in H2 Console goes into infinite loop (or maybe infinite recursion) with correct grammar in documentation. Other things aren't that important.

[manticore-projects]

Currently we have only one real problem here: code completion in H2 Console goes into infinite loop (or maybe infinite recursion) with correct grammar in documentation. Other things aren't that important.

Thank you for the explanations, I will try to figure this one out.

One more question though: if you want to throw out the Maven build (which always worked very well here), then how will IDEs digest/support the H2 project?

[grandinj]

One more question though: if you want to throw out the Maven build (which always worked very well here), then how will IDEs digest/support the H2 project?

H2 is a very simple project , but maybe we need some instructions for newcomers.
I normally do
(1) build on command line
(2) import as a standalone Java project
(3) configure the source folder
(4) add all the jars in the lib folders as jar dependencies

Which is not as easy as Maven, granted.

[manticore-projects]

To be fair: if there is a working maven build file one is not stupid considering it meant to be used.But yes such instructions for the inexperienced folks like me won't harm.

[manticore-projects]

Please pardon my curiosity: I looked into the building infrastructure java code in the tools package. And I saw a lot of stuff but nothing particular outstanding. Why exactly did we choose to re-invent the wheel instead of using Maven or Groovy and customize only what we need?

(No worries, I won't touch anything. This is not the hill I want to die upon.)

[grandinj]

H2 predates maven, so it wasn't around when we started.

Maven has lousy backwards compatibility, it needs fixing every 6 months or so. Our stuff just keeps on working, with no maintenance required.

Our stuff "just works" out the box, even for weird setups, like being behind a proxy, which Maven (still) cant get right.

Our stuff is debuggable using the same tools we debug normal Java with.
Maven is essentially undebuggable.

[manticore-projects]

Currently we have only one real problem here: code completion in H2 Console goes into infinite loop (or maybe infinite recursion) with correct grammar in documentation. Other things aren't that important.

Step 1) I have used the changed/improved Grammar CSV and I believe I can reproduce the problem:
a) with a normal SELECT statement (or any other statement without a WITH), auto completion works fine
b) however, as soon as a WITH ... is involved, autocomplete stops working

Step 2) I will build now a specific test for this in TestWeb and then start to trace and to debug.
I will keep you posted.