42p10 error for select distinct order by expressions must appear in select list - Исправление ошибок и поиск оптимальных решений проблем

Syntactic vs logical order of operations

I think that the confusion around the relationship between DISTINCT and ORDER BY (or also GROUP BY, for that matter), can only really be understood if the logical order of operations in SQL is understood. It is different from the syntactic order of operations, which is the primary source of confusion.

In this example, it looks as though DISTINCT is related to SELECT, given its syntactic closeness, but it’s really an operator that is applied after SELECT (the projection). Due to the nature of what DISTINCT does (remove duplicate rows), all the not-projected contents of a row are no longer available after the DISTINCT operation, which includes the ORDER BY clause. According to the logical order of operations (simplified):

FROM (produces all possible column references)
WHERE (can use all column references from FROM)
SELECT (can use all column references from FROM, and create new expressions, and alias them)
DISTINCT (operates on the tuple projected by SELECT)
ORDER BY (depending on the presence of DISTINCT, can operate on the tuple projected by SELECT, and if DISTINCT is absent *perhaps (depending on the dialect) also on other expressions)

What about `DISTINCT` and `ORDER BY`

The fact that, without DISTINCT, ORDER BY can access (in some dialects) also things that haven’t been projected may be a bit weird, certainly useful. E.g. this works:

WITH emp (id, fname, name) AS (
  VALUES (1, 'A', 'A'),
         (2, 'C', 'A'),
         (3, 'B', 'B')
)
SELECT id
FROM emp
ORDER BY fname DESC

dbfiddle here. Producing

id
--
2
3
1

This changes when you add DISTINCT. This no longer works:

WITH emp (id, fname, name) AS (
  VALUES (1, 'A', 'A'),
         (2, 'C', 'A'),
         (3, 'B', 'B')
)
SELECT DISTINCT name
FROM emp
ORDER BY fname DESC

dbfiddle here. The error being:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 8: ORDER BY fname DESC

Because what fname value would you attribute to name = A? A or C? The answer would decide whether you’d be getting A, B as a result or B, A. It cannot be decided.

PostgreSQL `DISTINCT ON`

Now, as mentioned in the above linked article, PostgreSQL supports an exception to this, which can occasionally be useful: DISTINCT ON (see also questions like these):

WITH emp (id, fname, name) AS (
  VALUES (1, 'A', 'A'),
         (2, 'C', 'A'),
         (3, 'B', 'B')
)
SELECT DISTINCT ON (name) id, fname, name
FROM emp
ORDER BY name, fname, id

dbfiddle here, producing:

id |fname|name
---|-----|----
1  |A    |A   
3  |B    |B

This query allows to produce only distinct values of name, and then per duplicate row, take the first one given the ORDER BY clause, which makes the choice per distinct group unambiguous. This can be emulated in other RDBMS using window functions.

Источник

Steps to reproduce

Create a table with at least one Guid (non-nullable), one DateTime (non-nullable) and one boolean (non-nullable) fields.
Populate the table with valid data.
Run the following query:

var idForInvestigation = Guid.NewGuid();
var limit = 100;
var result = context.MyTestTables
    .Where(x => x.IsChecked && x.ParentId == idForInvestigation)
    .OrderBy(x => x.CreatedAt)
    .Distinct()
    .Skip(limit)
    .Take(limit + 1)
    .ToList();

The issue

The code produces the following SQL query:

SELECT DISTINCT t."ParentId", t."IsChecked", t."CreatedAt", t."CreatedBy", t."Title", t."Description"
FROM public."MyTestTable" AS t
WHERE t."IsChecked" AND (t."Id" = @__idForInvestigation_0)
ORDER BY (SELECT 1)
LIMIT @__p_2 OFFSET @__p_1

The query fails with the exception message:

Npgsql.PostgresException (0x80004005): 42P10: for SELECT DISTINCT, ORDER BY expressions must appear in select list
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   ...local files...
  Exception data:
    Severity: ERROR
    SqlState: 42P10
    MessageText: for SELECT DISTINCT, ORDER BY expressions must appear in select list
    Position: 390
    File: parse_clause.c
    Line: 2822
    Routine: transformDistinctClause

