[
https://issues.apache.org/jira/browse/DERBY-3355?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564119#action_12564119
]
A B commented on DERBY-3355:
----------------------------
> I'm not sure those quote examples prove anything, not a single one is
> executed after the table has rows in it
Good catch! Sorry for missing that. Building on the example I mentioned above:
ij> insert into t1 values (1, 2);
1 row inserted/updated/deleted
ij> alter table t1 alter column """""C3" null;
0 rows inserted/updated/deleted
ij> alter table t1 alter column """""C3" not null;
ERROR 42X01: Syntax error: Encountered "\"" at line 1, column 43.
So it looks like you're right, the quotes will lead to a syntax error. Though
the specific error in this case seems a tad odd...where'd the slash (\) come
from?
> 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: 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.
