Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Sat, 16 Sep 2017 17:51:00 +0000 (UTC)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12413670.1233360357000.130166.1505584260191@Atlassian.JIRA>
In-Reply-To: <JIRA.12413670.1233360357000@Atlassian.JIRA>
References: <JIRA.12413670.1233360357000@Atlassian.JIRA> <JIRA.12413670.1233360357403@jira-lw-us.apache.org>
Subject: [jira] [Updated] (DERBY-4041) NullPointerException on query with
 misplaced DERBY-PROPERTIES clause
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Sat, 16 Sep 2017 17:51:10 -0000


     [ https://issues.apache.org/jira/browse/DERBY-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-4041:
-----------------------------------
    Attachment: commentedQueryRepro.sql
                commentedQueryRepro.out

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:

{code}
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 ;
{code}

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?


> NullPointerException on query with misplaced DERBY-PROPERTIES clause
> --------------------------------------------------------------------
>
>                 Key: DERBY-4041
>                 URL: https://issues.apache.org/jira/browse/DERBY-4041
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.3.0, 10.4.2.0, 10.5.1.1
>            Reporter: Kathey Marsden
>            Assignee: Dimuthu Wickramanayake
>            Priority: Minor
>              Labels: derby_triage10_8
>         Attachments: commentedQueryRepro.out, commentedQueryRepro.sql, modified_querynperepro.sql, querynperepro.sql
>
>
> 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


--
This message was sent by Atlassian JIRA
(v6.4.14#64029)