[Enhancement] in SQL query, treat -- inside quotes not as comment

[delphid]

behavior now

--within quotes is also treated as comment. This brings unexpected truncation of query.

• This happens when store Pytest case results in Clickhouse and then query. In Pytest, when parametrize cases with sets of values [('name1', 'id1', 'score1'), ('name2', 'id2', 'score2'), ('name3', '', 'score3')], case name will become like name1-id1-score1. Say if id is omitted for the 3rd case, case name then becomes name3--score3, then in query:
  • when query: SELECT * FROM my_table WHERE case='name3--score3'
  • then generated SQL: SELECT * FROM my_db.my_table WHERE case='name3

desired behavior

• when query: SELECT * FROM my_table WHERE case='name3--score3'-- some comment
• then generated SQL: SELECT * FROM my_db.my_table WHERE case='name3--score3'

proposed change

https://github.com/delphid/clickhouse-grafana/blob/commentRe/src/scanner.ts#L304

demo

const comment_reg = '--(([^\"\']*[\"\']){2})*[^\"\']*[$\n]';
const query = 'query content"--actually not comment" \'--also not comment\'--some comment"comment in quotes" \'also comment\'\na new line of query';


console.log(
    query.replace(new RegExp(comment_reg, 'g'), '')
);


// result: query content"--actually not comment" '--also not comment'a new line of query

---

If that seems alright I'd like to submit a PR

[Slach]

thanks a lot for reporting
could you make PR and add new test case into spec/scanner_specs.jest.ts
use docker-compose run --rm frontend_builder
to run tests

[Slach]

Also, could you provide, SQL query example when you need -- as non comment?

[delphid]

Simplified use case: see the behavior now part in Pytest related contents.

My real use case looks like this:

select timestamp, suite, case, duration, result, log, failure from my_db.my_table
where
  timestamp >= $from and timestamp <= $to
  and business in ($business)
  and system in ($system)
  and suite in ($suite)
  and case in ($case)
  and result in ($result)
  and env='$env'
order by timestamp desc
limit $limit

It happens in the and case in ($case) part. When ($case) is something like ('aaa-5-mtdnn_xxx', 'bbb-10-bert_xxx', 'ccc--bert_yyy')

It's a Pytest feature to create name for parametrized case by connecting parameter values with -. When one parameter is omitted, will have '-' + '' + '-' -> '--'

cases are like:

tokens	branch_id	model_tag
aaa	5	mtdnn_xxx
bbb	10	bert_xxx
ccc		bert_yyy

Thanks. I'll fill some test cases and run

[delphid]

Just added test case and used docker-compose run --rm frontend_builder to run tests to pass. PR is as follows:
https://github.com/Vertamedia/clickhouse-grafana/pull/375/files

[jonny-gg]

What can be published for this special character? It seems that the clickHouse plugin has not been published for a long time