Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #12603: Results Ordering not correct

BUG #12603: Results Ordering not correct

From
aruggiero@codarex.com
Date:
The following bug has been logged on the website:

Bug reference:      12603
Logged by:          Antonio Ruggiero
Email address:      aruggiero@codarex.com
PostgreSQL version: 9.2.9
Operating system:   Windows 7 SP1
Description:

Summary:  A field is Integer but results ordered as if it is text.


/* START */
DROP TABLE IF EXISTS bug_test;
create table Bug_test (businessunit integer, businessunitname character
varying(15));

INSERT INTO bug_test VALUES (10,'North');
INSERT INTO bug_test VALUES (20,'NorthEast');
INSERT INTO bug_test VALUES (100,'East');
INSERT INTO bug_test VALUES (110,'SouthEast');
INSERT INTO bug_test VALUES (2000,'South');
INSERT INTO bug_test VALUES (2100,'SouthWest');
INSERT INTO bug_test VALUES (10000,'West');
INSERT INTO bug_test VALUES (10100,'NorthWest');


SELECT DISTINCT businessunit AS bu_id,
'('||businessunit::TEXT||')'||businessunitname as businessunit FROM bug_test
ORDER BY businessunit;
/* Output */
/* --Notice results ordered as if businessunit is TEXT--
bu_id, businessunit
integer, text
10;'(10)North'
100;'(100)East'
10000;'(10000)West'
10100;'(10100)NorthWest'
110;'(110)SouthEast'
20;'(20)NorthEast'
2000;'(2000)South'
2100;'(2100)SouthWest'
*/

SELECT DISTINCT businessunit AS bu_id,
'('||businessunit::TEXT||')'||businessunitname as businessunit FROM bug_test
ORDER BY 1;
/* Output */
/* --Notice results ordered as if businessunit is INTEGER--
bu_id, businessunit
integer, text
10;'(10)North'
20;'(20)NorthEast'
100;'(100)East'
110;'(110)SouthEast'
2000;'(2000)South'
2100;'(2100)SouthWest'
10000;'(10000)West'
10100;'(10100)NorthWest'
*/

DROP TABLE IF EXISTS bug_test;

/* END */

Re: BUG #12603: Results Ordering not correct

From
Guillaume Lelarge
Date:
Le 20 janv. 2015 17:21, <aruggiero@codarex.com> a =C3=A9crit :
>
> The following bug has been logged on the website:
>
> Bug reference:      12603
> Logged by:          Antonio Ruggiero
> Email address:      aruggiero@codarex.com
> PostgreSQL version: 9.2.9
> Operating system:   Windows 7 SP1
> Description:
>
> Summary:  A field is Integer but results ordered as if it is text.
>
>
> /* START */
> DROP TABLE IF EXISTS bug_test;
> create table Bug_test (businessunit integer, businessunitname character
> varying(15));
>
> INSERT INTO bug_test VALUES (10,'North');
> INSERT INTO bug_test VALUES (20,'NorthEast');
> INSERT INTO bug_test VALUES (100,'East');
> INSERT INTO bug_test VALUES (110,'SouthEast');
> INSERT INTO bug_test VALUES (2000,'South');
> INSERT INTO bug_test VALUES (2100,'SouthWest');
> INSERT INTO bug_test VALUES (10000,'West');
> INSERT INTO bug_test VALUES (10100,'NorthWest');
>
>
> SELECT DISTINCT businessunit AS bu_id,
> '('||businessunit::TEXT||')'||businessunitname as businessunit FROM
bug_test
> ORDER BY businessunit;
> /* Output */
> /* --Notice results ordered as if businessunit is TEXT--
> bu_id, businessunit
> integer, text
> 10;'(10)North'
> 100;'(100)East'
> 10000;'(10000)West'
> 10100;'(10100)NorthWest'
> 110;'(110)SouthEast'
> 20;'(20)NorthEast'
> 2000;'(2000)South'
> 2100;'(2100)SouthWest'
> */
>
> SELECT DISTINCT businessunit AS bu_id,
> '('||businessunit::TEXT||')'||businessunitname as businessunit FROM
bug_test
> ORDER BY 1;
> /* Output */
> /* --Notice results ordered as if businessunit is INTEGER--
> bu_id, businessunit
> integer, text
> 10;'(10)North'
> 20;'(20)NorthEast'
> 100;'(100)East'
> 110;'(110)SouthEast'
> 2000;'(2000)South'
> 2100;'(2100)SouthWest'
> 10000;'(10000)West'
> 10100;'(10100)NorthWest'
> */
>
> DROP TABLE IF EXISTS bug_test;
>
> /* END */
>

It is text. You renamed the second column businessunit.

Re: BUG #12603: Results Ordering not correct

From
David G Johnston
Date:
Guillaume Lelarge-3 wrote
>> SELECT DISTINCT businessunit AS bu_id,
>> '('||businessunit::TEXT||')'||businessunitname as businessunit FROM
> bug_test
>> ORDER BY businessunit;
>
> It is text. You renamed the second column businessunit.

To be more specific, the ORDER BY chooses an output column name before an
input column name.

http://www.postgresql.org/docs/9.1/static/sql-select.html

"Each expression can be the name or ordinal number of an output column
(SELECT list item), or it can be an arbitrary expression formed from
input-column values."

David J.



--
View this message in context:
http://postgresql.nabble.com/BUG-12603-Results-Ordering-not-correct-tp5834749p5834765.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.