JSqlParser
JSqlParser copied to clipboard
JSQLParser
Adding WITH DATA statement
Describe the bug
When I try to parse the following SQL :
CREATE TABLE empty_nation AS SELECT * FROM nation WITH DATA
I got a exception (Full Stack Trace is copied later ):
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "WITH" "WITH" at line 4, column 1.
Was expecting one of:
";" "ACTION" "ACTIVE"
This SQL comes from the Trino / Presto Documentation . I would like to know if it is possible to add this features to JSQLParser ?
To Reproduce Steps to reproduce the behavior:
- Example SQL : CREATE TABLE empty_nation AS SELECT * FROM nation WITH DATA;
- Parsing this SQL using JSqlParser with this statements :
String selectRequest = sql;
net.sf.jsqlparser.statement.Statement statement = pm.parse(new StringReader(sql));
if (statement instanceof CreateTable) {
CreateTable ct = (CreateTable) statement;
Select select = ct.getSelect();
selectRequest = select.getSelectBody().toString();
System.out.println("EXPLAIN " + select.getSelectBody().toString());
}
- Exception
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "WITH" "WITH" at line 4, column 1.
Was expecting one of:
";" "ACTION" "ACTIVE" "ALGORITHM" "ARCHIVE" "ARRAY" "AS" "AT" "BYTE" "CASCADE" "CASE" "CAST" "CHANGE" "CHAR" "CHARACTER" "CHECKPOINT" "COLUMN" "COLUMNS" "COMMENT" "COMMIT" "CONNECT" "COSTS" "CYCLE" "DBA_RECYCLEBIN" "DEFAULT" "DESC" "DESCRIBE" "DISABLE" "DISCONNECT" "DIV" "DO" "DUMP" "DUPLICATE" "EMIT" "ENABLE" "END" "EXCLUDE" "EXTRACT" "FALSE" "FILTER" "FIRST" "FLUSH" "FN" "FOLLOWING" "FORMAT" "FULLTEXT" "GROUP" "HAVING" "HISTORY" "INDEX" "INSERT" "INTERVAL" "ISNULL" "JSON" "KEY" "LAST" "LEADING" "LIKE" "LINK" "LOCAL" "LOG" "MATERIALIZED" "NO" "NOLOCK" "NULLS" "OF" "OPEN" "OVER" "PARALLEL" "PARTITION" "PATH" "PERCENT" "PIVOT" "PRECISION" "PRIMARY" "PRIOR" "QUERY" "QUIESCE" "RANGE" "READ" "RECYCLEBIN" "REGISTER" "REPLACE" "RESTRICTED" "RESUME" "ROW" "ROWS" "SCHEMA" "SEPARATOR" "SEQUENCE" "SESSION" "SHUTDOWN" "SIBLINGS" "SIGNED" "SIZE" "SKIP" "START" "SUSPEND" "SWITCH" "SYNONYM" "SYSTEM" "TABLE" "TABLESPACE" "TEMP" "TEMPORARY" "TIMEOUT" "TO" "TOP" "TRUE" "TRUNCATE" "TRY_CAST" "TYPE" "UNQIESCE" "UNSIGNED" "USER" "VALIDATE" "VALUE" "VALUES" "VIEW" "WINDOW" "XML" "ZONE" <EOF> <K_DATETIMELITERAL> <K_DATE_LITERAL> <K_NEXTVAL> <K_STRING_FUNCTION_NAME> <S_CHAR_LITERAL> <S_IDENTIFIER> <S_QUOTED_IDENTIFIER>at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:25)
Expected behavior I would like to be able to parse this type of expression.
System
- Database you are using : Trino v 360
- Java Version : 11
- JSqlParser version : 4.4-SNAPSHOT
Simply not supported and far away from any SQL standard compliance.
Thanks for your quick response.
Simply not supported and far away from any SQL standard compliance. Do you mean it will never be implemented because it is considered far from SQL Standard ? If somebody was interested in extending SQLParser, it shoud be possible ?
Do you mean it will never be implemented because it is considered far from SQL Standard ? If somebody was interested in extending SQLParser, it shoud be possible ?
You can either send a Pull Request or sponsor the development of this feature (e.g. supporting the Trino/Presto syntax).
@abourakba Is this something like SqlServer hints (with (index) )?
From what I read, its a syntax for a CTE with/ or without data insertion. Standard Compliant rewrite was
create table foo as
select *
from bar
limit 0;
insert into foo select * from bar;
Although the logic has also been inverted.
I ended removing the [WITH DATA] / [WITH NO DATA] Then It worked for the sql I treated.