[jira] [Updated] (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
[ https://issues.apache.org/jira/browse/DERBY-1773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bryan Pendleton updated DERBY-1773: --- Attachment: fixedComments.diff Attached 'fixedComments.diff' is essentially identical to the previous patch, but I updated some of the comments to be more accurate, and changed the string text in a SanityManager,Debug statement. So, no functional change, just cleanup. All my regression tests have been clean. > 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 > Labels: derby_triage10_5_2 > Attachments: ASF.LICENSE.NOT.GRANTED--Alias.java, > NoUpdatesToAliasedColumnsWithTest.diff, anyVsAll.diff, fixedComments.diff, > updatedToHeadMarch2010.diff, updatedToHeadMarch2016.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 was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
[ https://issues.apache.org/jira/browse/DERBY-1773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bryan Pendleton updated DERBY-1773: --- Attachment: anyVsAll.diff updatedToHeadMarch2016.diff Uhm, I guess I lost track of this issue, for it's been 6 years! :) The first thing I did was to bring the previous patch up to date, since a lot can change in 6 years. 'updatedToHeadMarch2016.diff' is the result, and it builds and passes tests with the head of trunk. Upon closely re-reading Knut Anders's comments from 6 years ago, I remembered that I had not addressed his observation that it would be possible for some, but not all, of the columns in the result list to be aliased, and only those columns which are aliased should be non-updatable; the unaliased columns should still be updatable. After thinking about that for a while, it seemed to me that this merely required changing the proposed ResultColumnList.columnsAreUpdatable() method so that it tested whether *any* columns in the list were updatable, as opposed to requiring that *all* columns in the list were updatable. So I did that, and added a few additional test cases, and the results appear to be satisfactory. The revised patch is attached as anyVsAll.diff. I'm running tests on it now, to see how it behaves. Sorry for the 6 year gap, but if anyone is still interested in this issue, feedback would be gratefully received. > 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 > Labels: derby_triage10_5_2 > Attachments: ASF.LICENSE.NOT.GRANTED--Alias.java, > NoUpdatesToAliasedColumnsWithTest.diff, anyVsAll.diff, > updatedToHeadMarch2010.diff, updatedToHeadMarch2016.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 was sent by Atlassian JIRA (v6.3.4#6332)
[jira] Updated: (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
[ https://issues.apache.org/jira/browse/DERBY-1773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bryan Pendleton updated DERBY-1773: --- Attachment: updatedToHeadMarch2010.diff Just updated the patch to the latest trunk head to resolve a few merges, and re-attached it. > 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, > updatedToHeadMarch2010.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.
[jira] Updated: (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
[ 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.
[jira] Updated: (DERBY-1773) insertRow() and updateRow() fail with syntax error when column has an alias
[ https://issues.apache.org/jira/browse/DERBY-1773?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Knut Anders Hatlen updated DERBY-1773: -- Issue & fix info: [Repro attached] Urgency: Normal Triaged for 10.5.2. > 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 >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.
