Connect by in cross apply

I have the below code:

SELECT
    m, l
FROM
    (
        SELECT
            2 * level + 3 l
        FROM
            dual
        CONNECT BY
            2 * level + 3 <= 1000
    ) CROSS APPLY (
        SELECT
            2 * level + 1 m
        FROM
            dual
        CONNECT BY
            power(2 * level + 1, 2) <= l
    ) order by m, l

However, the first results shown are the below. Why is the 2nd power of m connecting with l in that case if power(3, 2) is not <= 5 (or 7)?

m   l
3   5
3   7

Answer

Because CONNECT BY checks whether the query will recurse after the first row is generated so you will always get at least one row (where LEVEL is 1); if you want to limit the number of rows then you need a START WITH or WHERE clause.

You can see the difference if you also include either clause and OUTER APPLY to return the l rows when m then has no generated rows (and would be NULL):

SELECT m, l
FROM   ( SELECT 2 * level + 3 AS l
         FROM   dual
         CONNECT BY 2 * level + 3 <= 25 )
       OUTER APPLY
       ( SELECT 2 * level + 1 AS m
         FROM   dual
         START WITH 9 <= l
         CONNECT BY power(2 * level + 1, 2) <= l )
order by l, m

or

SELECT m, l
FROM   ( SELECT 2 * level + 3 AS l
         FROM   dual
         CONNECT BY 2 * level + 3 <= 25 )
       OUTER APPLY
       ( SELECT 2 * level + 1 AS m
         FROM   dual
         WHERE  power(2 * level + 1, 2) <= l
         CONNECT BY power(2 * level + 1, 2) <= l )
order by l, m

or Outputs:

M	L
	5
	7
3	9
3	11
3	13
3	15
3	17
3	19
3	21
3	23
3	25
5	25

db<>fiddle here