You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2008/02/02 16:50:49 UTC
svn commit: r617818 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bpendleton
Date: Sat Feb 2 07:50:48 2008
New Revision: 617818
URL: http://svn.apache.org/viewvc?rev=617818&view=rev
Log:
DERBY-3355: Alter Column Not Null ignores double quotes around column name
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 was not properly enclosing
the column name in double quotes, so the compiler converts the column
name to upper case, and gets a no-such-column error.
The fix is to use IdUtil.normalToDelimited to delimit the column name
in the internally-generated SQL.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=617818&r1=617817&r2=617818&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Sat Feb 2 07:50:48 2008
@@ -78,6 +78,7 @@
import org.apache.derby.iapi.types.DataValueDescriptor;
import org.apache.derby.iapi.types.RowLocation;
import org.apache.derby.iapi.types.StringDataValue;
+import org.apache.derby.iapi.util.IdUtil;
import org.apache.derby.impl.sql.catalog.DDColumnDependableFinder;
import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
@@ -2428,7 +2429,12 @@
// already found a nullable column so add "AND"
if (foundNullable)
constraintText.append(" AND ");
- constraintText.append(columnNames[colCtr] + " IS NOT NULL ");
+ // Delimiting the column name is important in case the
+ // column name uses lower case characters, spaces, or
+ // other unusual characters.
+ constraintText.append(
+ IdUtil.normalToDelimited(columnNames[colCtr]) +
+ " IS NOT NULL ");
}
foundNullable = true;
nullCols[colCtr] = true;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=617818&r1=617817&r2=617818&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Sat Feb 2 07:50:48 2008
@@ -1893,4 +1893,55 @@
hi
my val
another
+ij> -- DERBY-3355: Exercise ALTER TABLE ... NOT NULL with table and column
+-- names which are in mixed case. This is important because
+-- AlterTableConstantAction.validateNotNullConstraint generates and
+-- executes some SQL on-the-fly, and it's important that it properly
+-- delimits the table and column names in that SQL. We also include a few
+-- other "unusual" table and column names.
+create table d3355 ( c1 varchar(10), "c2" varchar(10), c3 varchar(10));
+0 rows inserted/updated/deleted
+ij> create table "d3355_a" ( c1 varchar(10), "c2" varchar(10), c3 varchar(10));
+0 rows inserted/updated/deleted
+ij> create table d3355_qt_col ("""c""4" int, """""C5" int, "c 6" int);
+0 rows inserted/updated/deleted
+ij> create table "d3355_qt_""tab" ( c4 int, c5 int, c6 int);
+0 rows inserted/updated/deleted
+ij> insert into d3355 values ('a', 'b', 'c');
+1 row inserted/updated/deleted
+ij> insert into "d3355_a" values ('d', 'e', 'f');
+1 row inserted/updated/deleted
+ij> insert into d3355_qt_col values (4, 5, 6);
+1 row inserted/updated/deleted
+ij> insert into "d3355_qt_""tab" values (4, 5, 6);
+1 row inserted/updated/deleted
+ij> -- All of these ALTER TABLE statements should succeed.
+alter table d3355 alter column c1 not null;
+0 rows inserted/updated/deleted
+ij> alter table d3355 alter column "c2" not null;
+0 rows inserted/updated/deleted
+ij> alter table d3355 alter column "C3" not null;
+0 rows inserted/updated/deleted
+ij> alter table "d3355_a" alter column c1 not null;
+0 rows inserted/updated/deleted
+ij> alter table "d3355_a" alter column "c2" not null;
+0 rows inserted/updated/deleted
+ij> alter table "d3355_a" alter column "C3" not null;
+0 rows inserted/updated/deleted
+ij> alter table d3355_qt_col alter column """""C5" not null;
+0 rows inserted/updated/deleted
+ij> alter table d3355_qt_col alter column "c 6" not null;
+0 rows inserted/updated/deleted
+ij> alter table "d3355_qt_""tab" alter column c5 not null;
+0 rows inserted/updated/deleted
+ij> -- These ALTER TABLE statements should fail, with no-such-column and/or
+-- no-such-table errors:
+alter table d3355 alter column "c1" not null;
+ERROR 42X14: 'c1' is not a column in table or VTI 'D3355'.
+ij> alter table d3355 alter column c2 not null;
+ERROR 42X14: 'C2' is not a column in table or VTI 'D3355'.
+ij> alter table d3355_a alter column c1 not null;
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'D3355_A' because it does not exist.
+ij> alter table "d3355_a" alter column "c1" not null;
+ERROR 42X14: 'c1' is not a column in table or VTI 'd3355_a'.
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?rev=617818&r1=617817&r2=617818&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Sat Feb 2 07:50:48 2008
@@ -1103,3 +1103,36 @@
describe t2371;
insert into t2371 (a) values (default);
select * from t2371;
+
+-- DERBY-3355: Exercise ALTER TABLE ... NOT NULL with table and column
+-- names which are in mixed case. This is important because
+-- AlterTableConstantAction.validateNotNullConstraint generates and
+-- executes some SQL on-the-fly, and it's important that it properly
+-- delimits the table and column names in that SQL. We also include a few
+-- other "unusual" table and column names.
+
+create table d3355 ( c1 varchar(10), "c2" varchar(10), c3 varchar(10));
+create table "d3355_a" ( c1 varchar(10), "c2" varchar(10), c3 varchar(10));
+create table d3355_qt_col ("""c""4" int, """""C5" int, "c 6" int);
+create table "d3355_qt_""tab" ( c4 int, c5 int, c6 int);
+insert into d3355 values ('a', 'b', 'c');
+insert into "d3355_a" values ('d', 'e', 'f');
+insert into d3355_qt_col values (4, 5, 6);
+insert into "d3355_qt_""tab" values (4, 5, 6);
+-- All of these ALTER TABLE statements should succeed.
+alter table d3355 alter column c1 not null;
+alter table d3355 alter column "c2" not null;
+alter table d3355 alter column "C3" not null;
+alter table "d3355_a" alter column c1 not null;
+alter table "d3355_a" alter column "c2" not null;
+alter table "d3355_a" alter column "C3" not null;
+alter table d3355_qt_col alter column """""C5" not null;
+alter table d3355_qt_col alter column "c 6" not null;
+alter table "d3355_qt_""tab" alter column c5 not null;
+-- These ALTER TABLE statements should fail, with no-such-column and/or
+-- no-such-table errors:
+alter table d3355 alter column "c1" not null;
+alter table d3355 alter column c2 not null;
+alter table d3355_a alter column c1 not null;
+alter table "d3355_a" alter column "c1" not null;
+