Re: Different behavior between v15 and v17 using WITH CTE

Thanks, David.

After adding the alias, the query is working in v15.

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 ) AS TRADES_P
)

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
;

From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Date: Wednesday, May 7, 2025 at 3:03 PM
To: marcos sicat <marcos(dot)sicat(at)atlasifs(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Different behavior between v15 and v17 using WITH CTE

On Wednesday, May 7, 2025, marcos sicat <marcos(dot)sicat(at)atlasifs(dot)com<mailto:marcos(dot)sicat(at)atlasifs(dot)com>> wrote:
I am running this query on v17 and generating the results, but not on v15.

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]

Yes, we’ve since removed the requirement to assign the alias. If you need to run the query in v15 you will need to provide one.

David J.