Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1099

The column type returned by a JSON field in select union is incorrect/JSON类型字段在select union中返回列类型错误

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Not a Bug
    • 3.1.4
    • N/A
    • metadata
    • 操作系统:Windows10专业版;
      MariaDB Connector/J版本:3.1.4
      MySQL Server版本:5.7.24
      IntelliJ IDEA: 2019.2

    Description

      OS version:Windows10 professional
      MariaDB Connector/J version:3.1.4
      MySQL Server version:5.7.24
      IntelliJ IDEA version: 2019.2

      create table SQL:
      drop table if exists json1;
      drop table if exists json2;
      create table json1(id int , json_col json);
      create table json2(id int , json_col json);

      DemoCode:
      private static String url = "jdbc:mariadb://127.0.0.1:3306/world?useUnicode=true&characterEncoding=utf-8";

      public static void main(String args[]) {
      try (Connection conn = DriverManager.getConnection(url, "root", "xxxxxx")) {
      String sql = "(select json_col from json1) union all (select json_col from json2) order by 1 asc;";
      try (Statement statement = conn.createStatement();
      ResultSet resultSet = statement.executeQuery(sql)) {
      ResultSetMetaData rsmd = resultSet.getMetaData();
      while (resultSet.next()) {

      }
      }
      } catch (Throwable e)

      { e.printStackTrace(); }

      }

      Symptom:
      1. when sql is select UNION: (select json_col from json1) union all (select json_col from json2) order by 1 asc , The column type returned in the query result set is BLOB, which is incorrect.
      Failed to upload attachment screenshots. You can view fieldPackets of rsmd at the break point of IDEA. The first item is BlobColumn (dataType is BLOB).

      2. when sql is not select UNION: select json_col from json1 order by 1 asc , the column type returned in the query result set is JSON, which is correct.
      If attachment screenshots cannot be uploaded, view fieldPackets of rsmd at the break point of IDEA. The first item is JsonColumn (the value of dataType is JSON).

      中文版描述
      操作系统:Windows10专业版;
      MariaDB Connector/J版本:3.1.4
      MySQL Server版本:5.7.24
      IntelliJ IDEA: 2019.2

      建表SQL:
      drop table if exists json1;
      drop table if exists json2;
      create table json1(id int , json_col json);
      create table json2(id int , json_col json);

      客户端代码:
      private static String url = "jdbc:mariadb://127.0.0.1:3306/world?useUnicode=true&characterEncoding=utf-8";

      public static void main(String args[]) {
      try (Connection conn = DriverManager.getConnection(url, "root", "xxxxxx")) {
      String sql = "(select json_col from json1) union all (select json_col from json2) order by 1 asc;";
      try (Statement statement = conn.createStatement();
      ResultSet resultSet = statement.executeQuery(sql)) {
      ResultSetMetaData rsmd = resultSet.getMetaData();
      while (resultSet.next()) {

      }
      }
      } catch (Throwable e)

      { e.printStackTrace(); }

      }

      问题现象:
      1、当使用select UNION时(select json_col from json1) union all (select json_col from json2) order by 1 asc;,查询结果集返回列类型为BLOB,是错误的。
      无法上传附件截图,可在IDEA中断点查看rsmd的fieldPackets,第一项为BlobColumn(其dataType为BLOB)

      2、当未使用select UNION时select json_col from json1 order by 1 asc,查询结果集返回列类型为JSON,是正确的。
      无法上传附件截图,可在IDEA中断点查看rsmd的fieldPackets,第一项为JsonColumn(其dataType为JSON);

      Attachments

        Issue Links

          Activity

            People

              diego dupin Diego Dupin
              wangxinyu wangxinyu
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.