| Bug #111729 | (varchar, binary) compare, result incorrect | ||
|---|---|---|---|
| Submitted: | 12 Jul 2023 8:34 | Modified: | 13 Jul 2023 3:29 |
| Reporter: | haizhen xue | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[12 Jul 2023 13:19]
MySQL Verification Team
Hi Mr. xue, Thank you for your bug report. However, this is not a bug. This is expected result. We have amended and expanded your test case, so that we first have two queries that let us see what is being compared. Then we ran your query and then a changed query, which did not utilise nested query, but only row comparisons. Here are the results: +-------------+-------------+ | varchar_col | binary_col | +-------------+-------------+ | | c | +-------------+-------------+ +-------------+-------------+ | varchar_col | binary_col | +-------------+-------------+ | bbba2 | Amanda Ruiz | +-------------+-------------+ +----------+ | COUNT(*) | +----------+ | 1 | +----------+ +----------+ | COUNT(*) | +----------+ | 1 | +----------+ Results are correct, since second row in the expression is greater then the first row in the expression. That is because varchar column in t3 is greater then corresponding column in t2. Second one is actually smallest possible CHAR /VARCHAR value possible. Not a bug.
[13 Jul 2023 3:29]
haizhen xue
mysql> CREATE TABLE `t2` (
-> `varchar_col` varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
-> `binary_col` binary(11) NOT NULL
-> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> CREATE TABLE `t3` (
-> `varchar_col` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
-> `binary_col` binary(11) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values ('', 0x6300000000000000000000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values ('bbba2', 0x416D616E6461205275697A);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values ('', 0x446F726973205269636500);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT COUNT(*) FROM t2 where (varchar_col,binary_col) < (select varchar_col,binary_col from t3 order by 1,2 limit 1);
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select varchar_col,binary_col from t3 order by 1,2 limit 1;
+-------------+--------------------------+
| varchar_col | binary_col |
+-------------+--------------------------+
| | 0x446F726973205269636500 |
+-------------+--------------------------+
1 row in set (0.00 sec)
[13 Jul 2023 11:54]
MySQL Verification Team
Hi, You have got different result, because you change the ordering. That is expected behaviour.
Description: result incorrest: mysql> CREATE TABLE `t2` ( -> `varchar_col` varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, -> `binary_col` binary(11) NOT NULL -> ); binary(11) NOT NULL );Query OK, 0 rows affected, 2 warnings (0.08 sec) mysql> CREATE TABLE `t3` ( -> `varchar_col` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL, -> `binary_col` binary(11) NOT NULL -> ); Query OK, 0 rows affected (0.07 sec) mysql> insert into t2 values ('', 0x6300000000000000000000); Query OK, 1 row affected (0.01 sec) mysql> insert into t3 values ('bbba2', 0x416D616E6461205275697A); Query OK, 1 row affected (0.01 sec) mysql> SELECT COUNT(*) FROM t2 where (varchar_col,binary_col) < (select varchar_col,binary_col from t3 order by 1,2 limit 1); +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.01 sec) expect COUNT(*) result is 0. How to repeat: CREATE TABLE `t2` ( `varchar_col` varchar(0) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `binary_col` binary(11) NOT NULL ); CREATE TABLE `t3` ( `varchar_col` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL, `binary_col` binary(11) NOT NULL ); insert into t2 values ('', 0x6300000000000000000000); insert into t3 values ('bbba2', 0x416D616E6461205275697A); SELECT COUNT(*) FROM t2 where (varchar_col,binary_col) < (select varchar_col,binary_col from t3 order by 1,2 limit 1); Suggested fix: SELECT COUNT(*) FROM t2 where (varchar_col,binary_col) < (select varchar_col,binary_col from t3 order by 1,2 limit 1); expect COUNT(*) result is 0.