In MySQL 8 median, stats_mode returns VARBINARY type

[AndrewsBA]

ResultSetMetaData.getColumnTypeName() returns VARBINARY for median and stats_mode functions in MySQL version 8. In Previous versions it was DOUBLE. Have tested with two mysql connector jars. 5.1.44 and 8.0.11. Same happens in both. Let me know if you need more details.
udf_infusion.so.zip

[ghost]

I'm having the same issue. I'm using the x64.dll for windows.
I'm also getting loading erros when executing load.win.sql due to row_number being a native function in mysql 8

[infusion]

Thanks for opening that up! I need to verify the type mismatch.

@scanaximander About the .dll: Unfortunately, I don't have a Windows available to compile it. When the VARBINARY problem is fixed, it would be cool to find someone to compile a new dll.

[AndrewsBA]

I'm also getting loading erros when executing load.win.sql due to row_number being a native function in mysql 8

remove the row_number function fromm load.win.sql and load. mysql won't allow row_number since it is a keyword now.

[infusion]

Yes, that's a short cut. But it is still in the dll and in the long run, I would like to have it clean.

[AndrewsBA]

Yes, that's a short cut. But it is still in the dll and in the long run, I would like to have it clean.

👍

[ghost]

@infusion thanks for the prompt reply. I'm not adept at compiling dlls, but when my workload clears up I'll give it a shot.

[infusion]

I did not compile it previously, but the project files are included. Maybe it's enough to open it in Visual Studio, install the MySQL header files and done.

[AndrewsBA]

@infusion Did you get a chance to verify the type mismatch?

[infusion]

I looked into it from a users perspective:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15-6  |
+-----------+
1 row in set (0.07 sec)

mysql> create temporary table x as select median(1);
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show create table x;
+-------+-----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------+
| x     | CREATE TEMPORARY TABLE `x` (
  `median(1)` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

It shows, when I put in a number, median returns not VARBINARY. How did you verify, the opposite?

[AndrewsBA]

@infusion
This is how I did test it. May be an issue with the jdbc driver? I did the same with two versions of connector jars. 5.1.44 and 8.0.11

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class Test {

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String CONNECTION_URL = "jdbc:mysql://localhost:3306/test";
    private static final String DATABASE = "test";
    private static final String QUERY = "select median(1),stats_mode(1) from test.t limit 0";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection con = DriverManager.getConnection (CONNECTION_URL , "root", "");
        ResultSet rs = con.createStatement().executeQuery(QUERY);
        if (rs != null) {
            System.out.println("Column Class \t\t Column Type\t\t Column Name");

                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.println(rsmd.getColumnClassName(i)+"\t\t\t"+rsmd.getColumnTypeName(i)+"\t\t\t"+rsmd.getColumnName(i));
            }
        }   
    }
}

[infusion]

Could you please test the type of the return value of median using the procedure with create temporary table please?

[AndrewsBA]

@infusion It is double. May be the issue occurs due to the connector jar.

[infusion]

Is this problem new with MySQL 8? Do you get the correct number when you cast it? The question is, is this reproducible with other functions that might return real.
BTW: Did you notice, that you have a LIMIT 0 in your query? Mabye this is something the JDBC driver is not able to deal with properly with new MySQL?