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