[BUG] JSQLParser Version 5.1: PostgreSQL : NPE in Function alias

[davyboyhayes]

This is an initial query used on start up of Metabase

Failing SQL Feature:

• Function alias in select item of a sub-select using as keyword throws an NPE

SQL Example:

• Simplified Query Example, focusing on the failing feature

with table_privileges as (
 select
   NULL as role,
   t.schemaname as schema,
   t.objectname as table,
   pg_catalog.has_any_column_privilege(current_user, '"' || replace(t.schemaname, '"', '""') || '"' || '.' || '"' || replace(t.objectname, '"', '""') || '"',  'update') as update,
   pg_catalog.has_any_column_privilege(current_user, '"' || replace(t.schemaname, '"', '""') || '"' || '.' || '"' || replace(t.objectname, '"', '""') || '"',  'select') as select,
   pg_catalog.has_any_column_privilege(current_user, '"' || replace(t.schemaname, '"', '""') || '"' || '.' || '"' || replace(t.objectname, '"', '""') || '"',  'insert') as insert,
   pg_catalog.has_table_privilege(     current_user, '"' || replace(t.schemaname, '"', '""') || '"' || '.' || '"' || replace(t.objectname, '"', '""') || '"',  'delete') as delete
 from (
   select schemaname, tablename as objectname from pg_catalog.pg_tables
   union
   select schemaname, viewname as objectname from pg_catalog.pg_views
   union
   select schemaname, matviewname as objectname from pg_catalog.pg_matviews
 ) t
 where t.schemaname !~ '^pg_'
   and t.schemaname <> 'information_schema'
   and pg_catalog.has_schema_privilege(current_user, t.schemaname, 'usage')
)
select t.*

Software Information:

• JSqlParser version 5.1
• PostgreSQL

Firstly, there is an exception in the parser:

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "as" "AS"
    at line 7, column 170.

Was expecting:

    ")"

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:48520)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:48341)
	at net.sf.jsqlparser.parser.CCJSqlParser.ParenthesedSelect(CCJSqlParser.java:9531)
	at net.sf.jsqlparser.parser.CCJSqlParser.WithItem(CCJSqlParser.java:10816)
	at net.sf.jsqlparser.parser.CCJSqlParser.WithList(CCJSqlParser.java:10760)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:401)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:322)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:341)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:338)
	at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:317)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
	at --- Async.Stack.Trace --- (captured by IntelliJ IDEA debugger)
	at java.base/java.util.concurrent.FutureTask.<init>(FutureTask.java:132)
	at java.base/java.util.concurrent.AbstractExecutorService.newTaskFor(AbstractExecutorService.java:113)
	at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:144)
	at java.base/java.util.concurrent.Executors$DelegatedExecutorService.submit(Executors.java:790)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:338)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:115)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)

and then a null pointer exception is thrown:

java.lang.NullPointerException: Cannot invoke "Object.toString()" because "stm" is null
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:361)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:341)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:338)
	at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:317)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
	at --- Async.Stack.Trace --- (captured by IntelliJ IDEA debugger)
	at java.base/java.util.concurrent.FutureTask.<init>(FutureTask.java:132)
	at java.base/java.util.concurrent.AbstractExecutorService.newTaskFor(AbstractExecutorService.java:113)
	at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:144)
	at java.base/java.util.concurrent.Executors$DelegatedExecutorService.submit(Executors.java:790)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:338)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:115)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)

[manticore-projects]

Greetings.

"SELECT", "UPDATE", "DELETE", "INSERT" are all reserved keywords and need to be quoted properly.

With proper quotes, your query is parsed without any problem and you can test it here.