Informix WITH SQL Recursive cannot onmode -z stop session
The below WITH SQL run on Informix demo DB will cause Recursive CTE:
WITH customer AS ( SELECT * FROM customer )
SELECT customer.customer_num , items.stock_num , items.order_num
, items.manu_code , items.item_num ,
items.quantity , items.total_price ,nvl(orders.backlog,'N') FROM customer , orders , items
WHERE orders.customer_num = customer.customer_num
The session is running and allocating memory. I use the "onmode -z sid" can not stop the session.
My Informix version is 14.10.FC10 run at Linux x86.
Philip Yeh.
Cobrasonic Software Inc.
Phillip:
First, this is a bug and you need to open a PMR with IBM. There is nothing we can do to help you with the running session except to recommend that you bounce the instance to get rid of the runaway session.
Next: the CTE "WITH
customer AS ( SELECT * FROM customer )" is completely unnecessary to this query. The remainder of the query will run just fine without it, returning the same data, and may actually run faster. Just run:
SELECT customer.customer_num , items.stock_num , items.order_num
, items.manu_code , items.item_num ,
items.quantity , items.total_price ,nvl(orders.backlog,'N') FROM customer , orders , items
WHERE orders.customer_num = customer.customer_num
;
Just because v14.10 supports CTEs doesn't mean that every query needs to have that feature included.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
As I saw this myself 2-3 weeks ago, I think you are hitting two bugs.
The first is that 'onmode -z' or terminating the client session does not kill the session on the server.
The second is that you cannot give the CTE the same name as a base table but this is not enforced. This can lead to a nasty loop where virtual memory is assigned to a thread without limit and ultimately you'd have to bounce the instance. The reference is I
T43642 and there is no fix in any public release but you may be able to request a special build from support.
If you change the name of the CTE to something random that isn't 'customer' the problem will go away.
Ben.
------------------------------
Benjamin Thompson
------------------------------