Skip to content
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

Open
firebird-automations opened this issue Sep 5, 2015 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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);

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

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:
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=http://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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE5798 [ CORE5798 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE5798 [ CORE5798 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant