This very simple query fails against VDS which points to a JSON source
Attached query profile:
8327f4c7-7dcb-44b1-8ba0-92bb0c862eff.zip (43.4 KB)
I also ran essentially the same query against our production cluster (many more nodes) and is fails there too:
3b0ef014-5bc2-4701-8022-71e71397bb5b.zip (50.2 KB)
Hi @jhaynie
Can you please try to turn off “planner.experimental.pclean_logical” and retry the query. Click on Admin-Cluster (on the left)-Right Side support key box- ENter “planner.experimental.pclean_logical” and click show and hit save
Thanks
@balaji.ramaswamy
I turned if off and it still times out. here’s profile if that helps.
8c5bd497-0806-4ddf-833e-803ee3b27c82.zip (48.2 KB)
Since this is a calculated field from a nested JSON object, I tried making it simple (don’t convert to timestamp etc) and it seems to still fail.
instead of the vds, I went against the raw json source and it finishes but it’s super slow (37s). there aren’t many records in these JSON files
d2c1a4c1-936b-4ce5-a8e5-801b106132ce.zip (10.8 KB)
Seems like >1 field in where clause causes the problem.
If I used created_at by itself works fine. If I use another column by itself it works fine. If I use more than 1 column, times out.
Basically, after many hours of troubleshooting and workarounds, it appears to be related to nested S3 fields being referenced. No matter what I do, anything that is nested seems to not work when used as part of a virtual query. I worked around it in the short-term by flattening the JSON files (so nested becomes NestedA.B.C.D as the name of the key) and it now works.
Seems like there is some sort of problems with nested JSON data structures in queries.
Is this issue still there? I can unnest (flatten) json data structures without any issues. However, if I try to join the resulting table with any other table, I get an error… @balaji.ramaswamy is there such an issue with nested structures?
fcd6e598-1ddc-4fdc-9ce3-b6cfdc1588b8.zip (37.9 KB)