[
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.
