db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1967) UNION (ALL) contraint violation problem
Date Tue, 17 Oct 2006 15:59:36 GMT
    [ 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

        

Mime
View raw message