db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
Date Sat, 13 Feb 2010 17:40:28 GMT

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

Bryan Pendleton commented on DERBY-1773:
----------------------------------------

I've been investigating this issue a bit, and want to note 
what I've found, as well as several ideas.

Firstly, the core of the issue occurs in EmbedResultSet.updateRow, which is
attempting to construct and execute, on the fly, a statement like:

  update t set x = 2 where current of my-cursor-name

The problem arises due to the fact that the select statement:

  select * from t as a(a1)

has introduced aliases for both the table (T => A) and the column (X => A1)

Now, when EmbedResultSet.updateRow processes the table, it is careful
to use the true underlying base table name (T):

               updateWhereCurrentOfSQL.append(getFullBaseTableName(targetTable));//got the
underlying (schema.)table name

The problem arises a bit later, when it tries to access the columns which
are being updated:

            for (int i=1; i<=resultDescription.getColumnCount(); i++) { //in this for loop
we are constructing columnname=?,... part of the update sql
                if (columnGotUpdated[i-1]) { //if the column got updated, do following
                    if (foundOneColumnAlready)
                        updateWhereCurrentOfSQL.append(",");
                    //using quotes around the column name to preserve case sensitivity
                    updateWhereCurrentOfSQL.append(IdUtil.normalToDelimited(
                            resultDescription.getColumnDescriptor(i).getName()) + "=?");
                    foundOneColumnAlready = true;
                }
            }

The issue here is that resultDescription.getColumnDescriptor(i) is a
GenericColumnDescriptor, which was constructed from a ResultColumnDescriptor
by the method ResultColumnList.makeResultDescriptors(), and this code doesn't
have enough "smarts" to understand the difference between the column's "exposedName" (A1)
and its true base column name (X).

So, I can see two possibilities:
1) Enhance GenericColumnDescriptor, ResultColumnDescriptor, and (probably) ResultColumn,
so that we have a new method GenericColumnDescriptor.getBaseColumnName(), and call
that new method from EmbedResultSet.updateRow.
2) Refuse the statement. Change (probably) SelectNode.isUpdatableCursor() so that it
looks through its ResultColumnList to see if any of the result columns have been aliased,
and state that a SELECT with aliased column names is NOT a legal updatable SELECT statement.

I'll investigate both possibilities further, just wanted to note my results so far.

> insertRow() and updateRow() fail with syntax error when column has an alias
> ---------------------------------------------------------------------------
>
>                 Key: DERBY-1773
>                 URL: https://issues.apache.org/jira/browse/DERBY-1773
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.2.1.6
>            Reporter: Knut Anders Hatlen
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: Alias.java
>
>
> When the select query used in an updatable result set has column aliases, a syntax error
is thrown when executing ResultSet.insertRow() and ResultSet.updateRow(). The problem is seen
on embedded and client. Repro is attached.
> Exception in thread "main" ERROR 42X14: 'A1' is not a column in table or VTI 'APP.T'.
>         at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:316)
>         at org.apache.derby.impl.sql.compile.ResultColumn.bindResultColumnByName(ResultColumn.java:677)
>         at org.apache.derby.impl.sql.compile.ResultColumnList.bindResultColumnsByName(ResultColumnList.java:682)
>         at org.apache.derby.impl.sql.compile.ResultSetNode.bindResultColumns(ResultSetNode.java:683)
>         at org.apache.derby.impl.sql.compile.SelectNode.bindResultColumns(SelectNode.java:742)
>         at org.apache.derby.impl.sql.compile.UpdateNode.bind(UpdateNode.java:349)
>         at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:345)
>         at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:111)
>         at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:723)
>         at org.apache.derby.impl.jdbc.EmbedResultSet.updateRow(EmbedResultSet.java:3734)
>         at Alias.main(Alias.java:15)

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