Обсуждение: BUG #15839: Using text field for sorting in prepared query leads to wrong result

Поиск
Список
Период
Сортировка

BUG #15839: Using text field for sorting in prepared query leads to wrong result

От
PG Bug reporting form
Дата:
The following bug has been logged on the website:

Bug reference:      15839
Logged by:          Александр Черников
Email address:      me@helld.ru
PostgreSQL version: 10.6
Operating system:   Ubuntu 18.04.1
Description:

Example request:
select * from orders where ClientId=$1 and Moment >= $2 and Moment < $3
order by $4 desc, ClientId limit $5 offset ($5-1)*$6;
Field in question is $4
Expected result - poor performance or clear error that dynamic sorting
cannot be used

Re: BUG #15839: Using text field for sorting in prepared query leadsto wrong result

От
"David G. Johnston"
Дата:
On Fri, Jun 7, 2019 at 7:31 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15839
Logged by:          Александр Черников
Email address:      me@helld.ru
PostgreSQL version: 10.6
Operating system:   Ubuntu 18.04.1
Description:       

Example request:
select * from orders where ClientId=$1 and Moment >= $2 and Moment < $3
order by $4 desc, ClientId limit $5 offset ($5-1)*$6;
Field in question is $4
Expected result - poor performance or clear error that dynamic sorting
cannot be used

There is no bug here.

You wrote a valid query so no error should be issued.  I don't see where performance should come into this at all.

That parameters can only replace values, not identifiers, is a learning issue that one just needs to usually encounter and take into memory.  There is no substitute for experimentation and validating ones queries.  Quite few problematic queries will execute just fine but give unexpected results.

David J.