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 23:32:59 GMT

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

Dag H. Wanvik commented on DERBY-4413:

Dag said above: "I think it would better (and more
correct if I read the standard correctly) to compute those values when
the row is being inserted (as is currently done for the (general)
generated columns ("late")."

I checked the standard again on this point, and the upshot is that
defaults and default generated valued for columns not supplied should
be constructed when the row is inserted:

Section 14.11 (SQL 2008, part 2), General rules 5,6 and 7:

5) QT is effectively evaluated before insertion of any rows into T.
6) Let Q be the result of evaluating QT.
7) For each row R of Q:

   a) A candidate row of T is effectively created in which the value
      of each column is its default value, as specified in the General
      Rules of Subclause 11.5, "default clause>". The
      candidate row consists of every column of T.

   c) For each object column in the candidate row, let Ci be the
      object column identified by the i-th <column name> in the
      <insert column list> and let SVi be the i-th value of R.

   d) For every Ci for which one of the following conditions is true:

      i) Ci is not marked as unassigned and no underlying column of Ci
      is a self-referencing column.

      the General Rules of Subclause 9.2, "Store assignment", are applied
      with Ci and SVi as TARGET and SOURCE, respectively. Ci is no longer
      marked as unassigned.

My takeaway from this is that the defaults should be constructed as
each row is picked form the fully evaluated result set (QA). With SQL
2008, that result set may be sorted before insertion, e.g. as

  CREATE TABLE t (i int generated always as identity, j int)
  INSERT INTO t (j) SELECT j from sometab ORDER BY j

With the present implementation, the auto-increment happens "early"
(before the sort) and would not be monotonically increasing in the
result set to be inserted, cf. Bryan's issue in DERBY-4.  For general
generated columns, it happens at the right time ("late").  So, for
"DERBY-4397 Allow ORDER BY in subqueries", we would need to move the
auto-increment to "late" phase also to make it correct (not just a
performance issue).

> 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:,,
>            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.

View raw message