It seems the .OrderBy(x => x.CreatedAt) is incorrectly translated into ORDER BY (SELECT 1) instead of ORDER BY t."CreadtedAt". I have tried ordering the query by other columns, but the exception remains the same. When I remove the .Distinct() clause, the query is evaluated successfully:

SELECT t."ParentId", t."IsChecked", t."CreatedAt", t."CreatedBy", t."Title", t."Description"
FROM public."MyTestTable" AS t
WHERE t."IsChecked" AND (t."Id" = @__idForInvestigation_0)
ORDER BY t."CreatedAt"
LIMIT @__p_2 OFFSET @__p_1

Further technical details

Npgsql version: 4.1.2
Npgsql.EntityFrameworkCore.PostgreSQL: 3.1.0
PostgreSQL version: 10.6
Operating system: Windows 10 64-bit

Other details about my project setup: ASP.NET Core 3.1 web application

Источник

Содержание

ru_postgres
Russian PostgreSQL DBA
Problem with ORDER BY and DISTINCT ON
Как исправить сортировку полученных данных из БД?
Django distinct() query method introduction
1. What is a distinct expression?
2. How to apply this in Django?
3. Let’s see some examples
3. a. How to apply distinct on all columns or a few columns?
# How do get unique data?
Conclusion

ru_postgres

Russian PostgreSQL DBA

За время моей работы программистом я несколько раз сталкивался со следующей задачей: получить выборку нескольких последних объектов при условии, что из каждой группы объектов нужно выбрать только один последний. Это могут быть: страница с последними сообщениями на форуме, но при условии не более одного сообщения от каждого пользователя. Или страница с последними картинками в фотогалерее с таким же условием. Или страница последних статей при условии только одной статьи из каждого раздела.

Квалифицированный веб-программист сразу же скажет, что такая страница — отличный кандидат в кэш. Однако могут быть дополнительные условия, которые сделают мысль о кэше совсем не такой привлекательной, например, если на эту выборку должны быть наложены дополнительные условия, индивидуальные для каждого пользователя: списки друзей, подписка на группы или категории, права доступа и т.п. И даже если кэширование возможно, выборка для кэша, длящаяся несколько секунд, крайне неприятна.

Поэтому рассмотрю оптимизацию «честной» выборки на уровне SQL запросов.

Беглое знакомство с возможностями PostgreSQL должно подкинуть мысль об использовании конструкции DISTINCT ON:
SELECT DISTINCT ON (user_id) * FROM images ORDER BY post_date DESC;

К сожалению, текущая реализация DISTINCT ON в PostgreSQL использует сортировку и требует, чтобы первыми в ORDER BY стояли те же поля, что и в DISTINCT ON:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

В результате для получения такой выборки «в лоб» необходим подзапрос:
SELECT * FROM (SELECT DISTINCT ON (user_id) * FROM images ORDER BY user_id, post_date) AS images ORDER BY post_date DESC LIMIT 5;

К сожалению, переместить в этой выборке LIMIT внутрь подзапроса нельзя, поэтому внутренний SELECT сделает full table scan с сортировкой, а потом внешний SELECT сделает свою сортировку с наложением LIMIT. Уже при нескольких десятках тысяч объектов в таблице такой запрос начинает существенно раздражать.

Можно попытаться переписать этот запрос с использованием GROUP BY, но избежать полного перебора строк таблицы средствами SQL мне не удалось.

В идеале — надо копнуть код PostgreSQL, снять ограничение на список DISTINCT ON, а потом научить оптимизатор запросов делать DISTINCT ON выборкой по индексу без сортировки всей таблицы.

А до тех пор, пока энтузиаста не нашлось, можно применить PL/pgSQL. Алгоритм простой: для каждой строки из таблицы, отсортированной по «полю сортировки», выдаём её на выход, если её «категория» ещё не встречалась, а идентификаторы «категорий» собираем в массив.

Что мы имеем на выходе.
Запрос «в лоб»:

Total runtime: 10600.052 ms

Теперь функция:
Total runtime: 1.594 ms

