Uploaded image for project: '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
    • Status: Closed
    • Priority: 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

        Attachments

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: