Unexpected SQL exception returned

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

I run the following statement through JDBC with version 8.3.0

CREATE TABLE t1 ( c1 int, c2 int);
CREATE TABLE t2 ( c1 int unsigned);
INSERT INTO t1 (c1,c2) VALUES (8,4);
INSERT INTO t2 (c1) VALUES (2454396638);
SELECT ca4 AS ca1 FROM (SELECT c2 AS ca4 FROM t1) AS ta2 CROSS JOIN (SELECT c1 FROM t2) AS ta3 UNION ALL SELECT c1 AS ca13 FROM t2; 

2. What did you expect to see? (Required)

The JDBC should return {2454396638, 4}

3. What did you see instead (Required)

A SQLException is returned.

java.sql.SQLException: Out of range value for column 'ca1' : value 2454396638 is not in class java.lang.Integer range
    at org.mariadb.jdbc.internal.com.read.resultset.rowprotocol.RowProtocol.rangeCheck(RowProtocol.java:283)
    at org.mariadb.jdbc.internal.com.read.resultset.rowprotocol.TextRowProtocol.getInternalInt(TextRowProtocol.java:255)
    at org.mariadb.jdbc.internal.com.read.resultset.rowprotocol.TextRowProtocol.getInternalObject(TextRowProtocol.java:900)
    at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.getObject(SelectResultSet.java:1159)
    in JdbcHelperImpl.getObject(JdbcHelperImpl.java:353)

4. What is your TiDB version? (Required)

Release Version: v8.0.0 Edition: Community Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916 Git Branch: HEAD UTC Build Time: 2024-03-28 14:22:15 GoVersion: go1.21.4 Race Enabled: false Check Table Before Drop: false Store: tikv

[sayJason]

Moreover, I can reproduce it with the client like DataGrip with JDBC version 8.2.0.

[aytrack]

simple reproduce is select c2 from t1 union select c1 from t2;. It's about int uninion int unsigned for tidb

mysql> select c2 from t1 union select c1 from t2;
Field   1:  `c2`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 10
Decimals:   0
Flags:      BINARY NUM

+------------+
| c2         |
+------------+
|          4 |
| 2454396638 |
+------------+
2 rows in set (0.00 sec)

+------------------------------+---------+-----------+---------------+-------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                         |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------+
| HashAgg_9                    | 2.00    | root      |               | group by:Column#6, funcs:firstrow(Column#6)->Column#6 |
| └─Union_10                   | 2.00    | root      |               |                                                       |
|   ├─TableReader_13           | 1.00    | root      |               | data:TableFullScan_12                                 |
|   │ └─TableFullScan_12       | 1.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                        |
|   └─Projection_14            | 1.00    | root      |               | cast(test.t2.c1, int(11) BINARY)->Column#6            |
|     └─TableReader_16         | 1.00    | root      |               | data:TableFullScan_15                                 |
|       └─TableFullScan_15     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                        |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------+
7 rows in set (0.01 sec)

for mysql

mysql> select c2 from t1 union select c1 from t2;
Field   1:  `c2`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 10
Decimals:   0
Flags:      NUM

+------------+
| c2         |
+------------+
|          4 |
| 2454396638 |
+------------+
2 rows in set (0.00 sec)

[aytrack]

for mysql , it return the bigint type

for tidb, it return the int type and wrong result

[spihiker]

As versions are released, there are more and more problems. Can you put some effort into making the 6.5 version more stable?

[YangKeao]

As versions are released, there are more and more problems. Can you put some effort into making the 6.5 version more stable?

Let's focus on concrete issues. Did you face any issue while using 6.5 and shows that the version 6.5 is unstable? If so, feel free to open a new issue and we'll try to locate and fix it :beers:.

Besides, I believe we are continuously working on making every (not EOL) versions more stable. For example, the fix of this issue will also be back-ported to 6.5 so that the next version of 6.5 will include the fix.

[spihiker]

Please deal with existing issues first. I encountered the non-functional bug mentioned by someone in 6.5.3 before, and I still encounter it in 6.5.7. It was not until 6.5.8 that it was found to be resolved. I just hope that a big version is stable enough, including current functional modules, sql standards, compatibility, etc. Like mysql-5.6, mysql-5.7. I also hope that tidb will develop better and better and become an eye-catching database product at home and abroad.