И для сравнения, полная выборка таблицы без сортировок:
Seq Scan on t (cost=0.00..1491.00 rows=100000 width=12) (actual time=0.021..2357.040 rows=100000 loops=1)
Total runtime: 4680.567 ms
Или с сортировкой:
Index Scan using t_sort_idx on t (cost=0.00..2878.26 rows=100000 width=12) (actual time=0.000..2635.212 rows=100000 loops=1)
Total runtime: 4899.435 ms

Какие недостатки есть у описанного подхода?

К сожалению, на данный момент PostgreSQL не умеет выдавать строки из функции по одной, вместо этого он накапливает их все. Поэтому приходится задавать LIMIT в качестве параметра функции. А это означает, в свою очередь, что любые параметры, которые могут ограничить выборку, в том числе для присоединённых по JOIN таблиц, также надо вносить в функцию. То есть придётся либо дописывать функцию для каждого параметра, либо писать универсальную функцию с параметром «текст запроса».

Также этот алгоритм предполагает относительно равномерное распределение категорий по строкам: если какой-то категории принадлежит больше половины строк таблицы, функция может стать крайне медленной. Если бы этот алгоритм был реализован в планировщике PostgreSQL, он мог бы учитывать этот случай и при необходимости возвращаться к выборке с сортировкой.

Источник

Problem with ORDER BY and DISTINCT ON

To: pgsql-sql(at)postgresql(dot)org Subject: Problem with ORDER BY and DISTINCT ON Date: 2008-07-16 07:39:47 Message-ID: 20080716073955.EB1DF64FCBD@postgresql.org Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-sql

I’m a little baffled. I’m trying to generate a SQL statement that
issues a DISTINCT ON using the same values as my ORDER BY statement.
I’m using a somewhat complex CASE statement in my ORDER BY clause. I’m
on Pg 8.2. Here is some SQL to get you started at seeing my problem:

drop table if exists property;
create table property
( id serial,
state varchar(255),
search_rate_max decimal(8,2),
data_priority_code varchar(255)
);

SELECT DISTINCT ON
(«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
property.id)
property.id
FROM property WHERE ((«property».»data_priority_code» IS NOT NULL))
ORDER BY
«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
property.id
LIMIT 10 OFFSET 0

—————-
RESULTS: ERROR: SELECT DISTINCT ON expressions must match initial ORDER
BY expressions
SQL state: 42P10
—————-

Now if you run this statement it works

SELECT DISTINCT ON
(«property».»state»,
property.id)
property.id
FROM property WHERE ((«property».»data_priority_code» IS NOT NULL))
ORDER BY
«property».»state»,
property.id
LIMIT 10 OFFSET 0

