ParsingException is raised when parameterized query parameter is named "limit" or "offset"

[hdbin]

Using limit and/or offset as a parameter name in a parameterized query raises DB::ParsingException.

Following query:

client.execute("SELECT * FROM system.events LIMIT %(limit)s OFFSET %(offset)s", {"limit": 20, "offset": 30})

raises an exception:

DB::ParsingException. DB::ParsingException: Cannot parse quoted string: expected opening quote ''', got '2'.

Query executes without exception when anything else is used as a parameter names.

Versions

clickhouse-driver: 0.2.6
Clickhouse server: 23.4.2.11
Python: 3.8

[veotani]

Got the same error. The error is not in 0.2.5. Can give you more details if needed.

[xzkostyan]

I don't know on which side the problem is.

clickhouse-client throws the same error:

$ clickhouse-client  --param_limit=123
ClickHouse client version 23.4.2.11 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.4.2 revision 54462.

Warnings:
 * Table system.session_log is enabled. It's unreliable and may contain garbage. Do not use it for any kind of security monitoring.

 :) select 1;

SELECT 1

Query id: b652007e-d8cf-4e2e-92b2-493826c743c9


0 rows in set. Elapsed: 0.002 sec. 

Received exception from server (version 23.4.2):
Code: 26. DB::Exception: Received from localhost:9000. DB::ParsingException. DB::ParsingException: Cannot parse quoted string: expected opening quote ''', got '1'. (CANNOT_PARSE_QUOTED_STRING)

There are two ways to solve this: automatically quote parameter on client side or on server side. I'd suggest to wait some time. May be it will be fixed on server side.

Here is workaround to get 0.2.6 work without server-side parameters (client protocol downgrading):

from clickhouse_driver import Client, defines

client = Client('localhost', client_revision=defines.DBMS_MIN_PROTOCOL_VERSION_WITH_QUOTA_KEY)

client.execute("SELECT * FROM system.events LIMIT %(limit)s OFFSET %(offset)s", {"limit": 20, "offset": 30})

[dshein-alt]

Got the same issue after upgrade clickhouse-driver from 0.2.5 to 0.2.6 with SQL requests uses parameters.

Your solution with client_revision=defines.DBMS_MIN_PROTOCOL_VERSION_WITH_QUOTA_KEY works for me.

I've checked clickhouse issues but found no usable information there.

Currently we use ClickHouse server version 23.3.8 and it reports that protocol version is 54462.
Could you give any clue how this client_version setting could impact on inter-operation with server in future?

[shaojiankui]

same problem
{"code":500,"data":null,"msg":"Code: 26.\nDB::ParsingException. DB::ParsingException: Cannot parse quoted string: expected opening quote ''', got '1'. Stack trace:\n\n0. ./build_docker/./src/Common/Exception.cpp:96: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6d5d7b in /usr/lib/debug/usr/bin/clickhouse.debug\n1. ./contrib/llvm-project/libcxx/include/string:1499: DB::ParsingException::ParsingException<String, String>(int, FormatStringHelperImpl<std::type_identity::type, std::type_identity::type>, String&&, String&&) @ 0x000000000c741fc7 in /usr/lib/debug/usr/bin/clickhouse.debug\n2. ./build_docker/./src/IO/ReadHelpers.cpp:0: void DB::readQuotedStringInto<false, String>(String&, DB::ReadBuffer&) @ 0x000000000c72ff89 in /usr/lib/debug/usr/bin/clickhouse.debug\n3. ./src/Core/BaseSettings.h:824: DB::TCPHandler::receiveQuery() @ 0x000000001268ab4c in /usr/lib/debug/usr/bin/clickhouse.debug\n4. ./build_docker/./src/Server/TCPHandler.cpp:0: DB::TCPHandler::receivePacket() @ 0x0000000012680b7b in /usr/lib/debug/usr/bin/clickhouse.debug\n5. ./build_docker/./src/Server/TCPHandler.cpp:0: DB::TCPHandler::runImpl() @ 0x0000000012674905 in /usr/lib/debug/usr/bin/clickhouse.debug\n6. ./build_docker/./src/Server/TCPHandler.cpp:2294: DB::TCPHandler::run() @ 0x000000001268cd79 in /usr/lib/debug/usr/bin/clickhouse.debug\n7. ./build_docker/./base/poco/Net/src/TCPServerConnection.cpp:57: Poco::Net::TCPServerConnection::start() @ 0x00000000150f4e52 in /usr/lib/debug/usr/bin/clickhouse.debug\n8. ./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:48: Poco::Net::TCPServerDispatcher::run() @ 0x00000000150f5c51 in /usr/lib/debug/usr/bin/clickhouse.debug\n9. ./build_docker/./base/poco/Foundation/src/ThreadPool.cpp:202: Poco::PooledThread::run() @ 0x00000000151ece67 in /usr/lib/debug/usr/bin/clickhouse.debug\n10. ./base/poco/Foundation/include/Poco/SharedPtr.h:231: Poco::ThreadImpl::runnableEntry(void*) @ 0x00000000151eb45c in /usr/lib/debug/usr/bin/clickhouse.debug\n11. ? @ 0x00007fdf28a25ac3 in ?\n12. ? @ 0x00007fdf28ab7850 in ?\n"}

im using DBUtils
clickhouse_config = {
"host": host,
"port": port,
"user": user,
"password":password,
"database": db,
"client_revision" : defines.DBMS_MIN_PROTOCOL_VERSION_WITH_QUOTA_KEY
}
app.ckPool = PooledDB(dbapi, **clickhouse_config)
fix it

[xzkostyan]

@dshein-alt If you pin revision for a long time, which obviously not recommended, ClickHouse server will not accept connections from your code.

Both client (driver) and server has revision check and may refuse connection with remote side if it's considered too old. "Too old" here mean not supported anymore. It depends on ClickHouse server accept policy. AFAIK, it's pretty flexible and many old clients are supported.

You can try to connect with old clickhouse-client to test it.

[xzkostyan]

Version 0.2.7 is released. Now you can choose between client or server side templating.