BUG #15904: ERROR: argument of LIMIT must not contain variables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15904: ERROR: argument of LIMIT must not contain variables

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15904
Logged by:          Lakradi Marwan
Email address:      [hidden email]
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 ?

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15904: ERROR: argument of LIMIT must not contain variables

David G Johnston
On Thu, Jul 11, 2019 at 8:06 AM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15904
Logged by:          Lakradi Marwan
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15904: ERROR: argument of LIMIT must not contain variables

Lakradi Marwan
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 <[hidden email]> a écrit :
On Thu, Jul 11, 2019 at 8:06 AM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15904
Logged by:          Lakradi Marwan
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15904: ERROR: argument of LIMIT must not contain variables

David G Johnston
On Thu, Jul 11, 2019 at 9:26 AM Lakradi Marwan <[hidden email]> wrote:
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}

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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15904: ERROR: argument of LIMIT must not contain variables

Lakradi Marwan
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 <[hidden email]> a écrit :
On Thu, Jul 11, 2019 at 9:26 AM Lakradi Marwan <[hidden email]> wrote:
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}

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.