Bug #108545 Erroneous MYSQL_TYPE_INVALID data type
Submitted: 19 Sep 21:19 Modified: 17 Oct 19:16
Reporter: diego dupin 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

[19 Sep 21:19] diego dupin
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.
[20 Sep 12:14] MySQL Verification Team
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 ....
[22 Sep 11:50] MySQL Verification Team
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.
[17 Oct 19:16] Jon Stephens
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.
[18 Oct 11:35] MySQL Verification Team
Thank you, Jon, very much,

For documenting the fix and closing this bug.