Failed to create materialized view due to name conflict

[gangtao]

proton-client
proton client version 1.3.24.
Connecting to localhost:8463 as user default.
Connected to proton server version 1.3.24 revision 54459.

CREATE STREAM OrderBook
(`OrderId` string DEFAULT uuid(), `Symbol` string, `Side` enum('buy', 'sell'), `OrderQty` float, `OrdType` enum('MARKET', 'LIMIT'), `Price` float, `LastQty` float DEFAULT 0, `CumQty` float DEFAULT 0, `LeavesQty` float DEFAULT 0, `OrdStatus` enum('New', 'Partially Filled', 'Filled') DEFAULT 1
)
PRIMARY KEY OrderId

CREATE MATERIALIZED VIEW mv_MatchOrders INTO OrderBook
(`OrderId` string, `Symbol` string, `Side` enum('buy', 'sell'), `OrderQty` float, `OrdType` enum('MARKET', 'LIMIT'), `Price` float, `LastQty` float, `CumQty` float, `LeavesQty` float, `OrdStatus` enum('New', 'Partially Filled', 'Filled')
) AS
WITH order AS
  (
    SELECT 
      *
    FROM 
      OrderBook
  ), book AS
  (
    SELECT 
      *
    FROM 
      OrderBook
    WHERE 
      _tp_time > earliest_ts()
  )
SELECT 
  order.OrderId, Symbol, Side, OrderQty, OrdType, Price, least(order.OrderQty, book.OrderQty) AS LastQty, order.CumQty + order.LastQty AS CumQty, order.OrderQty - order.CumQty AS LeavesQty, if(LeavesQty = 0, 'Filled', 'Partially Filled') AS OrdStatus
FROM 
  order
INNER JOIN book ON order.Price = book.Price
WHERE 
  (order.Symbol = book.Symbol) AND (order.Side != book.Side) AND (order.OrdStatus != 'Filled') AND (book.OrdStatus != 'Filled')

Query id: 55c0be24-977b-473c-beae-c2eea8b0d30f


0 rows in set. Elapsed: 0.017 sec. 

Received exception from server (version 1.3.24):
Code: 47. DB::Exception: Received from localhost:8463. DB::Exception: Unknown identifier: LastQty; there are columns: OrderId, Symbol, Side, OrderQty, OrdType, Price, CumQty, OrdStatus, book.Symbol, book.Side, book.OrderQty, book.OrdStatus, equals(Symbol, book.Symbol), not_equals(Side, book.Side), 'Filled', 'Filled', not_equals(OrdStatus, 'Filled'), not_equals(book.OrdStatus, 'Filled'), and(equals(Symbol, book.Symbol), not_equals(Side, book.Side), not_equals(OrdStatus, 'Filled'), not_equals(book.OrdStatus, 'Filled')), least(OrderQty, book.OrderQty). (UNKNOWN_IDENTIFIER)

In the above query, the field LastQty has caused conflict, while it should not.

Rewriting to following query works but it is not necessary

CREATE MATERIALIZED VIEW mv_MatchOrders INTO OrderBook(
                    OrderId string,
                    Symbol string,
                    Side enum('buy','sell'),
                    OrderQty float,
                    OrdType enum('MARKET','LIMIT'),
                    Price float,
                    LastQty float,
                    CumQty float,
                    LeavesQty float,
                    OrdStatus enum('New','Partially Filled','Filled')
                   ) AS WITH
                    order AS (SELECT * FROM OrderBook WHERE OrdStatus != 'Filled' and _tp_delta !=-1), -- latest order
                    book AS (SELECT * FROM OrderBook WHERE _tp_time > earliest_ts() and OrdStatus != 'Filled' and _tp_delta !=-1), -- all previous orders
                   fill AS ( SELECT order.OrderId,Symbol,Side,OrderQty,OrdType,Price,
                    least(order.OrderQty,book.OrderQty) AS xLastQty, -- here, do the math for Qty updates
                    order.CumQty  + order.LastQty AS CumQty,
                    order.OrderQty - order.CumQty AS LeavesQty,
                    if(LeavesQty=0,'Filled','Partially Filled') AS OrdStatus -- update state
                   FROM order JOIN book
                     ON order.Price = book.Price
                   WHERE order.Symbol = book.Symbol
                    AND order.Side <> book.Side -- match orders in FIFO order, where price/Symbol same, side opposite
                    )
                    select * except(xLastQty) , xLastQty  as LastQty from fill

[yokofly]

for a temporary workaround:
change the order or change the naming

-- SELECT least(order.OrderQty, book.OrderQty) AS LastQty, order.CumQty + order.LastQty AS CumQty
++ SELECT order.CumQty + order.LastQty AS CumQty, least(order.OrderQty, book.OrderQty) AS LastQty
OR
++ SELECT least(order.OrderQty, book.OrderQty) AS LastXQty, order.CumQty + order.LastQty AS CumQty

[yokofly]

create stream cte1   (  `Id` int32,   `OrderQty` float,   `LastQty` float);
create stream cte2  (  `Id` int32,   `OrderQty` float,   `LastQty` float);
-- runnable:
SELECT 
  cte1.LastQty + cte1.OrderQty AS LastQty,
  least(cte1.LastQty, cte2.LastQty) AS OrderQty
FROM 
  cte1
INNER JOIN cte2 ON cte1.Id = cte2.Id;
-- cannot run without visit again:
SELECT 
  least(cte1.LastQty, cte2.LastQty) AS OrderQty,
  cte1.LastQty + cte1.OrderQty AS LastQty
FROM 
  cte1
INNER JOIN cte2 ON cte1.Id = cte2.Id;