Different behavior between v15 and v17 using WITH CTE

From: marcos sicat <marcos(dot)sicat(at)atlasifs(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Different behavior between v15 and v17 using WITH CTE
Date: 2025-05-07 18:58:59
Message-ID: MW5PR84MB2227D108F548B54B35372913F288A@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I am running this query on v17 and generating the results, but not on v15.

Query:

WITH _tv_datapoints
AS
( SELECT * from(SELECT distinct p.order_book,
(Cast(p.execution_price AS double precision) / 10000) AS "trade_price",
p.msg_date as trade_time ,
row_number() OVER (PARTITION BY p.execution_price ORDER BY p.msg_date) rn
FROM PUBLIC.prod_itchbbo_p_small_message p
WHERE p.order_book = 5082
AND Date(p.added_date) = '20250507'
AND p.printable = 'Y'
ORDER BY p.msg_date asc )
)

select tv.order_book, tv.trade_price, tv.trade_time,
stats.volume, stats.high_price, stats.low_price, stats.open_price from _tv_datapoints tv ,
LATERAL iq_get_stats_security_v1_4_4(tv.order_book,(
( SELECT
DATE(d.added_date) AS DATE
FROM
prod_itchbbo_s_message d
WHERE event_code = 'S'
ORDER BY
d.added_date DESC
LIMIT
1))::TIMESTAMP without TIME zone)
stats(value,
volume,
avg_price,
high_price,
low_price,
stock_date,
bestbidprice,
bestbidsize,
bestofferprice,
bestoffersize,
last_trade_price,
close_price,
open_price,
prev_close_price
)
WHERE rn = 1
;

In v15:

[Code: 0, SQL State: 42601] ERROR: subquery in FROM must have an alias
Hint: For example, FROM (SELECT ...) [AS] foo.
Position: 39 [Script position: 38 - 45]

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2025-05-07 19:03:12 Re: Different behavior between v15 and v17 using WITH CTE
Previous Message Andrei Lepikhov 2025-05-07 18:42:16 Re: Incorrect calculation of path fraction value in MergeAppend