dble occur error "Field type is not supported" when execute "select * from table1 union all select from table2"

[ClippedWings-renzy]

• dble version： 2.19.03.14
• preconditions ：
  jdbc version: 5.1.45 MySQL version：5.7.21
• configs：

schema.xml

<table name = "table1" type="default" rule="Hash" dataNode="dn0-dn31">
<table name = "table2" type="default" rule="Hash" dataNode="dn0-dn31">

• steps：
  step1. client: Class.forName("com.mysql.jdbc.Driver") Connection conn = DriverManager.getConnection(url,user,pwd) Statement stat = conn.createStatement(); String sql = "select * from table1 where id1 =? and id2(routeColumn) = ? and id3=? union all select * from table2 where id1 = ? and id2(routeColumn)=? and id3=?"
• expect result：
  1. one result
• real result：
  1. dble occur error:"Field type is not supported" in com.actiontech.dble.net.mysql.BinaryRowDataPacket.convert();
• supplements：
  1.client: when i use executeQuery(sql) to query,the result is correct，but use execute or executeQuery(),it will occur error.

[PanternBao]

need detail, eg: create table statement and jdbc code

[TommyZC]

need detail, eg: create table statement and jdbc code 这是复现的demo，只有使用union all select的情况下才会触发bug，使用union select不会触发

import java.sql.*;

public class testdemo {
    public static final String URL = "jdbc:mysql://192.168.31.128:8066/testdb?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&useSSL=true&useServerPrepStmts=true";
    public static final String USER = "dble";
    public static final String PASSWORD = "dble";
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement ps;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            String sql = "select * from nasebug1 where id = 1 union all select * from nasebug2 where id = 1";
            ps = conn.prepareStatement(sql);
            ps.execute();
            ps.close();
            }catch (Exception e){
                System.out.println(" error "+e);
            }finally {
                conn.close();
                System.out.println("task done");
            }
    }
}

数据表如下：

mysql> show create table nasebug1\G
*************************** 1. row ***************************
       Table: nasebug1
Create Table: CREATE TABLE `nasebug1` (
  `id` int(10) NOT NULL,
  `day` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> show create table nasebug2\G
*************************** 1. row ***************************
       Table: nasebug2
Create Table: CREATE TABLE `nasebug2` (
  `id` int(10) NOT NULL,
  `day` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表数据插入在nasebug2表中

insert into nasebug2 values('1','2022-03-11');

schema.xml

<?xml version="1.0"?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd">
<dble:schema xmlns:dble="http://dble.cloud/" version="2.19.03.14">

    <schema name="testdb">

        <!-- random sharding using mod sharind rule -->
        <table name="nasebug1" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-mod"/>
        <table name="nasebug2" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-mod"/>

    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
    <dataNode name="dn1" dataHost="host1" database="dbletest1"/>
    <dataNode name="dn2" dataHost="host1" database="dbletest2"/>
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="host1" url="localhost:3306" user="root" password="Tzc199808">
        </writeHost>
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
</dble:schema>

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dble:rule SYSTEM "rule.dtd">
<dble:rule xmlns:dble="http://dble.cloud/" version="2.19.03.14">

    <tableRule name="sharding-by-mod">
        <rule>
            <columns>id</columns>
            <algorithm>hashmod</algorithm>
        </rule>
    </tableRule>

    <!-- eg:  mod 4 -->
    <function name="hashmod" class="Hash">
        <property name="partitionCount">2</property>
        <property name="partitionLength">1</property>
    </function>

</dble:rule>

[LUAgam]

根据以上步骤在dble的2.19.03.2没复现，需要自行确定是不是自编译版本导致的 补充：在2.19.03/lts版本中同样没有复现

[PanternBao]

refer: https://dev.mysql.com/doc/internals/en/com-query-response.html#fnref_internal

MySQL bug: https://bugs.mysql.com/bug.php?id=87534

solution:

1. update mysql to 5.7.22 or higher
2. modify sql, replace union all