However if you run this statement it ALSO works, which tells me it’s
not just my CASE statements that are messing things up (note in this
example, I just removed the primary key «property.id» from the ORDER BY
and DISTINCT ON clauses:

SELECT DISTINCT ON
(«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»
)
property.id
FROM property WHERE ((«property».»data_priority_code» IS NOT NULL))
ORDER BY
«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»
LIMIT 10 OFFSET 0

———————
RESULTS: ERROR: SELECT DISTINCT ON expressions must match initial ORDER
BY expressions
SQL state: 42P10
———————

Finally, if you run this statement it works fine (removing one of the
duplicate search_rate_max statements):

SELECT DISTINCT ON
(«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
property.id)
property.id
FROM property WHERE ((«property».»data_priority_code» IS NOT NULL))
ORDER BY
«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
property.id LIMIT 10 OFFSET 0

What’s going on here? Am I doing something that isn’t legitimate SQL? I
can’t see why having a duplicate CASE statement should foul things up
like this? It’s pretty clear (from additional testing not included in
this email) that the duplicate «search_rate_max» CASE is causing the
problem.

Thanks for any advice or suggestions on how to get this to run
correctly. Is this a bug?

Basically I’m doing this as an optimization — I can get much better
performance running the DISTINCT ON in some circumstances than using
DISTINCT, but the edge case above is breaking my tests and preventing
me from implementing the idea. The code is generated by an application
layer which is not really paying attention to whether or not the two
CASE statements apply to the same field or not (sometimes they do
sometimes they don’t)..

Источник

Как исправить сортировку полученных данных из БД?

Есть такой запрос:

Нужно отсортировать полученные данные только по position, но на деле сейчас полученные данные сортируются по post_id, а сортировка по position игнорируется.

Притом если схитрить таким образом:
.order(position: :desc, post_id: :desc)

PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1

Точно такая же ошибка, если вообще не указывать «post_id» в ORDER. «post_id» должно всегда находиться в ORDER и всегда должно быть первым, так как используется DISTINCT.

Вопрос задан более трёх лет назад
382 просмотра

Оценить 2 комментария

Прежде всего нужно понять, что вы вообще хотите получить от запроса? Сформулируйте, пожалуйста, простыми словами и озвучьте.

В вашем запросе без указания сортировки база данных не может понять, по какому принципу отбирать уникальные post_id. Добавив это поле в ORDER BY, запрос начинает проходить, однако следующие уточнения по поводу позиции отбрасываются. Но это тоже логично! Ведь сортировка уточняется только когда по предыдущему полю идут одинаковые значения, а тут по полю post_id все значения разные (благодаря distinct).

Следовательно, вам нужно использовать вложенные запросы. Сначала находим уникальные посты, а потом внешним селектом сортируем по position.

Прежде всего нужно понять, что вы вообще хотите получить от запроса? Сформулируйте, пожалуйста, простыми словами и озвучьте.

Есть множество записей (posts_and_images), принадлежащих конкретному пользователю. Исходный массив выглядит так — множество записей с одинаковыми «post_id», а также остальными атрибутами.
Хочу сделать следующие — чтобы оставалась одна уникальная по «post_id» запись, но отсортированная по «position».

На текущий момент реализация уперлась в такой замечательный (на самом деле нет) костыль:

Разумеется, тут вынимаются все записи, а затем уже производится работа над Hash’ом. Это очень неоптимизированное решение, но я не знаю как иначе реализовать свою идею.

Антон, есть 2 варианта решения задачи.

Если у вас position для каждого post_id начинает свой отсчёт с нуля и они уникальны в рамках post_id, а также не содержат null-значений, то очевидно мы можем просто-напросто выбирать именно эти записи через условие where: `select * from posts_and_images where position = 0`.

Если position может иметь любое значение, например для каких-то post_id начинается с нуля, а для каких-то с 2 и идти не подряд, то нужно использовать оконные функции: `select * from (select *, row_number() over (partition by post_id order by position) as number from posts_and_images) ordered where number = 1`.

Источник

Django distinct() query method introduction

In this article, we are going to see how the DISTINCT expression/query method works on Django queryset and with different databases.

What is a distinct expression?
How to use this in Django?
Code examples and some notes.

1. What is a distinct expression?

Distinct is an expression/query clause used in SQL to remove duplicated results and return unique results based on the column we select.

2. How to apply this in Django?

We can use a distinct() query method at the end or in between queryset and with other regular Django query methods like all(), values(), etc…

3. Let’s see some examples

a). I created a simple model called user.

b) Loaded the sample data to the user model and it looks like the below.

c) Excecute the distinct() query and printing its results and corresponding SQL query.

In the above code, you can see the DISTINCT method is applied to all the available columns on the model(actually we never use this code because it returns all results like all(), at least as of now no use).

3. a. How to apply distinct on all columns or a few columns?

what is applying distinct on all columns?

Applying distinct on all columns using the below format.

distinct widely used while joining related fields.

2. what is applying distinct on a few columns?

Applying distinct on multiple columns using the below format and its works only in PostgreSQL database. in this case, returned results will be unique to the combination of the given columns.

SELECT DISTINCT ON table_column1, . . . . . table_column_n;

Note: the distinct method will also be considered the null as a unique value.

DISTINCT ON only applicable in PostgreSQL.

# How do get unique data?

a. one-way is using values()

# Let’s apply the DISTINCT ON expression on the age column and check this with different databases.

a. let’s check in the SQLite database.

b. let’s check in the PostgreSQL database.

in the above results, user id-2 was removed because it had duplicated value of age 21.

