You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Radu Radutiu (JIRA)" <de...@db.apache.org> on 2006/10/17 13:04:34 UTC
[jira] Created: (DERBY-1967) UNION (ALL) contraint violation
problem
UNION (ALL) contraint violation problem
---------------------------------------
Key: DERBY-1967
URL: http://issues.apache.org/jira/browse/DERBY-1967
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.6, 10.1.3.1
Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
and jdk 1.6.0-rc-b99.
Reporter: Radu Radutiu
The following simple test case gives an error:
create table a (f1 varchar(10));
create table b (f2 varchar(10));
insert into b values('test');
select nullif('x','x') as f0, f1 from a
union all
select nullif('x','x') as f0, nullif('x','x') as f1 from b;
ERROR 23502: Column 'F0' cannot accept a NULL value.
SQLState(23502) vendor code(30000)
However the following works ok:
drop table a;
drop table b;
create table a (f1 int);
create table b (f2 int);
insert into b values(1);
select nullif('x','x') as f0, f1 from a
union all
select nullif('x','x') as f0, nullif(1,1) as f1 from b;
The test case is a simplification of a query generated by Hibernate
with the table per class inheritance strategy. Both queries work ok on
MSSQL and PostgreSQL. On Derby only the second query works, the first
one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng updated DERBY-1967:
--------------------------
Attachment: derby1967-trunk-stat01.txt
derby1967-trunk-diff01.txt
Attaching patch derby1967-trunk-diff01.txt for review. derbyall still running. Will post result when it completes.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Bryan Pendleton updated DERBY-1967:
-----------------------------------
Fix Version/s: 10.3.0.0
Derby Info: [Regression] (was: [Patch Available, Regression])
Thanks Yip for the patch and thanks Army for the review. The patch looks good to me, too, and
my build and tests were successful, so I committed it to the trunk as subversion
revision 465122.
Should this patch be merged back to previous branch(es)?
I cleared patch available since the patch is now committed to the trunk, but didn't resolve
the issue in case we want to merge it to a release branch first.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng reassigned DERBY-1967:
-----------------------------
Assignee: Yip Ng
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng updated DERBY-1967:
--------------------------
Derby Info: [Patch Available, Regression] (was: [Regression])
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1967-10.2-diff01.txt, derby1967-10.2-stat01.txt, derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12442940 ]
A B commented on DERBY-1967:
----------------------------
I did some investigating around this and it appears that this query works in 10.1.2.1 but fails in 10.1.3 and later. I was eventually able to track it down to the changes for DERBY-7. Before those changes were committed the query ran without error; after that commit this query now fails. For what that's worth...
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12443195 ]
Yip Ng commented on DERBY-1967:
-------------------------------
Thanks Army and Bryan for reviewing the patch. I also have backported this to 10.2 line and ran derbyall without any problems.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12442949 ]
Yip Ng commented on DERBY-1967:
-------------------------------
Thanks Army, I just found the problem and running my patch against derbyall currently. Some explanation of the problem:
In the bind phase of ConditionalNode (NULLIF), the CAST node is generated on top of the untyped NULL and it gets the data type descriptor(DTD) of the left operand. However, the CAST node should have DTD where its value can be nullable.
BinaryComparisonOperatorNode bcon = (BinaryComparisonOperatorNode)testCondition;
QueryTreeNode cast = getNodeFactory().getNode(
C_NodeTypes.CAST_NODE,
thenElseList.elementAt(0),
bcon.getLeftOperand().getTypeServices(), <=== not nullable!
getContextManager());
The second query:
select nullif('x','x') as f0, f1 from a
union all
select nullif('x','x') as f0, nullif(1,1) as f1 from b;
works because it didn't generate a NormalizedResultSet on top of the PRN on the right hand side of the union since the datatype and length matches. So it didn't hit the path where it does additional checking at execution time.
For the first query:
select nullif('x','x') as f0, f1 from a
union all
select nullif('x','x') as f0, nullif('x','x') as f1 from b;
The union result column's length does not match with the right hand side result column, so it generated a NormalizedResultSet on top of the RHS of the union. When the system retrieves the row from NormalizedResultSet at execution time, the normalize method is called on the DTD and checks if the source is NULL and whether its DTD is not nullable. In this case, the SQLSTATE 23502 is thrown.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12443044 ]
Yip Ng commented on DERBY-1967:
-------------------------------
derbyall passes.
>From what Army described in previous comment, this looks like a regression.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng updated DERBY-1967:
--------------------------
Derby Info: [Patch Available]
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12443116 ]
A B commented on DERBY-1967:
----------------------------
Thanks for the quick turn-around on this, Yip.
Your description of the problem sounds correct to me and the changes themselves match what you say. They also line up with DERBY-7 as the code in question was added as part of that issue.
I confirmed that the patch applies cleanly to trunk and that the new test case fails without your changes and passes with it. The code comments also indicate why the change was necessary, which is great.
It's a pretty small change, it makes sense to me, and derbayll passed. So I vote +1 to commit...
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng updated DERBY-1967:
--------------------------
Attachment: derby1967-10.2-stat01.txt
derby1967-10.2-diff01.txt
Attaching derby1967-10.2-diff01.txt for 10.2 codeline. derbyall pass.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Fix For: 10.3.0.0
>
> Attachments: derby1967-10.2-diff01.txt, derby1967-10.2-stat01.txt, derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Bryan Pendleton resolved DERBY-1967.
------------------------------------
Fix Version/s: 10.2.1.8
Resolution: Fixed
Derby Info: [Regression] (was: [Patch Available, Regression])
Thanks Yip! I committed the merged fix to the 10.2 branch as revision 465256.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Fix For: 10.2.1.8, 10.3.0.0
>
> Attachments: derby1967-10.2-diff01.txt, derby1967-10.2-stat01.txt, derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1967) UNION (ALL) contraint violation
problem
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1967?page=all ]
Yip Ng updated DERBY-1967:
--------------------------
Derby Info: [Patch Available, Regression] (was: [Patch Available])
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> Environment: derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
> and jdk 1.6.0-rc-b99.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
> Attachments: derby1967-trunk-diff01.txt, derby1967-trunk-stat01.txt
>
>
> The following simple test case gives an error:
> create table a (f1 varchar(10));
> create table b (f2 varchar(10));
> insert into b values('test');
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif('x','x') as f1 from b;
> ERROR 23502: Column 'F0' cannot accept a NULL value.
> SQLState(23502) vendor code(30000)
> However the following works ok:
> drop table a;
> drop table b;
> create table a (f1 int);
> create table b (f2 int);
> insert into b values(1);
> select nullif('x','x') as f0, f1 from a
> union all
> select nullif('x','x') as f0, nullif(1,1) as f1 from b;
> The test case is a simplification of a query generated by Hibernate
> with the table per class inheritance strategy. Both queries work ok on
> MSSQL and PostgreSQL. On Derby only the second query works, the first
> one giving a contraint violation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira