bug with array query params

[zerlok]

Describe the bug

can't pass array parameters to clickhouse driver ordinary statements

To Reproduce

Setup

# setup client, connect to CH
client = Client(...)

# create  users table
client.execute("""
CREATE TABLE IF NOT EXISTS users (
    `id` Int32,
    `name` String
) ENGINE = MergeTree()
	PRIMARY KEY (id)
""")

# insert users test data
client.execute(
   query="""INSERT INTO users (`id`, `name`) VALUES""",
   params=[
      {'id': 0, 'name': 'user-name-000'},
      {'id': 1, 'name': 'user-name-001'},
      {'id': 2, 'name': 'user-name-002'},
      {'id': 3, 'name': 'user-name-003'},
   ],
)

Then execute a valid CH SQL with parameters:

SELECT * FROM users WHERE id IN {ids:Array(Int32)} ORDER BY id

print(client.execute(
   query="""SELECT * FROM users WHERE id IN {ids:Array(Int32)} ORDER BY id""",
   params={"ids": [2, 3]},
))

Throws the exception:

DB::ParsingException: Cannot parse input: expected ']' at end of stream.: value [ cannot be parsed as Array(Int32) for query parameter 'ids'

Traceback (without private files):

.venv/lib/python3.9/site-packages/clickhouse_driver/client.py:373: in execute
    rv = self.process_ordinary_query(
.venv/lib/python3.9/site-packages/clickhouse_driver/client.py:571: in process_ordinary_query
    return self.receive_result(with_column_types=with_column_types,
.venv/lib/python3.9/site-packages/clickhouse_driver/client.py:204: in receive_result
    return result.get_result()
.venv/lib/python3.9/site-packages/clickhouse_driver/result.py:50: in get_result
    for packet in self.packet_generator:
.venv/lib/python3.9/site-packages/clickhouse_driver/client.py:220: in packet_generator
    packet = self.receive_packet()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <clickhouse_driver.client.Client object at 0x7f3511d1b970>

    def receive_packet(self):
        packet = self.connection.receive_packet()
    
        if packet.type == ServerPacketTypes.EXCEPTION:
>           raise packet.exception
E           clickhouse_driver.errors.ServerException: Code: 27.
E           DB::ParsingException. DB::ParsingException: Cannot parse input: expected ']' at end of stream.: value [ cannot be parsed as Array(Int32) for query parameter 'ids'. Stack trace:
E           
E           0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe3b7135 in /usr/bin/clickhouse
E           1. ? @ 0xe416ae4 in /usr/bin/clickhouse
E           2. DB::throwAtAssertionFailed(char const*, DB::ReadBuffer&) @ 0xe4169e1 in /usr/bin/clickhouse
E           3. DB::SerializationArray::deserializeText(DB::IColumn&, DB::ReadBuffer&, DB::FormatSettings const&, bool) const @ 0x12dee6b8 in /usr/bin/clickhouse
E           4. DB::ReplaceQueryParameterVisitor::visitQueryParameter(std::shared_ptr<DB::IAST>&) @ 0x13eea1db in /usr/bin/clickhouse
E           5. DB::ReplaceQueryParameterVisitor::visitChildren(std::shared_ptr<DB::IAST>&) @ 0x13eed8f8 in /usr/bin/clickhouse
E           6. DB::ReplaceQueryParameterVisitor::visitChildren(std::shared_ptr<DB::IAST>&) @ 0x13eed8f8 in /usr/bin/clickhouse
E           7. DB::ReplaceQueryParameterVisitor::visitChildren(std::shared_ptr<DB::IAST>&) @ 0x13eed8f8 in /usr/bin/clickhouse
E           8. DB::ReplaceQueryParameterVisitor::visitChildren(std::shared_ptr<DB::IAST>&) @ 0x13eed8f8 in /usr/bin/clickhouse
E           9. DB::ReplaceQueryParameterVisitor::visitChildren(std::shared_ptr<DB::IAST>&) @ 0x13eed8f8 in /usr/bin/clickhouse
E           10. ? @ 0x13efc3e6 in /usr/bin/clickhouse
E           11. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x13efbc4d in /usr/bin/clickhouse
E           12. DB::TCPHandler::runImpl() @ 0x14cd2771 in /usr/bin/clickhouse
E           13. DB::TCPHandler::run() @ 0x14ce8279 in /usr/bin/clickhouse
E           14. Poco::Net::TCPServerConnection::start() @ 0x17c5ac34 in /usr/bin/clickhouse
E           15. Poco::Net::TCPServerDispatcher::run() @ 0x17c5be5b in /usr/bin/clickhouse
E           16. Poco::PooledThread::run() @ 0x17dd9207 in /usr/bin/clickhouse
E           17. Poco::ThreadImpl::runnableEntry(void*) @ 0x17dd6c3d in /usr/bin/clickhouse
E           18. ? @ 0x7f0cde78a609 in ?
E           19. __clone @ 0x7f0cde6af133 in ?

.venv/lib/python3.9/site-packages/clickhouse_driver/client.py:237: ServerException

Expected behavior

According to clickhouse doc, passed ids value should be casted to Array(Int32) on server side and query should be executed successfully with the following console output:

[(2, "user-name-002"), (3, "user-name-003")]

Versions

• python: 3.9
• clickhouse-driver version: 0.2.6
• clickhouse server version (docker image): clickhouse/clickhouse-server:23.4.3.48-alpine

Notes

Simple int param works perfectly:

SELECT * FROM users WHERE id = {id:Int32}

print(client.execute("""SELECT * FROM users WHERE id = {id:Int32}""", params={'id': 7}"""))

Returns

[(7, "user-name-007")]

UPD: just found that clickhouse driver connection class performs parameter escaping and results in the following (added print statement between 708 and 709 lines this module

params={'ids': [2, 3]}; escaped={'ids': "'['2', '3']'"}

I guess the valid escaped value should be

params={'ids': [2, 3]}; escaped={'ids': "[2, 3]"}