Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4041

NullPointerException on query with misplaced DERBY-PROPERTIES clause

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.3.0, 10.4.2.0, 10.5.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Low

      Description

      The following sql throws a NullPointerException
      ij> CREATE TABLE "APP"."T1" ("I" INTEGER, "J" INTEGER);
      0 rows inserted/updated/deleted
      ij> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10);
      5 rows inserted/updated/deleted
      ij> CREATE TABLE "APP"."T2" ("I" INTEGER, "J" INTEGER);
      0 rows inserted/updated/deleted
      ij> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10);
      5 rows inserted/updated/deleted
      ij> CREATE TABLE "APP"."T3" ("A" INTEGER, "B" INTEGER);
      0 rows inserted/updated/deleted
      ij> insert into T3 values (1,1), (2,2), (3,3), (4,4), (6, 24),
      (7, 28), (8, 32), (9, 36), (10, 40);
      9 rows inserted/updated/deleted
      ij> insert into t3 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20;
      10 rows inserted/updated/deleted
      ij> update t3 set b = 2 * a where a > 10;
      10 rows inserted/updated/deleted
      ij> CREATE TABLE "APP"."T4" ("A" INTEGER, "B" INTEGER);
      0 rows inserted/updated/deleted
      ij> insert into t4 values (3, 12), (4, 16);
      2 rows inserted/updated/deleted
      ij> insert into t4 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20;
      10 rows inserted/updated/deleted
      ij> update t4 set b = 2 * a where a > 10;
      10 rows inserted/updated/deleted
      ij> select count from (select * from t1 union select * from t3 --DERBY-PROPERTIES joinStrategy=HASH
      ) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = x2.e ;
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
      java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
      at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:505)
      at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347)
      at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)
      at org.apache.derby.impl.tools.ij.Main.go(Main.java:210)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:177)
      at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
      at org.apache.derby.tools.ij.main(ij.java:59)
      Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
      9)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
      ... 16 more
      Caused by: java.lang.NullPointerException
      at org.apache.derby.impl.sql.compile.OptimizerImpl.getFinalCost(OptimizerImpl.java:2498)
      at org.apache.derby.impl.sql.compile.SelectNode.getFinalCostEstimate(SelectNode.java:1987)
      at org.apache.derby.impl.sql.compile.UnionNode.getFinalCostEstimate(UnionNode.java:653)
      at org.apache.derby.impl.sql.compile.SetOperatorNode.modifyAccessPath(SetOperatorNode.java:169)
      at org.apache.derby.impl.sql.compile.ProjectRestrictNode.modifyAccessPath(ProjectRestrictNode.java:718)
      at org.apache.derby.impl.sql.compile.OptimizerImpl.modifyAccessPaths(OptimizerImpl.java:2456)
      at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(SelectNode.java:1865)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(DMLStatementNode.java:307)
      at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(CursorNode.java:515)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:367)
      at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConne
      ctionContext.java:802)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
      ... 9 more

      I think the directive is misplaced and should come after the x1 (c, d) but it shouldn't throw an NPE

      1. querynperepro.sql
        1 kB
        Kathey Marsden
      2. modified_querynperepro.sql
        1 kB
        Dimuthu Wickramanayake
      3. commentedQueryRepro.sql
        3 kB
        Bryan Pendleton
      4. commentedQueryRepro.out
        6 kB
        Bryan Pendleton
      5. AttributeHolder.patch
        0.7 kB
        Dimuthu Wickramanayake

        Activity

        Hide
        DimuthuW Dimuthu Wickramanayake added a comment -

        So this should have the error code ERROR XCY04:Invalid syntax for optimizer overrides. The syntax should be – DERBY-PROPERTIES

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - So this should have the error code ERROR XCY04:Invalid syntax for optimizer overrides. The syntax should be – DERBY-PROPERTIES
        Hide
        DimuthuW Dimuthu Wickramanayake added a comment -

        Okay i will look at them. Thank you very much bryan for spending your
        valuable time to analyse this

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - Okay i will look at them. Thank you very much bryan for spending your valuable time to analyse this
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        Hi Dimuthu,

        I spent some time looking at this issue, and perhaps I can try to explain it better.

        Firstly, it's important to have a look at the documentation for --DERBY-PROPERTIES. You can find that documentation here: https://db.apache.org/derby/docs/10.13/tuning/ctunoptimzoverride.html
        and also here: https://db.apache.org/derby/docs/10.13/tuning/ctundepthoptover.html

        A strange thing about --DERBY-PROPERTIES is that, syntactically, these properties are *comments* to the SQL parser, which is why the documentation notes that "Important: Make sure that you adhere to the correct syntax when using the --DERBY-PROPERTIES clause. Failure to do so can cause the parser to interpret it as a comment and ignore it."

        The particular aspect of --DERBY-PROPERTIES that we are concerned with here are table optimizer override properties, which must be included at the end of a tableExpression.

        The query from the original repro script is:

        select count(*) from (select * from t1 union select * from t3 --DERBY-PROPERTIES joinStrategy=HASH
        ) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = x2.e ;
        

        This query is *not* a syntax error. It is valid syntax. The table optimizer property "joinStrategy=HASH" is specified as applying to the table expression "t3", which is a valid table expression.

        *However*, t3 is not involved in a join.

        Rather, t3 is involved in a *union*: "select * from t1 union select * from t3".

        But joinStrategy is not appropriate for a union, only for a join.

        So, ideally, trying to run the query from the original repro should get an error message from the query compiler, saying something like:

        ERROR nnnnn: Invalid join strategy 'HASH' specified in Properties list on table 't3'. The table 't3' is included in a table UNION expression, not a join, and therefore may not have a join strategy applied to it.

        Alternatively, it would be acceptable, if not so friendly, if the incorrect join strategy specification were simply ignored.

        But we do *not* want the query to crash during optimization. That is a bug.

        I have attached updated files 'commentedQueryRepro.sql' and 'commentedQueryRepro.out' which show several different types of join strategy, both syntactically valid and syntactically invalid, together with their results.

        Can you have a look at my attached files, and at the documentation pages that I noted, and see if the problematic behavior, and the desired behavior, makes more sense now?

        Show
        bryanpendleton Bryan Pendleton added a comment - Hi Dimuthu, I spent some time looking at this issue, and perhaps I can try to explain it better. Firstly, it's important to have a look at the documentation for --DERBY-PROPERTIES. You can find that documentation here: https://db.apache.org/derby/docs/10.13/tuning/ctunoptimzoverride.html and also here: https://db.apache.org/derby/docs/10.13/tuning/ctundepthoptover.html A strange thing about --DERBY-PROPERTIES is that, syntactically, these properties are * comments * to the SQL parser, which is why the documentation notes that "Important: Make sure that you adhere to the correct syntax when using the --DERBY-PROPERTIES clause. Failure to do so can cause the parser to interpret it as a comment and ignore it." The particular aspect of --DERBY-PROPERTIES that we are concerned with here are table optimizer override properties, which must be included at the end of a tableExpression. The query from the original repro script is: select count(*) from (select * from t1 union select * from t3 --DERBY-PROPERTIES joinStrategy=HASH ) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = x2.e ; This query is * not * a syntax error. It is valid syntax. The table optimizer property "joinStrategy=HASH" is specified as applying to the table expression "t3", which is a valid table expression. * However *, t3 is not involved in a join. Rather, t3 is involved in a * union *: "select * from t1 union select * from t3". But joinStrategy is not appropriate for a union, only for a join. So, ideally, trying to run the query from the original repro should get an error message from the query compiler, saying something like: ERROR nnnnn: Invalid join strategy 'HASH' specified in Properties list on table 't3'. The table 't3' is included in a table UNION expression, not a join, and therefore may not have a join strategy applied to it. Alternatively, it would be acceptable, if not so friendly, if the incorrect join strategy specification were simply ignored. But we do * not * want the query to crash during optimization. That is a bug. I have attached updated files 'commentedQueryRepro.sql' and 'commentedQueryRepro.out' which show several different types of join strategy, both syntactically valid and syntactically invalid, together with their results. Can you have a look at my attached files, and at the documentation pages that I noted, and see if the problematic behavior, and the desired behavior, makes more sense now?
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        Thanks for the updated information. I will try to experiment with this myself, but probably not until this weekend.

        Show
        bryanpendleton Bryan Pendleton added a comment - Thanks for the updated information. I will try to experiment with this myself, but probably not until this weekend.
        Hide
        DimuthuW Dimuthu Wickramanayake added a comment -

        I uploaded the code without the NPE. There i haven't corrected any misplacing words i have just write the whole command in one line. So what i feel is this is not an issue.

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - I uploaded the code without the NPE. There i haven't corrected any misplacing words i have just write the whole command in one line. So what i feel is this is not an issue.
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        Thank you for investigating this issue! But I'm not sure I understand what you have discovered.

        Are you saying that, with current Derby code, you get a syntax error rather than a null pointer exception in the optimizer?

        Or are you saying that with the original reproduction script, you see a null pointer exception, but with a modified form of the script, you see no error at all?

        Perhaps you could attach a text file showing the results of your studies, including (a) the current behavior of the original reproduction script in your environment, and (b) the alternate behavior that you are seeing with your modified syntax.

        Show
        bryanpendleton Bryan Pendleton added a comment - Thank you for investigating this issue! But I'm not sure I understand what you have discovered. Are you saying that, with current Derby code, you get a syntax error rather than a null pointer exception in the optimizer? Or are you saying that with the original reproduction script, you see a null pointer exception, but with a modified form of the script, you see no error at all? Perhaps you could attach a text file showing the results of your studies, including (a) the current behavior of the original reproduction script in your environment, and (b) the alternate behavior that you are seeing with your modified syntax.
        Hide
        DimuthuW Dimuthu Wickramanayake added a comment - - edited

        Actually the statement "I think the directive is misplaced and should come after the x1 (c, d) but it shouldn't throw an NPE" is wrong. The reason for the null pointer exception as i found is the statement is in 2 rows. So when i gave that statement in one line there was no error. So this is a syntax error not a null pointer exception. Keeping the directive in the place does no harm.

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - - edited Actually the statement "I think the directive is misplaced and should come after the x1 (c, d) but it shouldn't throw an NPE" is wrong. The reason for the null pointer exception as i found is the statement is in 2 rows. So when i gave that statement in one line there was no error. So this is a syntax error not a null pointer exception. Keeping the directive in the place does no harm.
        Hide
        DimuthuW Dimuthu Wickramanayake added a comment -

        Thank you very much Bryan.

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - Thank you very much Bryan.
        Hide
        bryanpendleton Bryan Pendleton added a comment -

        Welcome to Derby! I've marked you as assigned to DERBY-4041.

        Show
        bryanpendleton Bryan Pendleton added a comment - Welcome to Derby! I've marked you as assigned to DERBY-4041 .
        Hide
        DimuthuW Dimuthu Wickramanayake added a comment -

        i LIKE TO WORK ON THIS ISSUE

        Show
        DimuthuW Dimuthu Wickramanayake added a comment - i LIKE TO WORK ON THIS ISSUE
        Hide
        kmarsden Kathey Marsden added a comment -

        attaching script to reproduce the problem querynperepro.sql

        Show
        kmarsden Kathey Marsden added a comment - attaching script to reproduce the problem querynperepro.sql

          People

          • Assignee:
            DimuthuW Dimuthu Wickramanayake
            Reporter:
            kmarsden Kathey Marsden
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:

              Development