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. 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:
              Bryan Pendleton
              Reporter:
              Piotr Zgadzaj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development