c. let’s check in the MYSQL database.

Note: While querying on related fields may produce duplicated requests, for example, isnull on reverse relation will always give duplicates results consider the below example with the ForeignKey model called Home,

and added some sample data.

Or more clearly.

so you can use a distinct query method to remove duplicates.

Note: one caveat on distinct expression is it’s very slow, if you have millions of rows of data then you can easily notice the performance.

Note: order_by columns are always available to the distinct by default. Compare below two queries.

in the above code, I excluded the name field using the ONLY query from the SELECT statement. but the below code name field is included in the SELECT statement by order_by.

Note: always use the order_by clause in combination with distinct to predict results otherwise it returns arbitrary results.

Note: If you get any error like the below,

that means distinct expression fields must start with order_by fields.

d. While using values() with distinct() together then please remember ordering by fields can affect the results, regrading there is a note in the Django doc please take look at it.

Conclusion

distinct() query method used to remove duplicates.
DISTINCT ON expression only applicable to PostgreSQL(i did not check in the oracle database please let me know in the comment section).
order_by fields are always available in the select statement.
use order_by with distinct to predict the results.
be careful with model meta default ordering with related filed order in order_by (go through the Django website for more in-depth information).

******If you found this article helpful please follow me on MEDIUM.*******

Источник

I’m using PostgreSQL 9.5 and have the following PDO Exception

PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...CT DISTINCT hostname FROM drupal_accesslog ORDER BY aid DESC ^: SELECT DISTINCT hostname FROM {accesslog} ORDER BY aid DESC; Array ( ) in ip_geoloc_ips_to_be_synched() (linea 347 di modules/ip_geoloc/ip_geoloc.admin.inc).

AFAIK a query like

SELECT DISTINCT x FROM table ORDER BY y

is not correct since if there are many rows with the same x value and different y values, which one should be chosen for ordering?

Probably mysql is tolerant on this, but the result may not be always correct, as stated here: http://www.programmerinterview.com/index.php/database-sql/sql-select-dis…

PostgreSQL instead gives an error
https://www.postgresql.org/message-id/27009.1171559417@sss.pgh.pa.us

If the aim of the query was to select the rows with the maximum aid, it may be rewritten using a subquery as
SELECT hostname FROM (SELECT hostname,max(aid) FROM {accesslog} GROUP BY hostname) q1;

Источник

I have a DB in PostgreSQL. Now I want to get all the info data from Tag table and want to know the content of that tag. I write command select as below:

 SELECT DISTINCT t.id, t.keyword as title, t.status,
(CASE 
 WHEN a.type = 1 THEN 'News' 
 WHEN a.type = 2 THEN 'Compare'
 WHEN a.type = 3 THEN 'Tips'
 WHEN a.type = 4 THEN 'New Car'
 WHEN a.type = 5 THEN 'Gallery'
 WHEN a.type = 6 THEN 'Stories'
 ELSE 'Other'
 END
) AS content_type
FROM tags t
LEFT JOIN articletagmapping atm1 ON atm1.tagid = t.id
LEFT JOIN articles a ON a.id = atm1.articleid
ORDER BY t.id DESC, t.isnoindex DESC

But I got an error ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

 ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ... a on a.id = atm1.articleid Order by status desc, countartic...
                                                             ^
(execution time: 63 ms; total time: 485 ms)

Please explain to me if you know any reason.

Have 2 answer(s) found.

Sandeep Kumar
Nov 07 2020

In PostgreSQL when you want distinct result from SELECT query, column appear in ORDER BY have to appear in DISTINCT command.

You can change your query as below:

 SELECT DISTINCT t.id, t.keyword as title, t.status, t.isnoindex,
(CASE 
 WHEN a.type = 1 THEN 'News' 
 WHEN a.type = 2 THEN 'Compare'
 WHEN a.type = 3 THEN 'Tips'
 WHEN a.type = 4 THEN 'New Car'
 WHEN a.type = 5 THEN 'Gallery'
 WHEN a.type = 6 THEN 'Stories'
 ELSE 'Other'
 END
) AS content_type
FROM tags t
LEFT JOIN articletagmapping atm1 ON atm1.tagid = t.id
LEFT JOIN articles a ON a.id = atm1.articleid
ORDER BY t.id DESC, t.isnoindex DESC

