Hi AskTom team.

I was playing with recursive subqueries and tried to do some string manipulations, but I noticed something strange:

SQL> WITH r(str, outtxt, outtxt2) AS (
  2    SELECT 'aabbba', '', '' FROM   dual
  3    UNION ALL
  4    SELECT substr(str, 2)
  5          ,CASE WHEN substr(str, 1, 1) = substr(outtxt, -1)
  6                THEN (1 + substr(outtxt, -2, 1)) || substr(str, 1, 1)
  7                ELSE '1' || substr(str, 1, 1)
  8           END
  9          ,CASE WHEN substr(str, 1, 1) = substr(outtxt, -1)
 10                THEN (1 + substr(outtxt, -2, 1)) || substr(str, 1, 1)
 11                ELSE '1' || substr(str, 1, 1)
 12           END
 13    FROM   r
 14    WHERE  str IS NOT NULL
 15  )
 16  SELECT * FROM r;

STR      OUTTXT   OUTTXT2
-------- -------- --------
aabbba
abbba    1a       1a
bbba     1a       2a
bba      1b       1b
ba       1b       2b
a        1b       2b
         1a       1a

7 rows selected.

You see, the expressions for outtxt and outtxt2 are identical, yet they return different results (e.g. 1a, 2a).
Should I consider it a bug? Or is there something I don't understand about recursive subqueries?

Thanks

WITH r(str, outtxt, outtxt2) AS (
  SELECT 'aabbba', 'a1', '' FROM   dual
  UNION ALL
  SELECT substr(str, 2)
        ,CASE WHEN substr(str, 1, 1) = substr(outtxt, -1)
              THEN (1 + substr(outtxt, -2, 1)) || substr(str, 1, 1)
              ELSE '1' || substr(str, 1, 1)
         END
        ,CASE WHEN substr(str, 1, 1) = substr(outtxt, -1)
              THEN (1 + substr(outtxt, -2, 1)) || substr(str, 1, 1)
              ELSE '1' || substr(str, 1, 1)
         END
  FROM   r
  WHERE  str IS NOT NULL
)
SELECT * FROM r ;