singhpratech opened a new issue, #67301: URL: https://github.com/apache/doris/issues/67301
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. (#49340 is a JDBC point-query prepared-statement report and does not cover parameter types.) ### Version - `apache/doris:all-in-one-4.1.3` — `@@version_comment` = `doris version doris-4.1.3-rc02-7126cf65d96` - `apache/doris:doris-all-in-one-2.1.0` — `doris version doris-2.1.0-rc11-91efb6a43d` - Client: MySQL Connector/ODBC 9.4.0 (`libmyodbc9w.so`, Linux x64, unixODBC 2.3.12), server-side prepared statements on (no `NO_SSPS=1`) ### What's Wrong? With server-side prepared statements, a prepared `INSERT INTO t (id, s) VALUES (?, ?)` fails whenever the string parameter is bound from a wide C type. MySQL Connector/ODBC sends every `SQL_C_WCHAR` parameter — the binding a Unicode ODBC client uses for strings — with MySQL type `BLOB` in `COM_STMT_EXECUTE`. The same statement with the same string bound as `SQL_C_CHAR` succeeds. - Doris 4.1.3: `AnalysisException, msg: Unsupported MySQL type: BLOB (1105)` - Doris 2.1.0: `NullPointerException, msg: null (1105)`, with this in `fe.log`: ``` java.lang.NullPointerException: null at org.apache.doris.qe.MysqlConnectProcessor.handleExecute(MysqlConnectProcessor.java:137) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.MysqlConnectProcessor.dispatch(MysqlConnectProcessor.java:208) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.qe.MysqlConnectProcessor.processOnce(MysqlConnectProcessor.java:258) ~[doris-fe.jar:1.2-SNAPSHOT] at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT] ``` MySQL itself accepts `MYSQL_TYPE_BLOB` parameters for character columns (it is how libmysqlclient sends long and wide strings), so a client that works against MySQL cannot `INSERT` through Doris' prepared-statement path unless it turns server-side prepare off (`NO_SSPS=1` in Connector/ODBC), which is what I do now. Output of the program below, verbatim (4.1.3 first, then 2.1.0): ``` driver libmyodbc9w.so 09.04.0000, server MySQL 5.7.99 SQL_C_WCHAR -> SQL_WVARCHAR rc=-1 [HY000] [MySQL][ODBC 9.4(w) Driver][mysqld-5.7.99]AnalysisException, msg: Unsupported MySQL type: BLOB (1105) SQL_C_WCHAR -> SQL_VARCHAR rc=-1 [HY000] [MySQL][ODBC 9.4(w) Driver][mysqld-5.7.99]AnalysisException, msg: Unsupported MySQL type: BLOB (1105) SQL_C_CHAR -> SQL_WVARCHAR rc=0 OK SQL_C_CHAR -> SQL_VARCHAR rc=0 OK SQL_C_CHAR -> SQL_CHAR rc=0 OK == array-bound prepared INSERT, PARAMSET_SIZE=3 array-bound INSERT rc=0 OK ``` ``` driver libmyodbc9w.so 09.04.0000, server MySQL 5.7.99 SQL_C_WCHAR -> SQL_WVARCHAR rc=-1 [HY000] [MySQL][ODBC 9.4(w) Driver][mysqld-5.7.99]NullPointerException, msg: null (1105) SQL_C_WCHAR -> SQL_VARCHAR rc=-1 [HY000] [MySQL][ODBC 9.4(w) Driver][mysqld-5.7.99]NullPointerException, msg: null (1105) SQL_C_CHAR -> SQL_WVARCHAR rc=0 OK SQL_C_CHAR -> SQL_VARCHAR rc=0 OK SQL_C_CHAR -> SQL_CHAR rc=0 OK ``` ### What You Expected? A `BLOB`-typed string parameter to be accepted for a `VARCHAR` column, as MySQL does — or, at minimum, the 2.1.0 line to return the 4.1.3 error instead of a `NullPointerException`. ### How to Reproduce? 1. `docker run -d -p 9030:9030 apache/doris:all-in-one-4.1.3`, then `ADMIN SET FRONTEND CONFIG ('force_olap_table_replication_num' = '1')`. 2. Build and run the program below with MySQL Connector/ODBC 9.4 through unixODBC: `gcc -O1 -Wall repro.c -lodbc`, connection string `Driver=/path/libmyodbc9w.so;Server=127.0.0.1;Port=9030;User=root;` (no `NO_SSPS`). It creates database `probe` and table `w1 (id INT, s VARCHAR(50))` with `enable_duplicate_without_keys_by_default`, then prepares the `INSERT` and binds the string five ways. <details><summary>repro.c</summary> ```c // Which prepared-INSERT bindings NPE the FE without NO_SSPS: the SQL_C_WCHAR cases a Unicode // ODBC client actually uses, and an array-bound (SQL_ATTR_PARAMSET_SIZE) INSERT. #include <sql.h> #include <sqlext.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <time.h> static void diag(SQLSMALLINT ht, SQLHANDLE h, const char* where) { SQLCHAR st[6], msg[1024]; SQLINTEGER ne; SQLSMALLINT len; SQLSMALLINT i = 1; while (SQLGetDiagRec(ht, h, i++, st, &ne, msg, sizeof msg, &len) == SQL_SUCCESS) printf(" [%s] %s (%d) at %s\n", st, msg, (int)ne, where); } #define CHECK(ht, h, call) do { SQLRETURN _r = (call); if (!SQL_SUCCEEDED(_r)) { printf("FAILED rc=%d: %s\n", (int)_r, #call); diag(ht, h, #call); exit(1);} } while (0) static SQLHENV env; static SQLHDBC dbc; static void connect_db(void) { const char* cs = getenv("FB_CONN"); if (!cs) { fprintf(stderr, "set FB_CONN=Driver=...;DBNAME=...;UID=...;PWD=...;CHARSET=UTF8;\n"); exit(2); } SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc); CHECK(SQL_HANDLE_DBC, dbc, SQLDriverConnect(dbc, NULL, (SQLCHAR*)cs, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)); SQLCHAR name[64], ver[64]; SQLSMALLINT l; SQLGetInfo(dbc, SQL_DRIVER_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DRIVER_VER, ver, sizeof ver, &l); printf("driver %s %s, ", name, ver); SQLGetInfo(dbc, SQL_DBMS_NAME, name, sizeof name, &l); SQLGetInfo(dbc, SQL_DBMS_VER, ver, sizeof ver, &l); printf("server %s %s\n", name, ver); } static void exec_ignore(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS); SQLFreeHandle(SQL_HANDLE_STMT, s); } static void exec_ok(const char* sql) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); CHECK(SQL_HANDLE_STMT, s, SQLExecDirect(s, (SQLCHAR*)sql, SQL_NTS)); SQLFreeHandle(SQL_HANDLE_STMT, s); } static SQLINTEGER g_id = 400; static void ins(const char* label, SQLSMALLINT ct, SQLSMALLINT st, void* buf, SQLLEN blen, SQLLEN* ind) { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); g_id++; SQLLEN i0 = 0; SQLRETURN r = SQLPrepare(s, (SQLCHAR*)"INSERT INTO w1 (id, s) VALUES (?, ?)", SQL_NTS); if (SQL_SUCCEEDED(r)) r = SQLBindParameter(s, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &g_id, 0, &i0); if (SQL_SUCCEEDED(r)) r = SQLBindParameter(s, 2, SQL_PARAM_INPUT, ct, st, 50, 0, buf, blen, ind); if (SQL_SUCCEEDED(r)) r = SQLExecute(s); printf(" %-46s rc=%d%s\n", label, (int)r, SQL_SUCCEEDED(r) ? " OK" : ""); if (!SQL_SUCCEEDED(r)) diag(SQL_HANDLE_STMT, s, label); SQLFreeHandle(SQL_HANDLE_STMT, s); } int main(void) { connect_db(); printf("connstr=%s\n", getenv("FB_CONN")); exec_ignore("CREATE DATABASE IF NOT EXISTS probe"); exec_ok("USE probe"); exec_ignore("DROP TABLE IF EXISTS w1"); exec_ok("CREATE TABLE w1 (id INT, s VARCHAR(50)) DISTRIBUTED BY RANDOM BUCKETS AUTO" " PROPERTIES (\"enable_duplicate_without_keys_by_default\" = \"true\")"); SQLWCHAR w[] = {'h','e','l','l','o',0}; char c[] = "hello"; SQLLEN ns = SQL_NTS; ins("SQL_C_WCHAR -> SQL_WVARCHAR", SQL_C_WCHAR, SQL_WVARCHAR, w, sizeof w, &ns); ins("SQL_C_WCHAR -> SQL_VARCHAR", SQL_C_WCHAR, SQL_VARCHAR, w, sizeof w, &ns); ins("SQL_C_CHAR -> SQL_WVARCHAR", SQL_C_CHAR, SQL_WVARCHAR, c, sizeof c, &ns); ins("SQL_C_CHAR -> SQL_VARCHAR", SQL_C_CHAR, SQL_VARCHAR, c, sizeof c, &ns); ins("SQL_C_CHAR -> SQL_CHAR", SQL_C_CHAR, SQL_CHAR, c, sizeof c, &ns); printf("== array-bound prepared INSERT, PARAMSET_SIZE=3\n"); { SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); SQLINTEGER ids[3] = {901, 902, 903}; char ss[3][16] = {"a", "bb", "ccc"}; SQLLEN i0[3] = {0,0,0}, sn[3] = {SQL_NTS, SQL_NTS, SQL_NTS}; SQLSetStmtAttr(s, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)(SQLULEN)3, 0); SQLRETURN r = SQLPrepare(s, (SQLCHAR*)"INSERT INTO w1 (id, s) VALUES (?, ?)", SQL_NTS); SQLBindParameter(s, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, ids, 0, i0); SQLBindParameter(s, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, ss, 16, sn); r = SQLExecute(s); printf(" array-bound INSERT rc=%d%s\n", (int)r, SQL_SUCCEEDED(r) ? " OK" : ""); if (!SQL_SUCCEEDED(r)) diag(SQL_HANDLE_STMT, s, "array-bound INSERT"); SQLFreeHandle(SQL_HANDLE_STMT, s); } SQLHSTMT s; SQLAllocHandle(SQL_HANDLE_STMT, dbc, &s); if (SQL_SUCCEEDED(SQLExecDirect(s, (SQLCHAR*)"SELECT id, s FROM w1 ORDER BY id", SQL_NTS))) while (SQLFetch(s) == SQL_SUCCESS) { SQLINTEGER a; char b[64]; SQLLEN ia, ibb; SQLGetData(s, 1, SQL_C_SLONG, &a, 0, &ia); SQLGetData(s, 2, SQL_C_CHAR, b, 64, &ibb); printf(" row id=%d s='%s'\n", (int)a, ibb == SQL_NULL_DATA ? "NULL" : b); } SQLFreeHandle(SQL_HANDLE_STMT, s); return 0; } ``` </details> ### Anything Else? Any parameter bound to a character SQL type from a non-`SQL_C_CHAR` C type takes the same path (`SQL_C_BINARY`, `SQL_C_TYPE_DATE`, `SQL_C_SLONG` to `SQL_VARCHAR` all fail identically on 2.1.0); `SQL_C_WCHAR` is the case that matters because it is what Unicode ODBC clients bind by default. Found while running Doris through [adbcBridge](https://github.com/singhpratech/adbcbridge) (an ADBC-over-ODBC driver; its Doris entry in [docs/COMPATIBILITY.md](https://github.com/singhpratech/adbcbridge/blob/main/docs/COMPATIBILITY.md) records the `NO_SSPS=1` workaround). The program above is plain ODBC and does not involve it. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
