Issue Details (XML | Word | Printable)

Key: CORE-4925
Type: Bug Bug
Status: Open Open
Priority: Critical Critical
Assignee: Unassigned
Reporter: Oscar Giovani Blanco
Votes: 1
Watchers: 3
Operations

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

Order By is not working when the column is a math calculation with the Rand function

Created: 05/Sep/15 06:35 PM   Updated: 19/Apr/18 01:55 PM
Component/s: Engine
Affects Version/s: 2.5.3 Update 1, 2.5.4
Fix Version/s: None

Environment: Linux 3.2.0-4-686-pae #1 SMP Debian 3.2.57-3 i686

QA Status: No test


 Description  « Hide
Firebird 2.5-super.
The clausule 'order by' is not working when you includes a column with a math calculation with the Rand() function, perhaps also Decode() function. The Query simpliest (because it belongs to another query bigger, that also is not ordering by 'Prbblty' field) is:

select QII.Id_Qstnry,
       QII.Id_Qstn,
       QII.Enabled,
       QII.Seconds,
       QII.Min_Score,
       QII.Probability,
       QII.Seq,
       Decode(Sign(QII.Probability-Round(Rand()*100,0)),-1,0,1) Prbblty
  from Questionary_Items QII
 order by 8 desc

The DDL of Questionary_Items is:
CREATE TABLE QUESTIONARY_ITEMS (
  ID_QSTNRY SMALLINT NOT NULL,
  ID_QSTN SMALLINT NOT NULL,
  ENABLED SMALLINT DEFAULT '1' NOT NULL,
  SECONDS SMALLINT DEFAULT '60' NOT NULL,
  MIN_SCORE SMALLINT DEFAULT '1' NOT NULL,
  PROBABILITY SMALLINT DEFAULT '100' NOT NULL,
  SEQ SMALLINT DEFAULT '0' NOT NULL);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT PK_QUESTIONARY_ITEMS PRIMARY KEY(ID_QSTNRY,ID_QSTN);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT FK_QUESTIONARY_ITEMS_00 FOREIGN KEY (ID_QSTNRY) REFERENCES QUESTIONARY(ID);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT FK_QUESTIONARY_ITEMS_01 FOREIGN KEY (ID_QSTN) REFERENCES QUESTIONS(ID);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT CK_QUESTIONARY_ITEMS_01 CHECK(Enabled in (0,1)
);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT CK_QUESTIONARY_ITEMS_02 CHECK(Seconds>0);
ALTER TABLE QUESTIONARY_ITEMS ADD CHECK (Seq>=0);
ALTER TABLE QUESTIONARY_ITEMS ADD CHECK (MIN_SCORE>=1);

Tha data to populate the table:
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 1, 1, 10, 6, 98, 12);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 2, 1, 3, 10, 52, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 3, 1, 3, 10, 25, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 4, 1, 7, 8, 81, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 5, 1, 2, 3, 98, 16);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 6, 1, 2, 9, 1, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 7, 1, 8, 2, 72, 14);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 8, 1, 6, 7, 57, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 9, 1, 9, 6, 63, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 10, 1, 6, 5, 81, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 11, 1, 11, 8, 89, 19);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 12, 1, 4, 2, 75, 11);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 13, 1, 1, 1, 99, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 14, 1, 4, 4, 54, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 15, 1, 4, 4, 68, 2);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 16, 1, 3, 10, 88, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 17, 1, 6, 7, 2, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 18, 1, 4, 2, 34, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 19, 1, 7, 5, 57, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 20, 1, 8, 5, 71, 7);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 21, 1, 9, 7, 58, 16);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 22, 1, 7, 3, 74, 0);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 23, 1, 9, 10, 90, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 24, 1, 7, 2, 8, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 25, 1, 5, 8, 98, 11);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 26, 1, 7, 8, 55, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 27, 1, 7, 10, 83, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 28, 1, 6, 9, 38, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 29, 1, 2, 8, 72, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 30, 1, 9, 6, 22, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 31, 1, 10, 7, 42, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 32, 1, 5, 8, 9, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 33, 1, 8, 5, 98, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 34, 1, 9, 4, 77, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 35, 1, 10, 8, 95, 20);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 36, 1, 2, 5, 56, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 37, 1, 1, 2, 72, 12);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 38, 1, 10, 7, 74, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 39, 1, 3, 10, 19, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 40, 1, 7, 10, 87, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 41, 1, 7, 10, 72, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 42, 1, 2, 8, 46, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 43, 1, 6, 9, 74, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 44, 1, 3, 9, 30, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 45, 1, 11, 2, 34, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 46, 1, 1, 6, 14, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 47, 1, 4, 4, 86, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 48, 1, 10, 7, 25, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 49, 1, 7, 7, 74, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 50, 1, 11, 4, 2, 1);


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 05/Sep/15 07:02 PM
Oscar,

