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] Updated: (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
Date Mon, 15 Feb 2010 18:17:28 GMT

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

Bryan Pendleton updated DERBY-1773:
-----------------------------------

    Attachment: NoUpdatesToAliasedColumnsWithTest.diff

Attached is a patch proposal. I'm still running tests, and would
appreciate suggestions for more tests.

This patch makes two basic changes:
1) It modifies ResultColumnList.markUpdatableByCursor so that
    it only marks a ResultColumn as updatable if it is not aliased,
    that is, if the actual column name of its ColumnReference matches
    the ResultColumn name.
2) It modifies SelectNode.isUpdatableCursor to call the new method
     FromTable.columnsAreUpdatable, which checks the columns to
    see if they have been aliased, in which case the select is NOT updatable.

There are various aspects to this code that I'm not completely clear on.
For one thing, I'm not clear on the differences between the following
two techniques for introducing column aliasing:

      SELECT c1 as a1, c2 as a2 from t1 as abcde

versus

      select * from t1 as a(a1,a2)

They seem to take substantially different paths through the code and
the resulting data structures are different, so the two basic changes that
I made seemed necessary, as the first change handles the first statement,
and the second change handles the second statement.

Secondly, I'm not clear on the overall semantics of "the ResultSet is updatable"
versus "this particular column is updatable". It seems like the FOR UPDATE OF
clause allows for the specification of a particular *subset* of columns which
are updatable, but one can also simply say "FOR UPDATE", or can in fact
omit the clause entirely (this is DERBY-231), in which case the implication is,
I guess, that all columns must be updatable in order for the ResultSet to be updatable?

Or is it more dynamic? Is the intent that, if I say "FOR UPDATE OF C1,C2", then
*only* C1 and C2 are updatable, but if I say just "FOR UPDATE" or omit it entirely,
then so long as at least one column is updatable, the ResultSet should be updatable,
and the computation of updatability must be deferred until the actual attempt to
perform an update (by calling rs.updateXXX) is performed?

As I said, it would be great to have suggestions for more test cases to add!


> 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, NoUpdatesToAliasedColumnsWithTest.diff
>
>
> 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