Bug #92627 COM_STMT_PREPARE_OK always returns the same column type in param definition
Submitted: 1 Oct 2018 19:03 Modified: 3 Oct 2018 15:32
Reporter: Wojtek Mach Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0.12 OS:Mac OS X
Assigned to: CPU Architecture:Any

[1 Oct 2018 19:03] Wojtek Mach
Description:
I'm implementing prepared statements binary protocol. I'm fetching the column_type of the parameter placeholder from the column_definition part of the COM_STMT_PREPARE_OK  packet, however I noticed the type is always 0xFD, regardless of the actual type of the column. I tried both on SELECT and INSERT queries.

How to repeat:
For this table:

    CREATE TABLE test_types (
      `id` int primary key auto_increment,
      `my_tinyint` TINYINT,
      `my_smallint` SMALLINT,
      `my_mediumint` MEDIUMINT,
      `my_int` INT,
      `my_bigint` BIGINT
    )

For both of these queries:

    SELECT 1 FROM test_types WHERE my_tinyint = ?
    SELECT 1 FROM test_types WHERE my_smallint = ?

The **params** column definition part of the packet is:

    0000 03 64 65 66 00 00 00 01 3F 00 0C 3F 00 00 00 00  .def....?..?....
    0001 00 FD 80 00 00 00 00                             .ý�....

the type is 0xFD, var_string, whereas I'd expect 0x01 and 0x02 respectively.
[1 Oct 2018 20:21] Wojtek Mach
Worth mentioning that I'm getting the correct column_type from column definitions for *columns*, just not *params*.

I also prepare a quick c program that reproduces the issue and I observe the packets with Wireshark.

    #include<stdio.h>
    #include<string.h>
    #include<mysql/mysql.h>

    int main() {
      MYSQL *conn = mysql_init(NULL);
      int ssl_mode = SSL_MODE_DISABLED;
      mysql_options(conn, MYSQL_OPT_SSL_MODE, &ssl_mode);

      if (mysql_real_connect(conn, "127.0.0.1", "root", "secret", "myxql_test", 3306, NULL, 0) == NULL) {
          fprintf(stderr, "%s\n", mysql_error(conn));
          mysql_close(conn);
          exit(1);
      }

      MYSQL_STMT *stmt1 = mysql_stmt_init(conn);
      char *sql1 = "SELECT 1 FROM test_types WHERE my_tinyint = ?";
      if (mysql_stmt_prepare(stmt1, sql1, strlen(sql1))) {
          fprintf(stderr, "%s\n", mysql_error(conn));
          mysql_close(conn);
          exit(1);
      }

      MYSQL_STMT *stmt2 = mysql_stmt_init(conn);
      char *sql2 = "SELECT 1 FROM test_types WHERE my_smallint = ?";
      if (mysql_stmt_prepare(stmt2, sql2, strlen(sql2))) {
          fprintf(stderr, "%s\n", mysql_error(conn));
          mysql_close(conn);
          exit(1);
      }

      return 0;
    }
[2 Oct 2018 12:46] Sinisa Milivojevic
Hi,

Thank you for your bug report.

However, what you are trying to get is not doable with COM_STMT_PREPARE. 

After you prepare the statement, you should bind the parameter. Then, after that, check the package for the parameters type. If that does not change anything, try to execute the statement also ......
[2 Oct 2018 15:57] Wojtek Mach
Hi Sinisa,

Thank you for your reply.

> After you prepare the statement, you should bind the parameter. Then, after that, check the package for the parameters type. If that does not change anything, try to execute the statement also ......

For clarification, I'm implementing the binary protocol without using the C binding, just using the wire protocol, I showed the C code just as an illustration. 

To give a bit more context about my use case, currently when executing preparing statements with integer params I'm always encoding them as MYSQL_TYPE_LONGLONG even though the type of the param should actually be, say, MYSQL_TYPE_TINYINT. If such information would be available, what's the type of the param returned from COM_STMT_PREPARE_OK, I could then encode it more efficiently. Another example when this would be useful is if I'd try to send integer instead of float, I could raise a type mismatch error without even making the network call, as opposed to wait for server to raise an error.

So if I understand this correctly, column_type in *param* column definition packet was never meant to store the actual type of the parameter, is that correct? In that case, I'll work around this.
[3 Oct 2018 12:48] Sinisa Milivojevic
Hi,

Yes, that is the case.

When you prepare the statement, the query string is parsed only for number of parameters and to each one a default type, a string , is assigned. Query text is not parsed to check columns etc, so column type is not known until you bind parameters and (in some cases) execute them.

Hope that it helps.
[3 Oct 2018 13:46] Wojtek Mach
Thank you for the confirmation.
[3 Oct 2018 15:32] Sinisa Milivojevic
You are truly welcome.