[BUG] JSQLParser Version 4.7 : WithItems moved inside Select

[serhiizapalskyi]

Failing SQL Feature:

I'm not sure is it a bug or feature, but unfortunately it breaks our current behavior. After migration from 4.5 to 4.7 the query WITH events as (select * from table1) select t1 from events is parsed with select body including WithItems. In previous versions Select.getSelectBody() was returning select without WithItems.

So Select.getSelectBody (I know it is deprecated) will result different in 4.5 and 4.7:
4.5: select t1 from events
4.7 WITH events as (select * from table1) select t1 from events

I see that Select structure was changed:
In 4.5 there was separately selectBody and withItems
In 4.7 withItems are included into select

The question:

Can I get select without WithItems (like it was in 4.5)?

SQL Example:

• Simplified Query Example, focusing on the failing feature

        WITH events as (
               select * from table1
         ) 
         select t1 
         from  events
  

Software Information:

• JSqlParser version 4.7

Tips:

The same query has different structure in different versions of parser

4.5 Structure:

4.7 Structure:

Thanks in advance,
Regards, Serhii Zapalskyi

[manticore-projects]

Greetings!

Thank you for your question. Although I have read it carefully three time and I am still not sure what your are asking for.
With JSQLParser-4.7 your AST will look like shown below. Your can try this by yourself online and interactively.

Are you trying to to eliminate the With clause from the PlainSelect? If so, then why not just ignore the PlainSelect's property withItemsList or set it to NULL (in case your are rewriting).

[manticore-projects]

Here is the Java API for PlainSelect showing how to modify the withItemsList property:
https://manticore-projects.com/JSQLParser/javadoc_stable.html#select

[serhiizapalskyi]

What I want, to use select like it was in previous version:

1. get whole select body without withItems from initial select (select t1 from events)
2. hold initial select unchanged:

WITH events as (
           select * from table1
     ) 
     select t1 
     from  events

3. It would be great to understand why it has changed.
   Before 4.7:

After:

[serhiizapalskyi]

And I have one more question about this bug #1856

I'm migrating from 4.0 to 4.7 and IGNORE NULLS in ARRAY_AGG parsed successfully in 4.2, but does not in 4.3. Can you confirm that it is the same issue which is described in here ?

This is the failed query

select ARRAY_AGG(DISTINCT NULLIF(wm_uservars_name, '') IGNORE NULLS) from ecs_db.prod_db_sessions_1

[manticore-projects]

What I want, to use select like it was in previous version:

1. get whole select body without withItems from initial select (`select t1  from  events`)

2. hold initial select unchanged:

If you are interested in the AST, then access the PlainSelect and just ignore its property WithItemsList.
If you are interested in deparsing the AST, then either set the property WithItemsList to NULL or overwrite the WithItem Visitor.

3. It would be great to understand why it has changed.

For various reasons, but the most obvious is the fact that a) the With clause belongs to a statement and can't exist on its own and b) this statement depends on the With clause and won't execute without it (unless the With is redundant). So WithItemsList is a natural property of a Statement.

[manticore-projects]

Can you confirm that it is the same issue which is described in here ?

Very likely, ARRAY_AGG is a special function with a special parameter syntax which has not been implemented.
You would need to provide or sponsor an implementation when you are interested in this particular feature.

[serhiizapalskyi]

Thank you for the answers