New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Order By is not working when the column is a math calculation with the Rand function [CORE4925] #5216
Comments
Commented by: Sean Leyne (seanleyne) Oscar, What happens if you execute: SELECT * |
Commented by: Oscar Giovani Blanco (bgracso) I had tested it on that way but the result is the same... in fact the real query is: 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. |
Commented by: @dyemanov 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. |
Commented by: Oscar Giovani Blanco (bgracso) 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. |
Commented by: @dyemanov 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. |
Commented by: Oscar Giovani Blanco (bgracso) 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. |
Submitted by: Oscar Giovani Blanco (bgracso)
Votes: 1
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);
The text was updated successfully, but these errors were encountered: