Issue Details (XML | Word | Printable)

Key: DOC-4
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Helen Borrie
Reporter: Pavel Cisar
Votes: 0
Watchers: 0
Operations

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

PSQL SELECT with ORDER BY :variable

Created: 29/Mar/06 12:00 AM   Updated: 14/Jun/06 09:31 AM
Component/s: None
Affects Version/s: None
Fix Version/s: None

SF_ID: 1460715


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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.

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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
  ...

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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.

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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.

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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.

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
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.

Alice F. Bird added a comment - 14/Jun/06 09:31 AM
Date: 2006-03-29 16:58
Sender: pcisar
Logged In: YES
user_id=4169

Verified on Firebird 1.5.2 and 2.0 RC1