What happens if you execute:

SELECT *
FROM (
select QII.Id_Qstnry,
       QII.Id_Qstn,
       QII.Enabled,
       QII.Seconds,
       QII.Min_Score,
       QII.Probability,
       QII.Seq,
       Decode(Sign(QII.Probability-Round(Rand()*100,0)),-1,0,1) Prbblty
  from Questionary_Items QII
)
ORDER BY Prbblty

Oscar Giovani Blanco added a comment - 05/Sep/15 07:28 PM
I had tested it on that way but the result is the same... in fact the real query is:
select Q.Quantity,
       QI.Id_Qstn,
       (select Text from Questions where Id=QI.Id_Qstn) TextQ,
       QI.Seconds,
       QI.Min_Score,
       QI.Prbblty,
       QI.Seq QISeq,
       QO.Id_Option,
       (select Text from Options where Id=QO.Id_Option) TextO,
       QO.Points,
       QO.Seq QOSeq
  from Questionary_Options QO,
       (select QII.*,
               Decode(Sign(QII.Probability-Round(Rand()*100,0)),-1,0,1) Prbblty
          from Questionary_Items QII
       ) QI,
       Questionary Q
 where Q.enabled=1
   and Q.Id=8
   and QI.Id_Qstnry=Q.Id
   and QI.Id_Qstnry=QO.Id_Qstnry
   and QI.Id_Qstn =QO.Id_Qstn
 order by 6 asc,... desc, ....

Obviously, there are more fields enumerated after the '6' in the 'order by' clausule, but the main field (and which must not fail) is the 6.

Dmitry Yemanov added a comment - 05/Sep/15 07:44 PM
The actual problem is that any expression is re-evaluated after the sort. With RAND() it produces visually different output (looking like unsorted) even if it was really properly sorted underneath. This is a historical behaviour nobody cared to change yet.

Oscar Giovani Blanco added a comment - 05/Sep/15 08:06 PM
hmm... so, isn't this a bad behaviour?, re-evaluate every field would be a expensive operation if some field is hard to calculate, even more... if this makes changes on another places... i think, in the midst of my ignorance.

Dmitry Yemanov added a comment - 05/Sep/15 09:50 PM
This behavior has its technical benefits, but formally (from POV of the declarative SQL nature) it's bad. I wouldn't expect it being immediately fixed though, so you need to find some workaround.

Oscar Giovani Blanco added a comment - 05/Sep/15 10:04 PM
hmm... If your result set is huge and / or has many columns of which are various expressions, even regardless user functions that make changes... this should really be fixed. Well, if you say me this is just a visual effect, i guess it will work... by now. Thanks for attention.

Dmitry Yemanov made changes - 19/Apr/18 05:13 AM
Field Original Value New Value
Link This issue is duplicated by CORE-5798 [ CORE-5798 ]
Dmitry Yemanov made changes - 19/Apr/18 01:55 PM
Link This issue is duplicated by CORE-5798 [ CORE-5798 ]