Impossibility to provide "Custom SQL" for Crate.io source

[Delicate-aRt]

While configure Crate.io data source using ZoomData dialog, there is no option to specify "Custom SQL".

Steps to reproduce:

1. Create Connector server (according to video)
2. Create Connection type (according to video)
3. Configure data source in ZoomData
4. On "Tables" tab - there is no option "Custom SQL"

Custom SQL option available for other source (e.g. Impala)

[bowenwr]

@Delicate-aRt suggest we close this issue for now. CrateDB has basically no support of subqueries. Although more is coming in 0.56, I don't think it will be enough for Zoomdata to consider this feature supported.

[jodok]

@Delicate-aRt and @bowenwr thanks for the feedback. right now we support only SUBSELECTs that can be solved on parser level -> meaning basically being rewritten / appended to the WHERE clause. that might be sufficient for some of the CUSTOM SQL cases. full SUBSELECT support is very high on our roadmap. we'll update this issue once we start working and finish them :)

[bowenwr]

@jodok Thanks for the update! It would be great to be able to provide custom SQL in this example once Crate has adequate support.

[bowenwr]

@jodok After the upgrade, I ran some tests and all the integration tests pass for custom SQL except for those involving both LIMIT and ORDER BY. A query such as the following is ordering randomly for each request and not deterministically:

select ds.string_full as string_full
from (select * from integration_tests.connector_test) as ds
where not (ds.string_full = 'Accessories Computers' or ds.string_full is null)
group by string_full
order by string_full asc
limit 5

I'm seeing the 5 records in a different order each time. This is on Crate 1.0.1.

[bowenwr]

Per https://github.com/crate/crate/issues/4673 this should be revisited once 1.0.3 is released.

[bowenwr]

This has been completed since Crate 1.0.3 has been out.