Prepared statements: a string parameter sent with MySQL type BLOB (what MySQL Connector/ODBC uses for every SQL_C_WCHAR parameter) is refused — 4.1.3 `Unsupported MySQL type: BLOB`, 2.1.0 NullPointerException

[singhpratech]

Search before asking

• I had searched in the issues and found no similar issues. ((bug)(prepared statement) #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.

repro.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; }

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 (an ADBC-over-ODBC driver; its Doris entry in 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

• I agree to follow this project's Code of Conduct