db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3355) Alter Column ... NULL ignores double quotes around column name
Date Wed, 30 Jan 2008 17:22:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3355?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564102#action_12564102
] 

A B commented on DERBY-3355:
----------------------------

Hi Bryan,

I reviewed at the patch and it looks good to me.  The new test cases fail without the change
and pass with it, as expected.

Two very minor things that occurred to me while reading the patch:

  1. Might be nice (though not by any means necessary) to include a comment in AlterTableConstantAction
      that explicitly mentions the motivation for double-quoting the column name.  On the
other hand, maybe
      the presence of the double quotes is itself enough to make that clear?  Not a big deal
either way.

  2. I instinctively found myself wondering if column names which had double quotes in them
would work
      correctly.  I ran some tests and the answer is Yes, they do work correctly.  Do you
think it might be useful
      to add a few test cases for that, just for completeness?  The quick tests that I ran
were simply:

      ij> create table t1 ("""c""2" int, """""C3" int);
      0 rows inserted/updated/deleted
      ij> select * from t1;
      "c"2       |""C3
      -----------------------

      0 rows selected
      ij> alter table t1 alter column """c""2" not null;
      0 rows inserted/updated/deleted
      ij> insert into t1 values (null, 2);
      ERROR 23502: Column '"c"2'  cannot accept a NULL value.
      ij> alter table t1 alter column """""C3" not null;
      0 rows inserted/updated/deleted
      ij> insert into t1 values (1, null);
      ERROR 23502: Column '""C3'  cannot accept a NULL value.
      ij> alter table t1 alter column """""c3" not null;
      ERROR 42X14: '""c3' is not a column in table or VTI 'T1'.
      ij>   alter table t1 alter column """c3" not null;
      ERROR 42X14: '"c3' is not a column in table or VTI 'T1'.
      ij> alter table t1 alter column "c2" not null;
      ERROR 42X14: 'c2' is not a column in table or VTI 'T1'.
      ij> alter table t1 alter column "C3" not null;
      ERROR 42X14: 'C3' is not a column in table or VTI 'T1'.

Neither of these nits should block the patch, though.  As the regression tests ran cleanly
and I can see no other problems with the changes, I'm +1 to commit.

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


Mime
View raw message