[ https://issues.apache.org/jira/browse/DERBY-3355?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12565080#action_12565080 ]
Bryan Pendleton commented on DERBY-3355: ---------------------------------------- Committed handleQuotes.diff to the trunk as revision 617818. If no additional problems show up over the next few days, I'll investigate merging this fix back to the 10.3 branch. > Alter Column ... NULL ignores double quotes around column name > -------------------------------------------------------------- > > Key: DERBY-3355 > URL: https://issues.apache.org/jira/browse/DERBY-3355 > Project: Derby > Issue Type: Bug > Affects Versions: 10.3.1.4 > Environment: mac 0s x > Reporter: geoff hendrey > Assignee: Bryan Pendleton > Attachments: handleQuotes.diff, patch.diff > > > ' is not a column in the target table., SQL State: 42X04, Error Code: -1 > Hi: > I think I have isolated a bug involving the use of double quotes to define a > column name. Here s the SQL to reproduce the bug, followed by the error > message generated by the final SQL statement. In order to make the bug go > away, eliminate all use of double quotes in the SQL statements below. Note > that the identical alter statement succeeds before the insert, and fail > after. I have spent a long time trying to isolate this problem, so please > take a look. > CREATE TABLE Table2 > ( > "c" VARCHAR(32672) > ); > alter table Table2 ALTER COLUMN "c" NULL; > alter table Table2 ALTER COLUMN "c" NOT NULL; > INSERT INTO Table2("c") VALUES('yo'); > alter table Table2 ALTER COLUMN "c" NULL; > alter table Table2 ALTER COLUMN "c" NOT NULL; > Query 1 of 6 elapsed time (seconds) - Total: 0.012, SQL query: 0.012, > Building output: 0 > Query 2 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, > Building output: 0 > Query 3 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, > Building output: 0 > 1 Row(s) Inserted > Query 4 of 6 elapsed time (seconds) - Total: 0.009, SQL query: 0.009, > Building output: 0 > Query 5 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, > Building output: 0 > Error: java.sql.SQLException: Column 'C' is either not in any table in the > FROM list or appears within a join specification and is outside the scope of > the join specification or appears in a HAVING clause and is not in the GROUP > BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a > column in the target table., SQL State: 42X04, Error Code: -1 > -----Inline Message Follows----- > Geoff hendrey wrote: > > I think I have isolated a bug involving the use of double quotes to > > define a column name. > Hi Geoff, I agree, that is definitely a bug. Your script reproduces > the problem for me, on the current Derby trunk. > It appears that AlterTableConstantAction.validateNotNullConstraint > is internally generating and executing a statement of the form: > select count(*) from tab where not (col is not null) > The code which generates this SQL staement is not properly enclosing > the column name in double quotes, as you noticed, so the compiler > converts the column name to upper case, and gets the no-such-column error. > Can you open a problem report in Jira so that we can track this down > and get it fixed? > http://db.apache.org/derby/DerbyBugGuidelines.html > thanks, > bryan -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.