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.g.johnston@gmail.com>
Date: Wednesday, May 7, 2025 at 3:03 PM
To: marcos sicat <marcos.sicat@atlasifs.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Different behavior between v15 and v17 using WITH CTE

On Wednesday, May 7, 2025, marcos sicat <marcos.sicat@atlasifs.com<mailto:marcos.sicat@atlasifs.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.