Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1967

UNION (ALL) contraint violation problem

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.2.2.0, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • 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.
    • Bug behavior facts:
      Regression

      Description

      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.

        Attachments

        1. derby1967-trunk-diff01.txt
          3 kB
          Yip Ng
        2. derby1967-trunk-stat01.txt
          0.3 kB
          Yip Ng
        3. derby1967-10.2-diff01.txt
          3 kB
          Yip Ng
        4. derby1967-10.2-stat01.txt
          0.3 kB
          Yip Ng

          Issue Links

            Activity

              People

              • Assignee:
                yipng Yip Ng
                Reporter:
                rradutiu Radu Radutiu
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: