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