Currently getting namespace already existing error, while using recursive CTE query in Multi threaded environment.
Please let me know does CTE supports parallel processing or not.
Is there any way to specify CTE belongs to session/connection specific
Below are the details:
SingleStore version 8.1.32
allow_materialize_cte_with_union = TRUE
materialize_ctes = AUTO
I have a table that contains entire tree data id, parent_id and name, And the root node having parent_id values as zero.
My Requirement is based on input name i need flattened data till root
Like (requested_node —> Parent—>Grand Parent – — —>Root)
With the help of CTE Query I’m able get the required data.
Table: AccountTree
| Id | Parent_ID | Name |
|---|---|---|
| 101 | 0 | A |
| 105 | 101 | B |
| 106 | 105 | BA |
| 107 | 105 | BB |
| 108 | 106 | C |
| 109 | 108 | D |
| 110 | 107 | E |
If input name is C then my required Output is below
| Id | Parent_ID | Name |
|---|---|---|
| 101 | 0 | A |
| 105 | 101 | B |
| 106 | 105 | BA |
| 108 | 106 | C |
With the help of Recursive CTE query I’m able to get the above results.
QUERY:
With Recursive FlattenedTree as(
Select id, parent_id from AccountTree where name in (‘C’)
UNION ALL
Select t.id, t.parent_id from AccountTree t
JOIN FlattenedTree as ft on t.id = ft. parent_id
) Select distinct * from AccountTree at
Join FlattenedTree ft on at.id = ft.id
But whereas in Multi-Threaded Out of 100 instances(Threads) I’m getting the below error on 5 to 10 instances.
All there 100 are different connection Objects
ERROR:
SQLSTATE: 42S01
Leaf error (memsql url) Arrangement ‘iter_FlattenedTree_21’ already exists in namespace ‘msql_testenv_col’::’temp_1_244244_8’
I Tried with different CTE names as well like FlattenedTree_1,FlattenedTree_2 …FlattenedTree_100 different name for each connection in that case also getting the similar error
Let me know is there any to specify CTE belongs to the particular session/connection?
And also Please let me know weather the approach is correct or not for my use case.
Is there any better way to achieve this.