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

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.6.2.4, 10.7.1.1
    • 10.8.3.0, 10.9.1.0
    • SQL
    • Urgent
    • High Value Fix, Repro attached
    • 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. patch1.txt
          12 kB
          Bryan Pendleton
        2. patch2.txt
          13 kB
          Bryan Pendleton
        3. query.log
          190 kB
          Dag H. Wanvik
        4. tests.out
          9 kB
          Bryan Pendleton
        5. tests.sql
          4 kB
          Bryan Pendleton
        6. try1.txt
          3 kB
          Bryan Pendleton

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment