Menu ▾ ▴

Support for 'NOT expr'?

Open Discussion
Dominik Menzi
2008-07-02
2012-12-07

  • Dominik Menzi

    Dominik Menzi - 2008-07-02

    I am using JSqlParser in a project and everything works so far.

    But when parsing something like
    select name from ENTERPRISE_BUSINESS_OBJECT where not 0=0
    I get the following error:

    net.sf.jsqlparser.JSQLParserException
        at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)
        at com.inventage.gse.util.rulemanager.AdvancedStatementProcessorTest.setUp(AdvancedStatementProcessorTest.java:87)
        at junit.framework.TestCase.runBare(TestCase.java:125)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:118)
        at junit.framework.TestSuite.runTest(TestSuite.java:208)
        at junit.framework.TestSuite.run(TestSuite.java:203)
        at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
        at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
    Caused by: net.sf.jsqlparser.parser.ParseException: Encountered "not (" at line 1, column 62.
    Was expecting one of:
        "ALL" ...
        "ANY" ...
        "NULL" ...
        "CASE" ...
        "SOME" ...
        "REPLACE" ...
        <S_DOUBLE> ...
        <S_INTEGER> ...
        <S_IDENTIFIER> ...
        <S_CHAR_LITERAL> ...
        <S_QUOTED_IDENTIFIER> ...
        "(" ...
        "?" ...
        "+" ...
        "-" ...
        "{d" ...
        "{t" ...
        "{ts" ...
        "{fn" ...
        "NOT" "EXISTS" ...
       
        at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:5153)
        at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:5034)
        at net.sf.jsqlparser.parser.CCJSqlParser.Condition(CCJSqlParser.java:1388)
        at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:1341)
        at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:1321)
        at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:1314)
        at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:1062)
        at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:634)
        at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:554)
        at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:541)
        at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:107)
        at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
        ... 14 more
    Caused by:
    net.sf.jsqlparser.parser.ParseException: Encountered "not (" at line 1, column 62.
    Was expecting one of:
        "ALL" ...
        "ANY" ...
        "NULL" ...
        "CASE" ...
        "SOME" ...
        "REPLACE" ...
        <S_DOUBLE> ...
        <S_INTEGER> ...
        <S_IDENTIFIER> ...
        <S_CHAR_LITERAL> ...
        <S_QUOTED_IDENTIFIER> ...
        "(" ...
        "?" ...
        "+" ...
        "-" ...
        "{d" ...
        "{t" ...
        "{ts" ...
        "{fn" ...
        "NOT" "EXISTS" ...
       
        at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:5153)
        at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:5034)
        at net.sf.jsqlparser.parser.CCJSqlParser.Condition(CCJSqlParser.java:1388)
        at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:1341)
        at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:1321)
        at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:1314)
        at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:1062)
        at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:634)
        at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:554)
        at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:541)
        at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:107)
        at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
        at com.inventage.gse.util.rulemanager.AdvancedStatementProcessorTest.setUp(AdvancedStatementProcessorTest.java:87)
        at junit.framework.TestCase.runBare(TestCase.java:125)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:118)
        at junit.framework.TestSuite.runTest(TestSuite.java:208)
        at junit.framework.TestSuite.run(TestSuite.java:203)
        at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
        at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

    It does work with
    select name from ENTERPRISE_BUSINESS_OBJECT where 0=0
    but neither 'NOT 0=0' nor 'NOT (0=0)' are accepted.

    I am using JSqlParser 0.4.3

    - Tangresh

     

    • Leonardo Francalanci

      Leonardo Francalanci - 2008-07-14

      Could you try 0.4.4?

       

      • Dominik Menzi

        Dominik Menzi - 2008-07-15

        I tried 0.4.4 and except for the missing isNot() for parenthesis that pdowler mentioned, I couldn't see any flaws.

         

    • Patrick Dowler

      Patrick Dowler - 2008-07-14

      I noticed that you can write "NOT ( a = b )" (in where clause) which the parser accepts, but the NOT is lost since the Parenthesis class does not support it (no isNot() method). The visit(Parenthesis) method in ExpressionDeParser would also have to be changed

      The same happens for "NOT some_column" (Column) and "NOT 123" (LongValue), "NOT 'foo'" (StringValue), "NOT NULL" (NullValue), etc. I expect these are the kinds of things that SQL allows (from a BNF point of view) even though they make little sense. Still, it would be good to not silently lose the NOT, just in case the query does actually work.

      Also in the ExpressionDeParser: in visit(ExistsExpression existsExpression) the right-hand expression comes before [NOT] EXISTS instead of after

      Patrick

       

      • Dominik Menzi

        Dominik Menzi - 2008-07-15

        Correct me if I'm wrong but I don't think SQL allows "NOT column" or "NOT 123".
        At least I don't see anything like that here: http://savage.net.au/SQL/sql-92.bnf.html#boolean%20factor

        Oracle 10 for example does not accept "select * from asdf where not 4"

         

    • Patrick Dowler

      Patrick Dowler - 2008-07-15

      Well, neither sybase nor db2 allow those constructs, but jsqlparser 0.4.4 does not reject them and silently drops the NOT. Well, the SelectDeParser does not output the NOT since Column, LongValue, etc do not support it. So I guess the problem is just that the parser is silently accepting the NOT in places it shouldn't.

      Patrick

       

    • Leonardo Francalanci

      Leonardo Francalanci - 2008-07-16

      Well: I suppose the parser accepts a lot of things that it shouldn't, but that was true even before the NOT. I suppose that the parser accepts:

      select * from asdf where a"

      So there is a much bigger problem here... I see if I can fix it.

      BTW: please feel free to add bugs in the bug database... that way we can track them in the proper way.

      Thank you

       

    • Leonardo Francalanci

      Leonardo Francalanci - 2008-07-17

      Added 0.4.5.

      This contains MAJOR changes. Please everybody download it and test it.

       

    • Patrick Dowler

      Patrick Dowler - 2008-07-17

      re: ExpressionDeParser.visit(ExistsExpression)

      I submitted a bug report (with the simple solution) for this since it was off-topic here anyway.

      On-topic: 0.4.5 seems to be good as far as NOT and use of incomplete predicates (eg columns or constants by themselves). Thanks!

       

Log in to post a comment.