Thread

1. Different behavior between v15 and v17 using WITH CTE

   marcos sicat <marcos.sicat@atlasifs.com> — 2025-05-07T18:58:59Z

   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]
   

2. Re: Different behavior between v15 and v17 using WITH CTE

   David G. Johnston <david.g.johnston@gmail.com> — 2025-05-07T19:03:12Z

   On Wednesday, May 7, 2025, marcos sicat <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.
   

3. Re: Different behavior between v15 and v17 using WITH CTE

   marcos sicat <marcos.sicat@atlasifs.com> — 2025-05-07T19:07:54Z

   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.