Map type not working on OSS vs Enterprise

[dsztykman]

What happened:

Querying map using Clickhouse plugin v3.3.0 fails with the latest docker image of Grafana OSS:
Status: 500. Message: sql: Scan error on column index 8, name "botData": unsupported Scan, storing driver.Value type map[string]*string into type *string: Could not process SQL results
Using the following Grafana:
docker run -d --name=grafana -p 3443:3000 grafana/grafana-oss:10.2.2-ubuntu

What you expected to happen:
Able to query datatype map(string,string).
Trying with docker run -d --name=grafana -p 3443:3000 grafana/grafana-enterprise:10.2.2-ubuntu using the same clickhouse plugin v3.3.0 and the same datasource works.

How to reproduce it (as minimally and precisely as possible):

Launch Grafana OSS with Docker, install clickhouse plugin 3.3.0, try to query a map(string, string) type.

Environment:

• Grafana version: grafana-oss:10.2.2-ubuntu
• Plugin version: v3.3.0
• OS Grafana is installed on: docker
• User OS & Browser: unrelated
• Others: works well with Grafana Enterprise

[adamyeats]

@dsztykman Thanks for flagging this! At the moment, I'm unable to reproduce the issue locally with the grafana-oss:10.2.2-ubuntu Docker image and Grafana main branch, both querying the fields of a map and the table itself (the screenshot below shows me selecting a key from a map using the SQL Editor).

Are you able to provide a sample of the dataset where you are seeing the issue occur? Are you using the Query Builder or SQL Editor to form the query?

[dsztykman]

Technically it's happening when I'm using the log panel and I do an ugly query like:
SELECT * FROM mytable LIMIT 10 to display example of "logs".

I have been playing a little bit and I have examples:
This works:

SELECT botData['botScore'] as score FROM mytable WHERE timestamp < now() and timestamp > now() - INTERVAL 10 MINUTE AND score = '10' LIMIT 100

But this doesn't:

SELECT botData FROM mytable WHERE timestamp < now() and timestamp > now() - INTERVAL 10 MINUTE LIMIT 100

I'm guessing if you try SELECT a FROM table_map; it'll fail on OSS but works on enterprise.

[adamyeats]

@dsztykman Hmmm, this doesn't seem to be reproducible for me in the logs view either, on my local version based on the main branch or Docker images of OSS 10.2.2 or Enterprise 10.2.2.

A sample dataset would help here if possible, as the exception seems to be thrown by the database driver, so might it be something that the driver, or the abstraction layer we have around it, is parsing incorrectly. It could also be me missing something here, so if you have any other helpful context, please feel free to add it.

[alyssabull]

Hey @dsztykman - is this still an issue for you? If so, can you provide us with some information (see above comment) as we haven't been able to reproduce it on our side. Thanks!

[aangelisc]

Closing this due to inactivity, please feel free to re-open if this is still an issue 😊

[dsztykman]

Sorry for my lack of response, it is indeed still an issue.

grafana-5b46cbf846-zs7g2 logger=datasources t=2024-04-04T18:56:24.827593858Z level=debug msg="Querying for data source via SQL store" uid=a6835544-2bfe-4f3a-98da-524301ae2280 orgId=2
grafana-5b46cbf846-zs7g2 logger=query_data t=2024-04-04T18:56:24.828285307Z level=debug msg="Processing metrics query" query="unsupported value type"
grafana-5b46cbf846-zs7g2 logger=query_data t=2024-04-04T18:56:24.832854513Z level=debug msg="Processing metrics query" query="unsupported value type"
grafana-5b46cbf846-zs7g2 logger=secrets.kvstore t=2024-04-04T18:56:24.833698127Z level=debug msg="got secret value" orgId=2 type=datasource namespace="demo cluster"
grafana-5b46cbf846-zs7g2 logger=secrets.kvstore t=2024-04-04T18:56:24.833746668Z level=debug msg="got secret value" orgId=2 type=datasource namespace="demo cluster"
grafana-5b46cbf846-zs7g2 logger=plugin.grafana-clickhouse-datasource t=2024-04-04T18:56:24.972726311Z level=error msg="sql: Scan error on column index 3, name \"leaf_cert_all_domains\": unsupport
ed Scan, storing driver.Value type []*string into type *string: Could not process SQL results"
grafana-5b46cbf846-zs7g2 logger=context userId=2 orgId=2 uname=xxxxxxx t=2024-04-04T18:56:24.97344002Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote
_addr=10.2.0.1 time_ms=149 duration=149.282978ms size=242 referer="https://dashboards.trafficpeak.live/d/c13cbad3-20b7-42ba-afbd-67a70be92e46/certificate-transparency?from=now-5m&orgId=2&refresh=
30s&to=now" handler=/api/ds/query status_source=downstream

We are using Grafana in HA via k8s and we are using the following env variable to load it:

- name: GF_INSTALL_PLUGINS
  value: https://github.com/grafana/clickhouse-datasource/releases/download/v4.0.4/grafana-clickhouse-datasource-4.0.4.zip;clickhouse-datasource

I can ping you a set of credentials to our deployment if you want to try to reproduce it?

[dsztykman]

@aangelisc it feels like very similar to this issue:
#783

[aangelisc]

Reopening this, I'll share with our current triage to review 😊

[alyssabull]

Hi @dsztykman - I cannot replicate this either. If you could share a set of credentials and the dataset you're using so we can further investigate that would be great. Thanks!

[SpencerTorres]

Wanted to chime in and ask/suggest a few things:

Questions:

• This was originally reported with plugin v3.x but it seems like it's still an issue with 4.x? (according to your GF_INSTALL_PLUGINS env var this seems to be the case)
• Is this an issue with Grafana, the plugin, or the Go driver the plugin backend uses? It seems like this might be a driver level issue.
• The query_type (Table, Logs, Time Series, Trace) does apply some mutations to the data, but only in the case of Trace.
• Lastly, and most important can you provide some DDL as well as a few INSERT rows to replicate this as minimally as possible? I see you provided some example queries but if you could also include some queries with the test DDL that would be helpful. Without the data there's no way to step through the error.

I am suspicious with how this error is appearing based on OSS/Enterprise versions. To my knowledge there shouldn't be any differences at that level of the application.

Hope to find the solution to this soon. Thank you for submitting this issue and providing an update recently

[dsztykman]

Yes it is still happening right now, the only solution we have is to cast into string so instead of selecting the map we actually do something like:

SELECT toString(requestHeaders) as RequestHeaders EXCEPT(requestHeaders)

We are using the query_type Logs as we want users to see the requestHeaders.
ddl_dump.zip
You can find the zip file which contains some rows and the DDL.

The "weird" thing is it doesn't happens all the time, for example we deploy grafana in HA and the query works for one of the container but not the other.

[dsztykman]

by the way we changed our schema to avoid that issue so on the dump the botData is not a map anymore but the requestHeaders still is and generate problem

[SpencerTorres]

Thanks for the info! I have loaded the table+data into my local ClickHouse server.
I've run a few different queries, SELECT *, SELECT requestHeaders, SELECT requestHeaders['Host'], etc. and none of them have errored yet. This is on OSS v10.3.3 (although I will try with the originally reported versions too).

The "weird" thing is it doesn't happens all the time, for example we deploy grafana in HA and the query works for one of the container but not the other.

Not sure what the query was in this case, but assuming Grafana+plugin are on the same version this could be something to do with the time ranges applied on the queries perhaps? For example, the first query has a different set of results from the second query.

I will try simplifying the table to just one column. I also want to note that the original issue said Map(String, String) but the DDL has Map(String, Nullable(String)).

Could you provide some more example queries, and confirm your most recently used Grafana + plugin version?

Thank you for your patience, this is issue is difficult to reproduce.

[dsztykman]

No worries I know it's difficult to reproduce because it doesn't happen on all our instances either...
This is the dashboard we use:
Akamai SIEM-1712941876761.json
You can try to start and stop grafana service and restart to see if it happens on it

[SpencerTorres]

Thanks! I loaded the dashboard and it works well with your sample data. I also added a few rows where Map(String, Nullable(String) would have a NULL map value.

Unfortunately, still no errors...

But I have reviewed the error logs you've pasted:

Scan error on column index 8, name "botData": unsupported Scan, storing driver.Value type map[string]*string into type *string: Could not process SQL results

Scan error on column index 3, name \"leaf_cert_all_domains\": unsupported Scan, storing driver.Value type []*string into type *string: Could not process SQL results"

It seems like this error could be happening with multiple types? Also, if you have a recent occurence of this error, you could find the original query in the system.query_log table and perhaps provide that information. That will show what the exact query was that triggered the error. Along with the DDL and time information this should be good information for reproducing the error, at least at the driver level.

[dsztykman]

This is the query that ran and failed:

SELECT ruleActions, * EXCEPT(ruleActions) FROM akamai.siem WHERE timestamp >= toDateTime(1713165122) AND timestamp <= toDateTime(1713168722) AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND if ('' = '', true, mapContainsKeyLike(requestHeaders, '%%')) AND if ('' = '', true, mapContainsKeyLike(responseHeaders, '%%')) LIMIT 100

[dsztykman]

Interestingly this error is on ruleActions which is an array of string...

[dsztykman]

@SpencerTorres I have upgraded the plugin to 4.0.6 which includes #783 and it fixes my issue.
I'm going to close this