Обсуждение: BUG #15904: ERROR: argument of LIMIT must not contain variables
The following bug has been logged on the website:
Bug reference: 15904
Logged by: Lakradi Marwan
Email address: lakradimarwan@gmail.com
PostgreSQL version: 11.4
Operating system: Mac OS X - High Sierra
Description:
Good afternoon,
I would like to be able to conditionally limit my query (without python
script) based on a column value (for dynamic limit)
See example below:
SELECT summary.*
FROM
(
SELECT
id,
amount, date,
SUM(amount) OVER (PARTITION BY customer ORDER BY date, id) as
amount_summed
FROM
customer
ORDER BY date DESC, id DESC
) AS summary
LIMIT CASE WHEN summary.amount_summed >= 0 THEN summary.id ELSE NULL END;
The limit doesn't work, whereas :
- LIMIT CASE WHEN 100.0 >= 0 THEN 10 ELSE NULL END;
works well !
I don't see the point, what is the main difference between
summary.amount_summed which is a float, and directly pass 100.0 ?
On Thu, Jul 11, 2019 at 8:06 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15904
Logged by: Lakradi Marwan
Email address: lakradimarwan@gmail.com
PostgreSQL version: 11.4
Operating system: Mac OS X - High Sierra
Description:
I don't see the point, what is the main difference between
summary.amount_summed which is a float, and directly pass 100.0 ?
A limit applies to the query as a whole and cannot depend on individual records. If you think that is wrong explain what the following query is supposed to return and why.
SELECT *
FROM (VALUES (1, 'one'), (2, 'two')) vals (i, t)LIMIT CASE WHEN vals.i = 1 THEN 1 ELSE 2 END;
David J.
Thank you for your quick feedback,
In my opinion, your query should return :
Id, Text
1| 1, 'one'
and be interpreted as due to variable type :
LIMIT CASE WHEN 1 = 1 THEN 1 ELSE 2 END;
Am I wrong in the way I see the situation ?
I think that in the case of a variable, the data should be retrieved until the condition is reached. Like LIMIT_TILL {condition}
Marwan L.
Le jeu. 11 juil. 2019 à 17:27, David G. Johnston <david.g.johnston@gmail.com> a écrit :
On Thu, Jul 11, 2019 at 8:06 AM PG Bug reporting form <noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 15904
Logged by: Lakradi Marwan
Email address: lakradimarwan@gmail.com
PostgreSQL version: 11.4
Operating system: Mac OS X - High Sierra
Description:I don't see the point, what is the main difference between
summary.amount_summed which is a float, and directly pass 100.0 ?A limit applies to the query as a whole and cannot depend on individual records. If you think that is wrong explain what the following query is supposed to return and why.SELECT *FROM (VALUES (1, 'one'), (2, 'two')) vals (i, t)LIMIT CASE WHEN vals.i = 1 THEN 1 ELSE 2 END;David J.
On Thu, Jul 11, 2019 at 9:26 AM Lakradi Marwan <lakradimarwan@gmail.com> wrote:
Thank you for your quick feedback,In my opinion, your query should return :Id, Text1| 1, 'one'and be interpreted as due to variable type :LIMIT CASE WHEN 1 = 1 THEN 1 ELSE 2 END;Am I wrong in the way I see the situation ?I think that in the case of a variable, the data should be retrieved until the condition is reached. Like LIMIT_TILL {condition}
That's not an unreasonable expectation. But that isn't how LIMIT is defined. LIMIT provides a query result max record count to return to the client - mostly to facilitate pagination when used in concert with ORDER BY (for determinism) and OFFSET (to skip already seen records). That max is a constant determined at plan time which means it cannot rely upon any of the data the query itself may generate.
SQL is set oriented and your expectation is incompatible with that fundamental property of the system. If you don't want records "after" something you need to apply a inequality filter in the WHERE clause to remove the undesired records.
David J.
Thank you for the clarification and for the time you gave me.
I was convinced of that kind of answer, I still wanted to try, on a misunderstanding you never know !
I will find a workaround, however the LIMIT_TILL clause would have been welcomed in this particular case.
Wishing you an excellent weekend.
Marwan L.
Le jeu. 11 juil. 2019 à 18:45, David G. Johnston <david.g.johnston@gmail.com> a écrit :
On Thu, Jul 11, 2019 at 9:26 AM Lakradi Marwan <lakradimarwan@gmail.com> wrote:Thank you for your quick feedback,In my opinion, your query should return :Id, Text1| 1, 'one'and be interpreted as due to variable type :LIMIT CASE WHEN 1 = 1 THEN 1 ELSE 2 END;Am I wrong in the way I see the situation ?I think that in the case of a variable, the data should be retrieved until the condition is reached. Like LIMIT_TILL {condition}That's not an unreasonable expectation. But that isn't how LIMIT is defined. LIMIT provides a query result max record count to return to the client - mostly to facilitate pagination when used in concert with ORDER BY (for determinism) and OFFSET (to skip already seen records). That max is a constant determined at plan time which means it cannot rely upon any of the data the query itself may generate.SQL is set oriented and your expectation is incompatible with that fundamental property of the system. If you don't want records "after" something you need to apply a inequality filter in the WHERE clause to remove the undesired records.David J.