LOGICAL.ANY([]).[]] could not be implemented error

1

Hi all

when I run the following query:

SELECT CID
FROM (
SELECT CID, flatten(ARRAY1) AS ARRAY1
FROM (
SELECT CID, json_imp_filtered.“json_struct”[‘ARRAY1’] AS ARRAY1
FROM json_imp_filtered) nested_0
) nested_1
WHERE nested_1.ARRAY1.DATE1 <> ‘’

I receive this error:

Node [rel#37941:Subset#63.LOGICAL.ANY([]).[]] could not be implemented; planner state:

Root: rel#37941:Subset#63.LOGICAL.ANY([]).[]
Original rel:
ProjectRel(subset=[rel#37941:Subset#63.LOGICAL.ANY([]).[]], CID=[$1]): rowcount = 7603.75, cumulative cost = {7603.75 rows, 0.07603750000000001 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 37940
FlattenRel(subset=[rel#37939:Subset#62.LOGICAL.ANY([]).[]], flattenField=[[$0]]): rowcount = 7603.75, cumulative cost = {7603.75 rows, 30415.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 37938
ProjectRel(subset=[rel#37937:Subset#61.LOGICAL.ANY([]).[]], expr000=[ITEM($1, ‘ARRAY1’)], expr001=[$0]): rowcount = 760.375, cumulative cost = {760.375 rows, 0.0152075 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 37936
FilterRel(subset=[rel#37935:Subset#60.LOGICAL.ANY([]).[]], condition=[AND($5, $6, $7)]): rowcount = 760.375, cumulative cost = {48664.0 rows, 745167.5 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 37934
JoinRel(subset=[rel#37932:Subset#59.LOGICAL.ANY([]).[]], condition=[AND(=($0, $8), =($2, $9))], joinType=[inner]): rowcount = 48664.0, cumulative cost = {81318.0 rows, 1948462.0 cpu, 32654.0 io, 32654.0 network, 431032.80000000005 memory}, id = 37931
ProjectRel(subset=[rel#37929:Subset#57.LOGICAL.ANY([]).[]], cid=[$0], json_struct=[$1], ts=[$2], $f3=[null], $f4=[null], $f5=[<>(ITEM(ITEM($1, ‘PUBBLICAZIONI’), ‘DATA_PUBBLICAZIONE’), ‘’)], $f6=[LIKE(ITEM(ITEM($1, ‘BANDO’), ‘CID_ACCORDO_QUADRO’), ‘’)], $f7=[<>(ITEM(FLATTEN(ITEM($1, ‘ARRAY1’)), ‘DATE1’), ‘’)]): rowcount = 48664.0, cumulative cost = {inf}, id = 37928
FilesystemScanDrel(subset=[rel#37927:Subset#56.LOGICAL.ANY([]).[]], table=["__accelerator".“b8df7a51-6435-4b3a-b01c-9c2956249775”.“a161f0f7-0b87-419f-add0-294b7cb44367”], columns=[cid, json_struct, ts], splits=[1]): rowcount = 48664.0, cumulative cost = {48664.0 rows, 1.3090616E7 cpu, 1.3090616E7 io, 1.3090616E7 network, 0.0 memory}, id = 37828
FilesystemScanDrel(subset=[rel#37930:Subset#58.LOGICAL.ANY([]).[]], table=["__accelerator".“d47dc32e-e720-4bec-802e-27f4f0c3e3fc”.“448f295a-de4f-444e-8f7a-69ac68199df8”], columns=[cid, max_ts], splits=[1]): rowcount = 16327.0, cumulative cost = {16327.0 rows, 32654.0 cpu, 32654.0 io, 32654.0 network, 0.0 memory}, id = 37831

Error doesn’t occur with preview.
I’m using Dremio Community 4.04, single node deployment.
I’m attaching the Profile.

Can someone help?

Thank you in advance 074fe434-1c57-4d7b-a67d-936390fd1bb1.zip (9.3 KB)

2

I had the same issue for a very similar type of query. I came up with a work-around that seems odd but it gets the job done.

Try running your query without the outer most select and where clause, i.e. just try running –

SELECT CID, flatten(ARRAY1) AS ARRAY1
FROM (
SELECT CID, json_imp_filtered.“json_struct”[‘ARRAY1’] AS ARRAY1
FROM json_imp_filtered) nested_0

If that runs OK, save it as a VDS.

Then create a second query that queries this VDS and re-adds your where clause –

SELECT CID FROM VDS WHERE DATE1 <> ‘’

This worked for me. Hopefully it works for you as well.

1 Like
3

@ZSponaugle your workaround worked for me, thank you!
Still curious to know what’s wrong with the original query which prevents Dremio from planning its execution

regards

4

I’m receiving this error with other virtual datasets now.
The workaround suggested by @ZSponaugle is ok for a few vds, but splitting the logic of all the vds would cost a huge amount of work.
Can someone help me understand what’s happening?