Postgres Pro
Downloads
Home   >   mailing lists

Puzzline CROSS JOIN when doing RECURSIVE CTE - Mailing list pgsql-general

From Pól Ua Laoínecháin
Subject Puzzline CROSS JOIN when doing RECURSIVE CTE
Date
Msg-id CAF4RT5T4Y69=Dd1sUYqePV-mx0QYeRfL3dCQ1UBy5oUebUr+sw@mail.gmail.com
Whole thread Raw
Responses Re: Puzzline CROSS JOIN when doing RECURSIVE CTE  (Alban Hertroys <haramrae@gmail.com>)
List pgsql-general
Tree view 
Hi all,

I've been working on a recursive query (I've already written a few, so
 I'm not a complete newbie..

All  of the code below is available on the fiddle here:

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=0cc20c9081867131260e6e3550bd08ab


I have a table called line

SELECT
  idx, length, string ~ 'html', string
FROM
  line;


Result:

idxlength?column?string1 257 f with t(x) as (values( XMLPARSE(DOCUMENT
('<root><NotificationServiceDetails NotificationNo="0"
AlarmCode="mail" AlarmStartTime="10:00:00" AlarmTime="0" Id ="2"
><NotificationServiceDetail
Id="2"><Title><![CDATA[aaaaaaaaaaaaa]]></Title><ContentJson><![CDATA[
2 22 t <html lang="en">
3 12 f <head>
4 33 f <meta charset="utf-8"/>
5 20 f more stuff
6 20 f more stuff
716f </table>
...
... snipped for brevity
...

16 rows

OK, grand, now I wish to perform a RECURSIVE CTE on it. So, I start by
trying something (I thought was) very simple. Obviously, I plan to do
more, but I wanted to get the "mechanics" correct to start with. So,
my query is:

WITH RECURSIVE cte1 (n, ln) AS
(
  SELECT 1 AS n, string
  FROM line
  UNION ALL
  SELECT n + 1, ln
  FROM cte1
  WHERE n < (SELECT COUNT(*) FROM line)
)
SELECT * FROM cte1;

i.e. have a counter variable and a string from the line table


But, then to my horror, the result of this query is

1with t(x) as (values( XMLPARSE(DOCUMENT
('<root><NotificationServiceDetails NotificationNo="0"
AlarmCode="mail" AlarmStartTime="10:00:00" AlarmTime="0" Id ="2"
><NotificationServiceDetail
Id="2"><Title><![CDATA[aaaaaaaaaaaaa]]></Title><ContentJson><![CDATA[
1 <html lang="en">
1 <head>
1 <meta charset="utf-8"/>
1 more stuff
1 more stuff
1 </table>
1 </body>
1 </html>
...
... snipped for brevity
...

256 rows!        <<=== note 256!


So, my simple recursive CTE is

a) not incrementing n and
b) obviously doing some sort of CROSS JOIN (16^2 = 256).


I would be grateful if somebody could explain what's going on here. As
shown in the fiddle, I can do the basic 1 - 10 RCTE and I've done way
more complex ones before, so I'm a bit baffled as to what's going on
here.

Any explanations, references to URLs or other advice gratefully received.

TIA and rgs,

Here's the fiddle again if you wish to take a look

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=0cc20c9081867131260e6e3550bd08ab

Pól...

pgsql-general by date:

Previous
From: Ajay Kajla
Date:
Subject: Re: Facing issues with pgsql upgrade.
Next
From: Jayadevan M
Date:
Subject: Metadata and data lineage tool