Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
  b=K037Sv74rgOQ2xAUB25FQ89OJEvsp6pKHuT3unQ+AnwtBF2jLPS44wM6HSxC/liMdKMmtfVvAvVWnX/EDaSYcPBA7geNOC6wnlrceTZj4Vo+eicM4BkOh+4FQSxKvbyT2MV3Ic7ADonAY95KzFwA2f77mzHOyjJrWJRNICcfyqY=;
Date: Sun, 27 Jan 2008 13:05:00 -0800 (PST)
From: Geoff hendrey <geoff_hendrey@yahoo.com>
Subject: ALTER COLUMN ..NOT NULL  double quotes bug?
To: derby-user@db.apache.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-332762929-1201467900=:50069"
Message-ID: <92279.50069.qm@web31809.mail.mud.yahoo.com>

--0-332762929-1201467900=:50069
Content-Type: text/plain; charset=us-ascii

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

--0-332762929-1201467900=:50069
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt">Hi:<br>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. <br><br>CREATE TABLE Table2<br>(<br>&nbsp;&nbsp; "c" VARCHAR(32672)<br>);<br><br>alter table Table2 ALTER COLUMN "c" NULL;<br>alter table Table2 ALTER COLUMN "c" NOT NULL;<br>INSERT INTO Table2("c") VALUES('yo');<br>alter table Table2 ALTER COLUMN "c" NULL;<br>alter table Table2 ALTER COLUMN "c" NOT NULL;<br><br><br>Query 1 of 6 elapsed
 time (seconds) - Total: 0.012, SQL query: 0.012, Building output: 0<br><br>Query 2 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, Building output: 0<br><br>Query 3 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, Building output: 0<br>1 Row(s) Inserted<br>Query 4 of 6 elapsed time (seconds) - Total: 0.009, SQL query: 0.009, Building output: 0<br><br>Query 5 of 6 elapsed time (seconds) - Total: 0.003, SQL query: 0.003, Building output: 0<br>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&nbsp; statement then 'C' is not a column in the target table., SQL State: 42X04, Error Code: -1<br></div></body></html>
--0-332762929-1201467900=:50069--