Clickhouse compatibility

[alanpaulkwan]

What happens?

 con=dbConnect(duckdb())
> dbGetQuery(con,"ATTACH 'host=madeuphostname user=default 
+            password=madeuppassword port=9004 database=comp' AS comp (TYPE mysql_scanner)")
Warning in dbFetch(rs, n = n, ...) :
  Should not call dbFetch() on results that do not come from SELECT, got ATTACH
data frame with 0 columns and 0 rows
> dbGetQuery(con,"use comp")
Error: rapi_execute: Failed to run query
Error: IO Error: Failed to run query "START TRANSACTION": Code: 62. DB::Exception: Syntax error: failed at position 1 ('START'): START TRANSACTION. Expected one of: Query, Query with output, EXPLAIN, SELECT query, possibly with UNION, list of union elements, SELECT query, subquery, possibly with UNION, SELECT subquery, SELECT query, WITH, FROM, SELECT, SHOW CREATE QUOTA query, SHOW CREATE, SHOW [FULL] [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER|MERGES 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], SHOW, SHOW COLUMNS query, SHOW ENGINES query, SHOW ENGINES, SHOW FUN
Warning: Connection is garbage-collected, use dbDisconnect() to avoid this.

To Reproduce

Create a clickhouse instance and try to connect.

# on a mac 
curl https://clickhouse.com | sh 
./clickhouse server

require(duckdb)
con=dbConnect(duckdb())
dbGetQuery(con,"ATTACH 'host=127.0.0.1 user=default   port=9004 database=default' AS def(TYPE mysql_scanner)")
dbGetQuery(con,'use def')

Error: rapi_execute: Failed to run query
Error: IO Error: Failed to run query "START TRANSACTION": Code: 62. DB::Exception: Syntax error: failed at position 1 ('START'): START TRANSACTION. Expected one of: Query, Query with output, EXPLAIN, SELECT query, possibly with UNION, list of union elements, SELECT query, subquery, possibly with UNION, SELECT subquery, SELECT query, WITH, FROM, SELECT, SHOW CREATE QUOTA query, SHOW CREATE, SHOW [FULL] [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER|MERGES 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], SHOW, SHOW COLUMNS query, SHOW ENGINES query, SHOW ENGINES, SHOW FUN

OS:

Ubuntu

MySQL Version:

Clickhouse MySQL Protocol

DuckDB Version:

9.2

DuckDB Client:

R

Full Name:

Alan Kwan

Affiliation:

HKU

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[driv]

I'm having the same issue, is it possible to disable transactions when connecting to MYSQL?