[#MDEV-23913] Prepared statement column metadata can be wrong with UNION

Comment by Oleksandr Byelkin [ 2020-10-08 ]

Probably present in all current versions (have to be checked)

Comment by Vladislav Vaintroub [ 2020-10-08 ]

mostly likely present in all versions, but currently affects ~~MDEV-19237~~ somewhat. I thought there would be a often used variety of prepared statements, where metadata does not mutate unless reprepare(). For this it did not hold, even from prepare to first execution.

Comment by Oleksandr Byelkin [ 2020-10-13 ]

Critical for the other task

Comment by Oleksandr Byelkin [ 2020-10-13 ]

proposed fix:

diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc

index c144d3a8d7e..180c7472db3 100644

--- a/sql/sql_prepare.cc

+++ b/sql/sql_prepare.cc

@@ -1498,7 +1498,9 @@ static int mysql_test_select(Prepared_statement *stmt,

   if (!lex->describe && !thd->lex->analyze_stmt && !stmt->is_sql_prepare())

     /* Make copy of item list, as change_columns may change it */

-    List<Item> fields(lex->first_select_lex()->item_list);

+    List<Item> fields(lex->unit.fake_select_lex ?

+                      lex->unit.fake_select_lex->item_list :

+                      lex->first_select_lex()->item_list);

     /* Change columns if a procedure like analyse() */

     if (unit->last_procedure && unit->last_procedure->change_columns(thd, fields))

Comment by Vladislav Vaintroub [ 2020-10-13 ]

The original test SELECT 1 UNION SELECT 'abc' passes alright with the proposed fix, but a more complicated one (taken from olap.test) fails . The query in question would be

SELECT i FROM t1 GROUP BY i WITH ROLLUP UNION ALL SELECT ELT(FOUND_ROWS(), 1) f FROM t1 GROUP BY f WITH ROLLUP

One can add this test to mysql_client_test.c , to check

static void test_union_metadata2()

  MYSQL_STMT *stmt;

  MYSQL_RES *result;

  MYSQL_FIELD *fields;

  unsigned long type1, type2;

  int rc;

  myheader("test_union_metadata2");

  rc= mysql_query(mysql, "CREATE TABLE t1(i INT)");

  stmt= mysql_simple_prepare(

      mysql, "SELECT i FROM t1 GROUP BY i WITH ROLLUP UNION ALL SELECT ELT("

                 "FOUND_ROWS(), 1) f FROM t1 GROUP BY f WITH ROLLUP");

  check_stmt(stmt);

  result= mysql_stmt_result_metadata(stmt);

  mytest(result);

  fields= mysql_fetch_fields(result);

  type1= fields[0].type;

  mct_log("Field name: '%s'/'%s';\n table: '%s'/'%s';\n "

          "db: '%s';\n catalog: '%s';\n length: %d;\n max_length: %d;\n "

          "type: %d;\n decimals: %d\n",

          (const char *) fields[0].name, (const char *) fields[0].org_name,

          (const char *) fields[0].table, (const char *) fields[0].org_table,

          (const char *) fields[0].db, (const char *) fields[0].catalog,

          (int) fields[0].length, (int) fields[0].max_length,

          (int) fields[0].type, (int) fields[0].decimals);

  rc= mysql_stmt_execute(stmt);

  check_execute(stmt, rc);

  result= mysql_stmt_result_metadata(stmt);

  mytest(result);

  fields= mysql_fetch_fields(result);

  type2= fields[0].type;

  mct_log("Field name: '%s'/'%s';\n table: '%s'/'%s';\n "

          "db: '%s';\n catalog: '%s';\n length: %d;\n max_length: %d;\n "

          "type: %d;\n decimals: %d\n",

          (const char *) fields[0].name, (const char *) fields[0].org_name,

          (const char *) fields[0].table, (const char *) fields[0].org_table,

          (const char *) fields[0].db, (const char *) fields[0].catalog,

          (int) fields[0].length, (int) fields[0].max_length,

          (int) fields[0].type, (int) fields[0].decimals);

  mysql_free_result(result);

  mysql_stmt_close(stmt);

  rc= mysql_query(mysql, "DROP TABLE t1");

  DIE_UNLESS(type1 == type2);

1 of (1/1): test_union_metadata2

#####################################

Field name: 'i'/'i';

 table: 't1'/'t1';

 db: 'client_test_db';

 catalog: 'def';

 length: 11;

 max_length: 0;

 type: 3;

 decimals: 0

Field name: 'i'/'i';

 table: 't1'/'t1';

 db: 'client_test_db';

 catalog: 'def';

 length: 11;

 max_length: 0;

 type: 253;

 decimals: 39

C:\work\10.6-org\tests\mysql_client_test.c:21089: check failed: 'type1 == type2'

The type 3 (MYSQL_TYPE_LONG) changes to type 253 (MYSQL_TYPE_VAR_STRING) from prepare to execute.

Comment by Vladislav Vaintroub [ 2020-11-23 ]

was closed by mistake

[MDEV-23913] Prepared statement column metadata can be wrong with UNION Created: 2020-10-07 Updated: 2023-01-19
Status:	Stalled
Project:	MariaDB Server
Component/s:	Prepared Statements
Affects Version/s:	10.6
Fix Version/s:	10.6

[MDEV-23913] Prepared statement column metadata can be wrong with UNION Created: 2020-10-07 Updated: 2023-01-19