I hope this answer is helpful for you.

I think the root cause of the error is that your are ordering using ORDER BY t.id DESC, t.isnoindex DESC, column t.isnoindex does not appear in the SELECT clause.
You can remove t.isnoindex column from ORDER BY or add more this column to the SELECT query.

Источник

Problem with ORDER BY and DISTINCT ON

drop table if exists property;
create table property
( id serial,
state varchar(255),
search_rate_max decimal(8,2),
data_priority_code varchar(255)
);

—————-
RESULTS: ERROR: SELECT DISTINCT ON expressions must match initial ORDER
BY expressions
SQL state: 42P10
—————-

Now if you run this statement it works

SELECT DISTINCT ON
(«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»
)
property.id
FROM property WHERE ((«property».»data_priority_code» IS NOT NULL))
ORDER BY
«property».»state»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»,
CASE WHEN («search_rate_max» IS NOT NULL) THEN 1 ELSE 2
END,»search_rate_max»
LIMIT 10 OFFSET 0

———————
RESULTS: ERROR: SELECT DISTINCT ON expressions must match initial ORDER
BY expressions
SQL state: 42P10
———————

Finally, if you run this statement it works fine (removing one of the
duplicate search_rate_max statements):

Thanks for any advice or suggestions on how to get this to run
correctly. Is this a bug?

Источник

Как исправить сортировку полученных данных из БД?

Есть такой запрос:

Притом если схитрить таким образом:
.order(position: :desc, post_id: :desc)

PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1

Вопрос задан более трёх лет назад
380 просмотров

Оценить 2 комментария

Прежде всего нужно понять, что вы вообще хотите получить от запроса? Сформулируйте, пожалуйста, простыми словами и озвучьте.

На текущий момент реализация уперлась в такой замечательный (на самом деле нет) костыль:

Антон, есть 2 варианта решения задачи.

Источник

Issues

Context Navigation

#24218 new New feature

Use sub-query in ORM when distinct and order_by columns do not match

Reported by:	Miroslav Shubernetskiy	Owned by:	nobody
Component:	Database layer (models, ORM)	Version:	dev
Severity:	Normal	Keywords:	subquery distinct order_by
Cc:	Harro	Triage Stage:	Accepted
Has patch:	yes	Needs documentation:	yes
Needs tests:	yes	Patch needs improvement:	no
Easy pickings:	no	UI/UX:	no

Description (last modified by Miroslav Shubernetskiy )

This ticket is to propose a slight change in ORM — use subqueries when querying a model where .distinct() and .order_by() (or .extra(order_by=()) ) leftmost columns do not match. For example:

The above generates the following SQL:

I am not sure about all backends however the above syntax is not allowed in PostgreSQL which produces the following error:

Here are PostgreSQL docs explaining why that is not allowed:

DISTINCT ON ( expression [, . ] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [. ] Note that the «first row» of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [. ] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

This ticket proposes to use subqueries in such situations which would use SQL:

The above is perfectly valid SQL and produces expected results (please note that ORDER_BY is in the outer query to guarantee that distinct results are correctly sorted).

I created a simple proof-of-concept patch by overwriting few things in SQLCompiler.as_sql() which seems to work pretty well. The patch only creates subquery when the above dilema is encountered which should not have any negative side-effects on existing queries (since such queries were not allowed by SQL). The patch also works on the .count() queries since Django then strips any ordering hence the subquery is never created.

Change History (11)

comment:1 Changed 8 years ago by Miroslav Shubernetskiy

comment:2 Changed 8 years ago by Carl Meyer

Needs documentation:	set
Needs tests:	set
Triage Stage:	Unreviewed → Accepted

Thanks for the patch! The feature idea generally makes sense to me — taking something that currently results in an error and making it instead return sane results. Tentatively accepting based on that. Some thoughts:

We need to check what the other core backends currently do with this query. If there are backends in which that query is currently not an error, but this patch would change their results, that might be a problem.

If I understand the Postgres docs correctly, your proposed subquery SQL results in an undefined/unpredictable selection of which actual row is chosen of each group where the DISTINCT ON query returns the same value. I guess if this is a problem, the answer is «make your ORDER BY match the DISTINCT ON «, but it seems like a non-obvious subtlety that might bite people.

If nobody else sees any blockers that I’m missing, the things that would still be needed here are:

a) Add a test demonstrating the new behavior.
b) Add/update the documentation as needed (I think this is a behavior that ought to be mentioned in the docs somewhere).
c) Turn it into a pull request so the CI runs on it and we can see if it breaks any existing tests.

