Issue Details (XML | Word | Printable)

Key: CORE-1089
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Nando Dessena
Votes: 2
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Wrong ordering with views, distinct, left join and order by

Created: 16/Jan/07 02:08 PM   Updated: 15/Mar/12 09:57 AM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1, 2.0.5, 2.1.2, 2.5 Beta 1, 2.5 Beta 2, 2.1.3, 3.0 Initial, 2.5 RC1, 2.5 RC2
Fix Version/s: 2.0.6, 2.5 RC3, 2.1.4, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Duplicate
 
Relate


 Description  « Hide
Selecting from a view that uses distinct and left join returns records in the wrong order if the order by clause doesn't include columns from the right (non-mandatory) table. Test case:

CREATE TABLE FAT
   (
     IDXXFAT VARCHAR(26) NOT NULL,
     PROGFAT INTEGER,
     IDXXCCB VARCHAR(20),
     NDONFAT INTEGER,
   CONSTRAINT PK$_FAT PRIMARY KEY (IDXXFAT)
   );

COMMIT;

   INSERT INTO FAT (IDXXFAT,PROGFAT,IDXXCCB,NDONFAT) values
('2007.1',1,'Y',1002);
   INSERT INTO FAT (IDXXFAT,PROGFAT,IDXXCCB,NDONFAT) values
('2007.2',2,'X',1001);

COMMIT;

   CREATE TABLE SCA
   (
     IDXXSCA VARCHAR(16) NOT NULL,
     PROGSCA INTEGER,
     IDXXFAT VARCHAR(26),
   CONSTRAINT PK$_SCA PRIMARY KEY (IDXXSCA)
   );

COMMIT;

   INSERT INTO SCA (IDXXSCA,PROGSCA,IDXXFAT) values ('2007.4',4,'2007.1');
   INSERT INTO SCA (IDXXSCA,PROGSCA,IDXXFAT) values ('2007.3',3,'2007.1');
   INSERT INTO SCA (IDXXSCA,PROGSCA,IDXXFAT) values ('2007.2',2,'2007.2');
   INSERT INTO SCA (IDXXSCA,PROGSCA,IDXXFAT) values ('2007.1',1,'2007.2');

COMMIT;

   CREATE VIEW VW$_SCA (
     IDXXSCA,
     PROGSCA,
     IDXXFAT,
     IDXXCCB,
     NDONFAT
   ) AS
   SELECT DISTINCT
   SCA.IDXXSCA,
   SCA.PROGSCA,
   SCA.IDXXFAT,
   FAT.IDXXCCB,
   FAT.NDONFAT
   FROM
   SCA LEFT JOIN FAT ON SCA.IDXXFAT=FAT.IDXXFAT;

COMMIT;

SQL> select * from vw$_sca order by 2 desc;

IDXXSCA PROGSCA IDXXFAT [truncated]
================ ============ ==============
2007.1 1 2007.2
2007.2 2 2007.2
2007.3 3 2007.1
2007.4 4 2007.1

Wrong order.

SQL> select * from vw$_sca order by progsca desc, idxxccb desc;

IDXXSCA PROGSCA IDXXFAT IDXXCCB [truncated]
================ ============ =========== =======
2007.4 4 2007.1 Y
2007.3 3 2007.1 Y
2007.2 2 2007.2 X
2007.1 1 2007.2 X

Correct order.


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Philippe Makowski added a comment - 21/Mar/11 04:45 PM
QA test made