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

SELECT DISTINCT returns duplicates when selecting from subselects

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: 10.1.2.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Latest development sources (SVN revision 232227), Sun JDK 1.5, Solaris/x86

      Description

      When one performs a select distinct on a table generated by a subselect, there sometimes are duplicates in the result. The following example shows the problem:

      ij> CREATE TABLE names (id INT PRIMARY KEY, name VARCHAR(10));
      0 rows inserted/updated/deleted
      ij> INSERT INTO names (id, name) VALUES
      (1, 'Anna'), (2, 'Ben'), (3, 'Carl'),
      (4, 'Carl'), (5, 'Ben'), (6, 'Anna');
      6 rows inserted/updated/deleted
      ij> SELECT DISTINCT(name) FROM (SELECT name, id FROM names) AS n;
      NAME
      ----------
      Anna
      Ben
      Carl
      Carl
      Ben
      Anna

      Six names are returned, although only three names should have been returned.

      When the result is explicitly sorted (using ORDER BY) or the id column is removed from the subselect, the query returns three names as expected:

      ij> SELECT DISTINCT(name) FROM (SELECT name, id FROM names) AS n ORDER BY name;
      NAME
      ----------
      Anna
      Ben
      Carl

      3 rows selected
      ij> SELECT DISTINCT(name) FROM (SELECT name FROM names) AS n;
      NAME
      ----------
      Anna
      Ben
      Carl

      3 rows selected

        Attachments

        1. DERBY-504.diff
          27 kB
          Knut Anders Hatlen
        2. DERBY-504.stat
          0.7 kB
          Knut Anders Hatlen
        3. DERBY-504_b.diff
          65 kB
          Myrna van Lunteren
        4. DERBY-504_b.stat
          0.9 kB
          Myrna van Lunteren
        5. DERBY-504_c.diff
          63 kB
          Knut Anders Hatlen
        6. DERBY-504_c.stat
          0.9 kB
          Knut Anders Hatlen
        7. DERBY-504_c-CRLF.diff
          64 kB
          Knut Anders Hatlen
        8. DERBY-504_c-CRLF.diff
          64 kB
          Knut Anders Hatlen
        9. DERBY-504-cleanup.diff
          2 kB
          Knut Anders Hatlen
        10. DERBY-504-cleanup.stat
          0.1 kB
          Knut Anders Hatlen
        11. DERBY-504-10.1-unix.diff
          64 kB
          Knut Anders Hatlen
        12. DERBY-504-10.1-windows.diff
          65 kB
          Knut Anders Hatlen
        13. DERBY-504-10.1.stat
          1.0 kB
          Knut Anders Hatlen

          Issue Links

            Activity

              People

              • Assignee:
                knutanders Knut Anders Hatlen
                Reporter:
                knutanders Knut Anders Hatlen
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: