Query Builder: Column names containing parentheses not handled correctly

[mbUSC]

(cross-posting from 103501 in the Grafana repo)

What happened?

If a column name contains parentheses, the query builder fails to generate the correct SQL statements.

For example, let's say we have a Clickhouse data source that has a table with the following columns:

• Track ID
• Length (Kilometers)

When selecting those two columns, the query builder generates this SQL:

SELECT "Track ID", Length (Kilometers) FROM "mydatabase" . "TracksTable" LIMIT 1000

What did you expect to happen?

The correct SQL should be generated (i.e. Length (Kilometers) should be enclosed in double quotes):

SELECT "Track ID", "Length (Kilometers)" FROM "mydatabase" . "TracksTable" LIMIT 1000

Did this work before?

Not sure

How do we reproduce it?

See example provided above

Is the bug inside a dashboard panel?

No response

Environment (with versions)?

Grafana: 11.6.0
OS: MacOS
Browser: Safari

Grafana platform?

Kubernetes

Datasource(s)?

Clickhouse Datasource plugin

[mbUSC]

@SpencerTorres

Looks like this is the offending code:
https://github.com/grafana/clickhouse-datasource/blob/00710672ed9e97eb59070b7e4131fa26ea1bea31/src/data/sqlGenerator.ts#L539C1-L555C1

const getColumnIdentifier = (col: SelectedColumn): string => {
  let colName = col.name;


  // allow for functions like count()
  if (colName.includes('(') || colName.includes(')') || colName.includes('"') || colName.includes('"') || colName.includes(' as ')) {
    colName = col.name
  } else if (colName.includes(' ')) {
    colName = escapeIdentifier(col.name);
  }


  if (col.alias && (col.alias !== col.name && escapeIdentifier(col.alias) !== colName)) {
    return `${colName} as "${col.alias}"`
  }


  return colName;
}

We should be able to logically separate out the function from the column name, since they are two different things

[SpencerTorres]

Hello! Yep that's the exact code that came to mind when I read the title.

We should be able to logically separate out the function from the column name, since they are two different things

There's many edge cases to consider. I think in this case the best workaround is to manually add the quotes when selecting the column. There's too many cases where users are trying to write functions and they get escaped with quotes. You should be able to manually type in the column name with quotes. I understand this is inconvenient. If you have any suggestions on how we can best accommodate both, I'm open to ideas and PRs.

Thanks!

[mbUSC]

Are there any places where the user can actually type the function names, other than the SQL editor itself?

All I see in the Query Builder is drop-down menus:

I am trying to think what feature would fail if all column names were escaped

[mbUSC]

@SpencerTorres Now that I've used this a bit more, I get it -- you can type directly in "Columns".
I see where the problem would be.

[SpencerTorres]

Yeah some people use this ability to wrap columns in functions, so then we get the opposite problem where your function is wrapped in quotes. For example toDateTime(MyTimestamp) would be "toDateTime(MyTimestamp)", which wouldn't give the right result.

We can consider adding some logic to check whether the selected column name matches one within the table, then we can add quotes... but considering there's already an understood workaround/compromise for this issue, I think we can close this for now. Let me know if you have any more actionable suggestions and we can reopen. Thanks!