Derby
  1. Derby
  2. DERBY-5584

Select statement with subqueries with group by and count distinct statements returns wrong number of results

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.2.4, 10.7.1.1
    • Fix Version/s: 10.8.3.0, 10.9.1.0
    • Component/s: SQL
    • Environment:
    • Urgency:
      Urgent
    • Issue & fix info:
      High Value Fix, Repro attached
    • Bug behavior facts:
      Deviation from standard, Regression, Wrong query result

      Description

      Steps to reproduce:

      1. Create database, connect to database with any JDBC client

      2. create two tables:

      CREATE TABLE TEST_5 (
      profile_id INTEGER NOT NULL,
      group_ref INTEGER NOT NULL,
      matched_count INTEGER NOT NULL
      );

      CREATE TABLE TEST_6 (
      profile_id INTEGER NOT NULL,
      group_ref INTEGER NOT NULL,
      matched_count INTEGER NOT NULL
      );

      3. Insert two records for each table:

      insert into test_5 values (1, 10000,1);
      insert into test_5 values (2, 10000, 2);

      insert into test_6 values (1, 10000,1);
      insert into test_6 values (2, 10000, 2);

      4. Run following statement

      SELECT *
      FROM
      (SELECT ps1.group_ref,
      COUNT(DISTINCT ps1.matched_count) AS matched_count
      FROM test_5 ps1
      GROUP BY ps1.group_ref,
      ps1.profile_id
      ) a,
      (SELECT ps2.group_ref,
      COUNT( DISTINCT ps2.matched_count) AS matched_count
      FROM test_6 ps2
      GROUP BY ps2.group_ref,
      ps2.profile_id
      ) b

      As a result I've got 3 records instead of 4 - at least Oracle 10g
      returns 4 records for this statement. Maybe i'm doing something wrong.
      Do you have any suggestions / possible workarounds for this problem

      1. try1.txt
        3 kB
        Bryan Pendleton
      2. tests.sql
        4 kB
        Bryan Pendleton
      3. tests.out
        9 kB
        Bryan Pendleton
      4. query.log
        190 kB
        Dag H. Wanvik
      5. patch2.txt
        13 kB
        Bryan Pendleton
      6. patch1.txt
        12 kB
        Bryan Pendleton

        Issue Links

          Activity

          Piotr Zgadzaj created issue -
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Link This issue relates to DERBY-3002 [ DERBY-3002 ]
          Dag H. Wanvik made changes -
          Attachment query.log [ 12511673 ]
          Bryan Pendleton made changes -
          Assignee Bryan Pendleton [ bryanpendleton ]
          Bryan Pendleton made changes -
          Attachment try1.txt [ 12511841 ]
          Bryan Pendleton made changes -
          Attachment tests.sql [ 12511988 ]
          Attachment tests.out [ 12511989 ]
          Bryan Pendleton made changes -
          Attachment patch1.txt [ 12512074 ]
          Kathey Marsden made changes -
          Labels derby_triage10_9
          Bug behavior facts Deviation from standard [ 10367 ] Deviation from standard,Wrong query result [ 10367,10366 ]
          Urgency Urgent [ 10051 ]
          Issue & fix info High Value Fix,Patch Available,Repro attached [ 10422,10102,10424 ]
          Component/s SQL [ 11408 ]
          Component/s Network Server [ 11410 ]
          Bryan Pendleton made changes -
          Bug behavior facts Wrong query result,Deviation from standard [ 10366,10367 ] Deviation from standard,Regression,Wrong query result [ 10367,10420,10366 ]
          Bryan Pendleton made changes -
          Attachment patch2.txt [ 12515367 ]
          Bryan Pendleton made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Issue & fix info Repro attached,Patch Available,High Value Fix [ 10424,10102,10422 ] High Value Fix,Repro attached [ 10422,10424 ]
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Fix Version/s 10.8.2.3 [ 12318540 ]
          Kathey Marsden made changes -
          Labels derby_triage10_9 derby_backport_reject_10_5 derby_triage10_9
          Affects Version/s 10.6.2.3 [ 12315434 ]
          Kathey Marsden made changes -
          Fix Version/s 10.8.3.0 [ 12323456 ]
          Fix Version/s 10.8.2.3 [ 12318540 ]
          Gavin made changes -
          Workflow jira [ 12650183 ] Default workflow, editable Closed status [ 12802124 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

            • Assignee:
              Bryan Pendleton
              Reporter:
              Piotr Zgadzaj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development