Once those are done there may be some comments on code style or implementation choices. The POC code seems clear enough, but I’d defer to others who know the ORM better on whether it’s actually implemented at the right level / in the right place.

comment:3 follow-up: 4 Changed 8 years ago by Ramiro Morales

Per the Postgres documentation snippet, shouldn’t the ORDER BY actually be applied to the sub-query to ensure a predictable result?

Also, make sure to test behavior with both bare distinct() calls and calls where joins are involved (e.g. Model.distinct(‘fkfield__related_model_field’) )

comment:4 in reply to: 3 Changed 8 years ago by Carl Meyer

Per the Postgres documentation snipped, shouldn’t the ORDER BY actually be applied to the sub-query to ensure a predictable result?

I think the idea is that in this case the user has supplied an ORDER BY which _can’t_ be applied to the sub-query (because it doesn’t match the DISTINCT ON ), and so we accept unpredictable results in order to provide some results which match what the user requested.

This business of unpredictable results is what makes me most uncomfortable with the patch.

Would it be better to auto-generate an ORDER BY for the subquery matching the DISTINCT ON ?

If we did that, the subquery isn’t even needed: we could get the same results by just automatically prepending the ORDER BY such that it matches the DISTINCT ON .

Not sure if that’s a good idea, but it seems like perhaps at least as good an idea as the one proposed here.

comment:5 Changed 8 years ago by Carl Meyer

Actually that wouldn’t give the same results, never mind.

comment:6 Changed 8 years ago by Miroslav Shubernetskiy

Thank you for such quick responses!

This business of unpredictable results is what makes me most uncomfortable with the patch.

I completely agree with that statement. Let me explain my-use case and then maybe possible solutions.

The reason I needed distinct here is because otherwise whenever foo had multiple bars, I would get duplicate foo models due to the join. Since in my case I simply used bars for filtering foos, I didnt care about «unpredictable» order at all since I only cared about foos. As for sorting, initially I also thought that ORDER BY should be inside the inner query and DISTINCT on the outer query however for some reason when I did that, foos were not sorted by distance anymore.

This however is just my use-case and I do see how this can cause issues in other scenarios since you cannot be sure what comes first in the DISTINCT .

Here are some possible solutions:

It seems that whenever a pattern of many-guys is used to filter single guys on one/many-to-many and sorting is required, this pattern might be useful. So maybe this functionality should only be enabled in those cases.

What about if this behavior will be configurable? More specifically what about if the user will be able to control:

explicitly enable this functionary since otherwise Django cannot guarantee data integrity for all use-cases. This will force the user to explicitly acknowledge they want to do this hence eliminating the burden from Django to always provide data-integrity (and explain all the cases when it is not provided).
what goes to inner and outer subquery ( ORDER BY or DISTINCT ). Maybe even allow ORDER BY in both queries. So the user will pick some sort columns for both inner and outer query hence solving «predictability» of DISTINCT and allowing to sort overall results.

I do like the idea of making these things customizable however not sure what would be an API to do those customizations. Is there precedent like that in Django ORM for query customizations?

Perhaps the right solution is try not to solve my particular use-case but provide a more generic solution to use nested queries. Maybe use API something like:

Let me know your thoughts.

comment:7 Changed 8 years ago by Anssi Kääriäinen

We need to go wirh option 2, specifically the solution where the inner and outer query have differen order by clauses. This way you can order the results the way you want, yet pick the distinct element as you wish.

I’vw thought of a solution something like this

