db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4413) INSERT from SELECT DISTINCT gives assertFailure (sane), or NPE (insane) in presence of generated columns
Date Fri, 23 Oct 2009 22:35:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4413?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12769492#action_12769492
] 

Dag H. Wanvik commented on DERBY-4413:
--------------------------------------

Looking at the syntax for insert statement in SQL 2008, I conclude that
an ORDER BY can not be attached to the INSERT INTO .. VALUES.

Section 14.11 <insert statement>:
    
    <insert statement> ::=
        INSERT INTO <insertion target> <insert columns and source>
    
    <insert columns and source> ::=
        <from subquery>
      | <from constructor>
      | <from default>
    
    <from constructor> ::=
      [ <left paren> <insert column list> <right paren> ]
      [ <override clause> ]
      <contextually typed table value constructor>
    
    
    <contextually typed table value constructor> ::=
      VALUES <contextually typed row value expression list>

This would not allow and ORDER BY.  However, looking at the <from
subquery> alternative:
    
    <from subquery> ::=
      [ <left paren> <insert column list> <right paren> ]
      [ <override clause> ]
      <query expression>
    
    <query expression> ::=
      [ <with clause> ] <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause>
]

we see that here we could supply an ORDER BY clause if we the VALUES
clause can be derived from <query expression body>, and indeed it can:
    
    <query expression body> ::=
        <query term>
      | <query expression body> UNION [ ALL | DISTINCT ]
          [ <corresponding spec> ] <query term>
      | <query expression body> EXCEPT [ ALL | DISTINCT ]
          [ <corresponding spec> ] <query term>
    
    <query term> ::=
        <query primary>
      | <query term> INTERSECT [ ALL | DISTINCT ]
          [ <corresponding spec> ] <query primary>
    
    <query primary> ::=
        <simple table>
      | <left paren> <query expression body>
          [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause>
] <right paren>
    
    <simple table> ::=
        <query specification>
      | <table value constructor>
      | <explicit table>
    
    <table value constructor> ::=
      VALUES <row value expression list>

So how to resolve this?
Finally, looking closely, I found this (14.11, Syntactic Rule 17):

    17) A <query expression> simply contained in a <from subquery> shall
        not be a <table value constructor>.
    
    NOTE 391 - This rule removes a syntactic ambiguity; otherwise, "VALUES (1)" 
           could be parsed either as
    
    <insert columns and source> ::=
        <from subquery> ::=
        <query expression> ::=
        <table value constructor> ::=
        VALUES (1)
    
    or
    
    <insert columns and source> ::=
        <from constructor> ::=
        <contextually typed table value constructor> ::=
        VALUES (1)

So, in conclusion, the <contextually typed table value constructor>
derivation will prevail, and ORDER BY can not be attached to VALUES here. 


> INSERT from SELECT DISTINCT gives assertFailure (sane), or  NPE (insane) in presence
of generated columns
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4413
>                 URL: https://issues.apache.org/jira/browse/DERBY-4413
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.1, 10.5.2.0, 10.5.3.0
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>         Attachments: derby-4413.diff, derby-4413.stat
>
>
> When a generated column is present in a table, an INSERT DISTINCT fail:
> Repro:
> create table t(i integer, 
>                j integer not null generated always as (i*66));
> insert into t(i) values 1,2;
> insert into t(i) select distinct i from t;
> In an insane build we see this assertFailure:
> ij version 10.5
> ij> connect 'jdbc:derby:wombat2;create=true';
> ij> create table t(i integer, 
>                j integer not null generated always as (i*66));
> 0 rows inserted/updated/deleted
> ij> insert into t(i) values 1,2;
> 2 rows inserted/updated/deleted
> ij> insert into t(i) select distinct i from t;
> ERROR XJ001: Java exception: 'ASSERT FAILED col[1]  is null: org.apache.derby.shared.common.sanity.AssertFailure'.
> java.sql.SQLException: Java exception: 'ASSERT FAILED col[1]  is null: org.apache.derby.shared.common.sanity.AssertFailure'.
> 	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.executeStatement(EmbedStatement.java:1323)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
> 	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:217)
> 	at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
> 	at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
> 	at org.apache.derby.tools.ij.main(ij.java:59)
> 	at org.apache.derby.iapi.tools.run.main(run.java:53)
> Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED col[1]  is null: org.apache.derby.shared.common.sanity.AssertFailure'.
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
> 	... 18 more
> Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED col[1]
 is null
> 	at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
> 	at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
> 	at org.apache.derby.impl.store.access.sort.MergeSort.checkColumnTypes(MergeSort.java:458)
> 	at org.apache.derby.impl.store.access.sort.MergeInserter.insert(MergeInserter.java:98)
> 	at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(SortResultSet.java:317)
> 	at org.apache.derby.impl.sql.execute.SortResultSet.openCore(SortResultSet.java:268)
> 	at org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(NormalizeResultSet.java:139)
> 	at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:415)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
> 	... 11 more

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message