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
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 ]