Failed to parse valid "SELECT" statement containing table aliases

[nkubala]

I am trying to parse a query which contains columns qualified with their table name, which has been aliased. However, the parser seems to choke at random instances of aliases. For example:

SELECT d.id, d.uuid, d.name, d.amount, d.percentage, d.modified_time FROM discount d LEFT OUTER JOIN discount_category dc ON d.id = dc.discount_id WHERE merchant_id=? AND deleted=? AND dc.discount_id IS NULL AND modified_time<? AND modified_time>=? ORDER BY modified_time

gives me the following error:

Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " <S_IDENTIFIER> "dc "" at line 1, column 186.
Was expecting one of:
    "NOT" ...
    "(" ...
    "(" ...
    "NOT" ...
    "NOT" ...
    "NOT" ...
    "NOT" ...
    "(" ...
    "(" ...
    "(" ...
+about 40 more of the same line

Any ideas here? My first guess would be maybe when aliasing the table, I have LEFT OUTER JOIN discount_category dc instead of LEFT OUTER JOIN discount_category AS dc, so since the AS is missing the parser does not know what alias to look for...however, it successfully got through the first instance of the alias (d.id = dc.discount_id) so I'm not sure why it would be failing later.

[wumpz]

I will look into it.

[wumpz]

Strange. Using JSqlParser 0.9.4-SNAPSHOT (but I would guess 0.9.x as well) I have no problem parsing your statement. Which version do you use?

[nkubala]

I'm using 0.9.

<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9</version>

I've got a few other examples of queries that give me similar errors. I'll send them your way if you want.

[wumpz]

Could you try 0.9.3.

[wumpz]

any results?

[nkubala]

sorry for the delay. still happening to me after switching to 0.9.3 :(

SELECT r.id, r.uuid, r.name, r.system_role FROM role r  WHERE r.merchant_id = ? AND r.deleted_time IS NULL ORDER BY r.id DESC (SchemaService)
net.sf.jsqlparser.JSQLParserException
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:51)
    ...
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " <S_IDENTIFIER> "r "" at line 1, column 85.
Was expecting one of:
    "NOT" ...
    "(" ...
    "(" ...
    "NOT" ...
    "NOT" ...
    "NOT" ...
    "NOT" ...
    "(" ...
    "(" ...

    at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:12109)
    at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:11965)
    at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:3215)
    at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:3134)
    at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:3113)
    at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:2707)
    at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:1389)
    at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:1281)
    at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:1268)
    at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:74)
    at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:54)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:49)
    ... 34 more

[wumpz]

Again, I was able to parse your statement. I included both of your SQLs as tests (SelectTest.testIssue154, SelectTest.testIssue154_2). Are you able to build JSqlParser 0.9.4-SNAPSHOT from scratch? Are the tests succeeding here?

My guess is you have an encoding Problem. There are some invisible characters within your SQL that are lost using copy and past the SQL into GitHub.

[nkubala]

very strange that you're not able to reproduce this....I built 0.9.4-SNAPSHOT from source and tried it, and got the same results. I checked the string did not contain any extraneous characters (extra spaces, newlines, etc) and it does not...I'm really not sure why we get different results. possibly your environment is configured differently than mine?

for now I have workarounds for this issue, so it's not incredibly urgent that we address it. just trying to help though!

[wumpz]

travis ci does succeed as well.

[wumpz]

Tested here with Java 1.7, 1.8, travis uses oracle jdk and open jdk. So indeed, very weird.

[wumpz]

Could we close this issue? My status is still: not reproducable.

[nkubala]

sure, I do still think it's a (small) issue but I made a workaround so it's not important for now.