The idea is that the barrier call effectively forces everything left of the call to go into subquery, then things after the barrier go into outer query. This construct would be hugely useful in other use cases, too.

comment:8 Changed 8 years ago by Carl Meyer

@miki725 — In a case like yours, I’ve generally just done .distinct() instead of .distinct(‘id’) . Since you’re only selecting columns from foo, that should give the same results, and it makes Postgres happy with any ORDER BY clause you like, with no need for a subquery. So I’m not sure that your particular case actually offers a use case for this feature — in this case our support for DISTINCT ON is actually an «attractive nuisance.»

@akaariai — that API doesn’t feel quite right to me. order_by() in every other case determines the ordering of the returned results. Just because in this case we are also applying a SQL ORDER BY clause in the subquery (an implementation detail) doesn’t mean that overloading order_by() is the right API for it. For addressing this particular use case, I think a more intuitive API would be to introduce an ordering argument to .distinct() to allow customization of the ordering used to pick the distinct row. Of course, that’s a less general API — but I think we would need a more thorough list of the cases where subqueries are needed in order to see what a generic subqueries API should look like (e.g. are you intending to limit this API to cases where the outer query is simply a SELECT * from the subquery?). It feels to me that a generic subqueries API should involve passing one queryset to another, or wrapping one in another, much like the current case where passing a queryset to an __in filter results in a subquery.

comment:9 Changed 8 years ago by Anssi Kääriäinen

Yeah, the barrier() API is more a generic tool. No objection to adding a specific API for distinct.

Источник

Migrated from MySQL where this query worked:

SELECT DISTINCT
    code,
    title,
    country
FROM
    data_a
ORDER BY
    sortorder

Basically, get a list and sort it by a user/company defined sortorder that is not based on code, title, or country.

Now that I’m in Postgres, this query does not work and gets a:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 12:  sortorder
          ^
SQL state: 42P10
Character: 135

Doing this however returns duplicates because there are additional fields not listed here that make that entire record unique. I just want the unique values from code, title, or country in order of the sortorder.

I tried to do a sub-query thinking I could sort it in the sub-query and bring it into the parent query already sorted, but it just comes over in non particular order… well, maybe it is particular, but it isn’t the order we want.

SELECT
    DISTINCT s.*
FROM
    (
        SELECT
            code,
            title,
            country
        FROM
            data_a
        GROUP BY
            code,
            title,
            country,
            sortorder
        ORDER BY
            sortorder
    ) s;

Any suggestions for how to do this?

Источник

Syntactic vs logical order of operations

What about DISTINCT and ORDER BY

PostgreSQL DISTINCT ON

Steps to reproduce

The issue

Further technical details

<img decoding="async" src="https://l-stat.livejournal.net/img/community.gif" />ru_postgres

Russian PostgreSQL DBA

Problem with ORDER BY and DISTINCT ON

Как исправить сортировку полученных данных из БД?

Django distinct() query method introduction

1. What is a distinct expression?

2. How to apply this in Django?

3. Let’s see some examples

3. a. How to apply distinct on all columns or a few columns?

# How do get unique data?

Conclusion

Problem with ORDER BY and DISTINCT ON

Как исправить сортировку полученных данных из БД?

Issues

Context Navigation

#24218 new New feature

Use sub-query in ORM when distinct and order_by columns do not match

Description (last modified by Miroslav Shubernetskiy )

Change History (11)

comment:1 Changed 8 years ago by Miroslav Shubernetskiy

comment:2 Changed 8 years ago by Carl Meyer

comment:3 follow-up: 4 Changed 8 years ago by Ramiro Morales

comment:4 in reply to: 3 Changed 8 years ago by Carl Meyer

comment:5 Changed 8 years ago by Carl Meyer

comment:6 Changed 8 years ago by Miroslav Shubernetskiy

comment:7 Changed 8 years ago by Anssi Kääriäinen

comment:8 Changed 8 years ago by Carl Meyer

comment:9 Changed 8 years ago by Anssi Kääriäinen

Читайте также:

What about `DISTINCT` and `ORDER BY`

PostgreSQL `DISTINCT ON`

ru_postgres