Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.1.2
-
None
-
None
-
Win64
Description
Sample query
WITH a AS (SELECT 5 mx) |
SELECT column_value FROM a,
|
(
|
WITH recursive r as
|
(SELECT 1 level |
union all
|
select LEVEL+1 FROM r |
)
|
select LEVEL column_value FROM r
|
) i
|
WHERE i.column_value<=a.mx
|
runs OK, but on insert or temporary table create:
CREATE OR REPLACE TEMPORARY TABLE tmp0 as
|
WITH a AS (SELECT 5 mx) |
SELECT column_value FROM a,
|
(
|
WITH recursive r as
|
(SELECT 1 level |
union all
|
select LEVEL+1 FROM r |
)
|
select LEVEL column_value FROM r
|
) i
|
WHERE i.column_value<=a.mx
|
it pops with error/warning "Query execution was interrupted. The query exceeded max_recursive_iterations = 100000. The query result may be incomplete".
I'd like to lose that unreasonable warning, just like it goes silent in select action. How can I do it?
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Component/s | Optimizer - CTE [ 13513 ] |
| Priority | Blocker [ 1 ] | Major [ 3 ] |
I'm not as so confident as you are to state what does other person really want. But yes, I want both things. First - that insert /CTAS would work as fast as select does. Second - that warning wouldn't appear.
you don't really want to disable the warning, because it says that in the second case CTE generates 100,000 rows (and hits the limit), while in the first case it — I suppose — generates only 5, as requested. What you want is the second CTE to generate only 5 rows and not to waste time going all the way to 100,000.