Bug #118556 Queries on tables with same data return different results

• Submitted: 29 Jun 19:07
• Modified: 30 Jun 8:13
• Reporter: Jiyuan Li
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Ubuntu
• CPU Architecture: x86

[29 Jun 19:07] Jiyuan Li

Description:
SELECT should return same results but not

How to repeat:
DROP DATABASE IF EXISTS database107;
CREATE DATABASE database107;
USE database107;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL    STORAGE MEMORY, c1 DECIMAL  NULL) ;
CREATE TABLE t1(c0 FLOAT  NULL, c1 TINYTEXT  ) ;

ALTER TABLE t1 CHECKSUM 0, FORCE, STATS_PERSISTENT 1, PACK_KEYS 1, RENAME t2;
ALTER TABLE t2 FORCE, CHECKSUM 0, RENAME t4, PACK_KEYS 0, STATS_PERSISTENT 1, DELAY_KEY_WRITE 1, INSERT_METHOD NO, STATS_AUTO_RECALC DEFAULT, ENABLE KEYS, DROP COLUMN c0, COMPRESSION 'LZ4';
CREATE UNIQUE INDEX i0 ON t4(c1(1)) VISIBLE ALGORITHM= DEFAULT;

REPLACE INTO t4(c1) VALUES('Qi]jc*');

SELECT ALL t4.c1 FROM t4 WHERE (t4.c1) <= ("ۢZ");

DROP DATABASE IF EXISTS database107_raw;
CREATE DATABASE database107_raw;
USE database107_raw;

CREATE TABLE t4(c1 tinytext);
INSERT INTO t4 SELECT * FROM database107.t4;

SELECT ALL t4.c1 FROM t4 WHERE (t4.c1) <= ("ۢZ");

[30 Jun 8:13] MySQL Verification Team

Hello Jiyuan Li,

Thank you for the report and test case.

-- non-ndb SE

mysql> show variables like 'version%';
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| version                 | 9.3.0-cluster                        |
| version_comment         | MySQL Cluster Community Server - GPL |
| version_compile_machine | x86_64                               |
| version_compile_os      | Linux                                |
| version_compile_zlib    | 1.3.1                                |
+-------------------------+--------------------------------------+
5 rows in set (0.001 sec)

mysql>
mysql>
mysql> DROP DATABASE IF EXISTS database107;
Query OK, 0 rows affected, 1 warning (0.002 sec)

mysql> CREATE DATABASE database107;
Query OK, 1 row affected (0.042 sec)

mysql> USE database107;
Database changed
mysql> CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL    STORAGE MEMORY, c1 DECIMAL  NULL) ;
Query OK, 0 rows affected (0.009 sec)

mysql> CREATE TABLE t1(c0 FLOAT  NULL, c1 TINYTEXT  ) ;
Query OK, 0 rows affected (0.010 sec)

mysql>
mysql> ALTER TABLE t1 CHECKSUM 0, FORCE, STATS_PERSISTENT 1, PACK_KEYS 1, RENAME t2;
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t2 FORCE, CHECKSUM 0, RENAME t4, PACK_KEYS 0, STATS_PERSISTENT 1, DELAY_KEY_WRITE 1, INSERT_METHOD NO, STATS_AUTO_RECALC DEFAULT, ENABLE KEYS, DROP COLUMN c0, COMPRESSION 'LZ4';
Query OK, 0 rows affected, 1 warning (0.022 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> CREATE UNIQUE INDEX i0 ON t4(c1(1)) VISIBLE ALGORITHM= DEFAULT;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> REPLACE INTO t4(c1) VALUES('Qi]jc*');
Query OK, 1 row affected (0.002 sec)

mysql>
mysql> SELECT ALL t4.c1 FROM t4 WHERE (t4.c1) <= ("ۢZ");
Empty set (0.000 sec)

mysql> DROP DATABASE IF EXISTS database107_raw;
Query OK, 0 rows affected, 1 warning (0.002 sec)

mysql> CREATE DATABASE database107_raw;
Query OK, 1 row affected (0.042 sec)

mysql> USE database107_raw;
Database changed
mysql>
mysql> CREATE TABLE t4(c1 tinytext);
Query OK, 0 rows affected (0.010 sec)

mysql> INSERT INTO t4 SELECT * FROM database107.t4;
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT ALL t4.c1 FROM t4 WHERE (t4.c1) <= ("ۢZ");
+--------+
| c1     |
+--------+
| Qi]jc* |
+--------+
1 row in set (0.000 sec)

regards,
Umesh