JSqlParser Exception thrown when parsing mysql create view

code:

Statement stmt = CCJSqlParserUtil.parse(sql);

sql:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `testview` AS select * from testtable

exception:

Exception in thread "main" net.sf.jsqlparser.JSQLParserException
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:56)
	at name.jacktang.JsqlparserTest.main(JsqlparserTest.java:16)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " <S_QUOTED_IDENTIFIER> "`root` "" at line 1, column 36.
Was expecting one of:
    "ON" ...
    "KEY" ...
    "NOT" ...
    "NULL" ...
    "WITH" ...
    "TABLE" ...
    "WHERE" ...
    "USING" ...
    "DELETE" ...
    "UPDATE" ...
    "PRIMARY" ...
    "FOREIGN" ...
    "CONSTRAINT" ...
    "REFERENCES" ...
    "CHECK" ...
    "ROWS" ...
    "COMMIT" ...
    "UNIQUE" ...
    "CASCADE" ...
    <K_TIME_KEY_EXPR> ...
    "TABLESPACE" ...
    "EXCLUDE" ...
    <S_DOUBLE> ...
    <S_LONG> ...
    <S_IDENTIFIER> ...
    <S_CHAR_LITERAL> ...
    "=" ...
    "(" ...
    "+" ...
    "-" ...
    <S_IDENTIFIER> ...
    "NULL" ...
    "NOT" ...
    "PRIMARY" ...
    "FOREIGN" ...
    "REFERENCES" ...
    "KEY" ...
    <S_CHAR_LITERAL> ...
    "+" ...
    "-" ...
    <S_LONG> ...
    <S_DOUBLE> ...
    "ON" ...
    "COMMIT" ...
    "ROWS" ...
    "UNIQUE" ...
    "CASCADE" ...
    "DELETE" ...
    "UPDATE" ...
    <K_TIME_KEY_EXPR> ...
    "=" ...
    "USING" ...
    "TABLESPACE" ...
    "(" ...
    "CHECK" ...
    "CONSTRAINT" ...
    "WITH" ...
    "EXCLUDE" ...
    "WHERE" ...
    
	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16869)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16722)
	at net.sf.jsqlparser.parser.CCJSqlParser.CreateTable(CCJSqlParser.java:8651)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:168)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54)
	... 1 more
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered " <S_QUOTED_IDENTIFIER> "`root` "" at line 1, column 36.
Was expecting one of:
    "ON" ...
    "KEY" ...
    "NOT" ...
    "NULL" ...
    "WITH" ...
    "TABLE" ...
    "WHERE" ...
    "USING" ...
    "DELETE" ...
    "UPDATE" ...
    "PRIMARY" ...
    "FOREIGN" ...
    "CONSTRAINT" ...
    "REFERENCES" ...
    "CHECK" ...
    "ROWS" ...
    "COMMIT" ...
    "UNIQUE" ...
    "CASCADE" ...
    <K_TIME_KEY_EXPR> ...
    "TABLESPACE" ...
    "EXCLUDE" ...
    <S_DOUBLE> ...
    <S_LONG> ...
    <S_IDENTIFIER> ...
    <S_CHAR_LITERAL> ...
    "=" ...
    "(" ...
    "+" ...
    "-" ...
    <S_IDENTIFIER> ...
    "NULL" ...
    "NOT" ...
    "PRIMARY" ...
    "FOREIGN" ...
    "REFERENCES" ...
    "KEY" ...
    <S_CHAR_LITERAL> ...
    "+" ...
    "-" ...
    <S_LONG> ...
    <S_DOUBLE> ...
    "ON" ...
    "COMMIT" ...
    "ROWS" ...
    "UNIQUE" ...
    "CASCADE" ...
    "DELETE" ...
    "UPDATE" ...
    <K_TIME_KEY_EXPR> ...
    "=" ...
    "USING" ...
    "TABLESPACE" ...
    "(" ...
    "CHECK" ...
    "CONSTRAINT" ...
    "WITH" ...
    "EXCLUDE" ...
    "WHERE" ...
    
	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16869)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16722)
	at net.sf.jsqlparser.parser.CCJSqlParser.CreateTable(CCJSqlParser.java:8651)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:168)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54)
	at name.jacktang.JsqlparserTest.main(JsqlparserTest.java:16)

Jan 04 '18 08:01 JackEggie

by the way, this create view sql is from mysql binlog.

Jan 04 '18 08:01 JackEggie

Jsqlparser does not support this kind of create view options.

Jan 04 '18 09:01 wumpz

so are this kind of options in your roadmap?

Jan 04 '18 09:01 JackEggie

is binary not supported as well?

sql:

create table test(
    id varchar(20),
    name varchar(20),
    age datetime,
    job binary,
    PRIMARY KEY (id)
);

exception:

Exception in thread "main" net.sf.jsqlparser.JSQLParserException
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:56)
	at name.jacktang.JsqlparserTest.main(JsqlparserTest.java:14)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "BINARY" "binary "" at line 5, column 9.
Was expecting one of:
    "XML" ...
    "INTERVAL" ...
    "CHARACTER" ...
    "BIT" ...
    <K_DATETIMELITERAL> ...
    "DOUBLE" ...
    <DT_ZONE> ...
    <S_IDENTIFIER> ...
    
	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16869)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16722)
	at net.sf.jsqlparser.parser.CCJSqlParser.ColDataType(CCJSqlParser.java:9265)
	at net.sf.jsqlparser.parser.CCJSqlParser.CreateTable(CCJSqlParser.java:9032)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:168)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54)
	... 1 more
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered " "BINARY" "binary "" at line 5, column 9.
Was expecting one of:
    "XML" ...
    "INTERVAL" ...
    "CHARACTER" ...
    "BIT" ...
    <K_DATETIMELITERAL> ...
    "DOUBLE" ...
    <DT_ZONE> ...
    <S_IDENTIFIER> ...
    
	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16869)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16722)
	at net.sf.jsqlparser.parser.CCJSqlParser.ColDataType(CCJSqlParser.java:9265)
	at net.sf.jsqlparser.parser.CCJSqlParser.CreateTable(CCJSqlParser.java:9032)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:168)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54)
	at name.jacktang.JsqlparserTest.main(JsqlparserTest.java:14)

Jan 05 '18 08:01 JackEggie

@JackEggie your second query is now parseable

Jun 28 '20 23:06 AnEmortalKid

Can be parsed as UnsupportedStatement as shown in #1945 (unless you want to provide or sponsor a specific implementation).

Apr 18 '24 12:04 manticore-projects