MySQL Bugs: #108545: Erroneous MYSQL_TYPE

Bug #108545	Erroneous MYSQL_TYPE_INVALID data type
Submitted:	19 Sep 2022 21:19	Modified:	17 Oct 2022 19:16
Reporter:	diego dupin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	243 data type

Description:
Server send wrong MYSQL_TYPE_INVALID datatype to client, resulting in error. 
This data type is not expected to be send to connectors.

How to repeat:
CREATE FUNCTION basic_function (t1 INT, t2 INT unsigned) RETURNS INT DETERMINISTIC RETURN t1 * t2;

prepare "SELECT basic_function(?,?)"

exchanges are : 
send : 
--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 1B 00 00 00 16 53 45 4C  45 43 54 20 62 61 73 69 | .....SELECT basi |
| 63 5F 66 75 6E 63 74 69  6F 6E 28 3F 2C 3F 29    | c_function(?,?)  |
+--------------------------------------------------+------------------+

receive : 
+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 0C 00 00 01 00 01 00 00  00 01 00 02 00 00 00 00 | ................ |
+--------------------------------------------------+------------------+

+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 02 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 15 00 00 00 08 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+

+--------------------------------------------------+
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
+--------------------------------------------------+------------------+
| 17 00 00 03 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
| 00 00 00 00 00 F3 80 00  00 00 00                | ...........      |
+--------------------------------------------------+------------------+
...

this second column definition send a data type 0xf3 = MYSQL_TYPE_INVALID (0x08 expected) that is not expected ... creating various issue afterwhile.

Hi Mr. dupin,

Thank you for your bug report.

However, we do not have enough information from you in order to process your bug report. Also, your expectations are not in tune with SQL:2016, which is the current and valid SQL standard.

The true types can not be returned with PREPARE, but with EXECUTE ..... Particularly when all the parameters are yet to be supplied with EXECUTE ...... WITH .......

If you can provide an example with EXECUTE returning invalid type(s), then please send all the other info required, like table structure and contents etc ,,, so that we can try to repeat it fully. 

Also, we do not need a hexdump, since types can be obtained with metadata ........

Can't repeat ....

Hi Mr. Dupin,

We have analysed this case further and discovered that you are actually quite right.

Problem is when we have more than one parameter. With one parameter, there is no problem.

Hence, we have decided to fix the problem.

Verified as reported.

Documented fix as follows in the MySQL 8.0.32 changelog:

    The deduction of data types for dynamic parameters passed as
    parameters to a user-defined SQL function was correct only for a
    single parameter; with more than one parameter, no such
    deduction was performed for the second and following parameters,
    with the result that their types were always reported
    erroneously to clients as MYSQL_TYPE_INVALID.

Closed.

Thank you, Jon, very much,

For documenting the fix and closing this bug.