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

PSQL SELECT with ORDER BY :variable [DOC4] #18

Closed
firebird-automations opened this issue Mar 29, 2006 · 7 comments
Closed

PSQL SELECT with ORDER BY :variable [DOC4] #18

firebird-automations opened this issue Mar 29, 2006 · 7 comments

Comments

@firebird-automations
Copy link

Submitted by: @pcisar

SFID: 1460715#⁠
Submitted By: pcisar

Resultset is ordered "randomly" when sort order field
No. is specified by variable. It's undocumented "grey
zone" as it's not specified whether Firebird support
this or not. Anyway, it's a bug as Firebird should
either report an error or return corectly ordered result.

Sample procedure using example EMPLOYEE.FDB database:

SET TERM ^ ;

CREATE PROCEDURE TEST_ORDER (
ORD INTEGER)
RETURNS (
COUNTRY VARCHAR(15),
CURRENCY VARCHAR(10))
AS
begin
for select * from country order by :ord into
:country, :currency do
suspend;
end
^

SET TERM ; ^
COMMIT;

select * from test_order(1);

returns:

COUNTRY CURRENCY
=============== ==========
Germany D-Mark
Australia ADollar
Fiji FDollar
Belgium BFranc
France FFranc
Switzerland SFranc
Hong Kong HKDollar
Austria Schilling
Canada CdnDlr
Japan Yen
Italy Lira
USA Dollar
England Pound
Netherlands Guilder

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 19:11
Sender: pcisar
Logged In: YES
user_id=4169

Ok Dmitry, I incline to your opinion too. The problem is
that procedures are often written by developers that use
languages like Delphi and C++, so "compile time" and "at
runtime" have slighly different meaning for them than how it
works in Firebird, so we should document this really well,
so evrybody would really grok it. Even Helen's Firebird
Bible doesn't mention this pitfall.

This is now categorized as Documentation issue.

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 19:07
Sender: seanleyne
Logged In: YES
user_id=71163

The desired functionality is available through the use of
the EXECUTE STATEMENT syntax in the FOR ... DO loop.

Example:

FOR
EXECUTE STATEMENT
'
SELECT
...
FROM
...
'
INTO
...
DO
...

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 18:51
Sender: dimitr
Logged In: YES
user_id=61270

Implementors did consider everything, people just don't want
to understand the purpose of this feature. "ORDER BY
:variable" in their meaning cannot be supported by the
engine as the execution path is always determined before a
procedure gets executed. I agree the feature is a bit fuzzy,
but the rules are pretty simple. An expression can represent
an ordinal only if it's a compile-time integer constant.
"Compile-time" because the parser should get the value and
translate to a field reference, "integer" because
strings/dates/etc have no meaning here, "constant" because
the parser does not (and should not) evaluate expressions.
Neither "1+1" nor ":varible" are evaluated at compile time,
so they're runtime expressions. Perhaps we could determine
some expressions as being invariant at compile time and
throw an error in this case (as it won't change the data
ordering anyway). However, this would stop some useful
debugging tricks like writing ORDER BY "" to force a dummy
sort. My position is to document the current behaviour and
forget about the issue.

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 18:24
Sender: pcisar
Logged In: YES
user_id=4169

Certainly it's a side effect of ORDER BY <expression>
introduced in v1.5, but I still think that this is a bug,
either in documentation or in egine (we just have to decide
which one).

What is the core of this issue is that the definition of
expression is a little bit fuzzy here. While number 1
specified as literal is not handled as expression, but as
field possition number, simple single integer variable (no
calculations, not functions calls) is handled as expression,
although it's clear that it's a constant value.

I think that implementers did not considered the impact that
use of variable in "expression" would have, and that it was
not intended to happen in real applications. But variables
were "dragged in" by code that generaly handle expressions.
This order by expression simply dug a hole in previously
crystal clear order by logic that people can easily fall into.

So we can chose freely how to solve this issue:

1) We can mark it as pitfall (really anoying one, as people
are logically dragged to it) and clearly document it in
Release Notes / Manual (point out the issue of constant
expressions in ORDER BY). As an example we can use this
"switch sort column on demand" problem with alternate
solution which is the CASE function.

2) If engine can detect constant expression, then it could
handle integer ones as field possition and character ones as
field name. Or it can raise an exception or warnning.

3) If expression is just single variable, engine can raise
an exception.

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 18:07
Sender: dimitr
Logged In: YES
user_id=61270

Ivan is 100% correct, this is as designed. Order by
expression does not mean that an expression specifies the
field position.

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 17:31
Sender: prenosil
Logged In: YES
user_id=89535

I think it is not a bug - in fact you are not sorting by
field number, but by constant expression, so random order
is o.k.

@firebird-automations
Copy link
Author

Commented by: Alice F. Bird (firebirds)

Date: 2006-03-29 16:58
Sender: pcisar
Logged In: YES
user_id=4169

Verified on Firebird 1.5.2 and 2.0 RC1

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

3 participants