feature: incorrect result set.INSERT INTO t1 (a) VALUES(x'FFA5A4ABACA9');The query result is empty

[shangyanwen]

Describe the problem

CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
a VARCHAR(100) CHARACTER SET ARMSCII8
)engine=tianmu;
INSERT INTO t1 (a) VALUES(x'616263AC646566');
INSERT INTO t1 (a) VALUES(x'FFA5A4ABACA9');

SELECT *, HEX(a) FROM t1 WHERE a = x'616263AC646566';
#Incorrect test results
Empty set (0.00 sec)

SELECT *, HEX(a) FROM t1 WHERE a = x'FFA5A4ABACA9';
#Incorrect test results
Empty set (0.00 sec)

DROP TABLE t1;

Expected behavior

# Correct test results（note:Innodb test verification, test results are as follows）

SELECT *, HEX(a) FROM t1 WHERE a = x'616263AC646566';
+----+---------+----------------+
| id | a       | HEX(a)         |
+----+---------+----------------+
|  1 | abc-def | 616263AC646566 |
+----+---------+----------------+

SELECT *, HEX(a) FROM t1 WHERE a = x'FFA5A4ABACA9';
+----+--------+--------------+
| id | a      | HEX(a)       |
+----+--------+--------------+
|  2 | '(),-. | FFA5A4ABACA9 |
+----+--------+--------------+

How To Reproduce

CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
a VARCHAR(100) CHARACTER SET ARMSCII8
)engine=tianmu;
INSERT INTO t1 (a) VALUES(x'616263AC646566');
INSERT INTO t1 (a) VALUES(x'FFA5A4ABACA9');
SELECT *, HEX(a) FROM t1 WHERE a = x'616263AC646566';
SELECT *, HEX(a) FROM t1 WHERE a = x'FFA5A4ABACA9';
DROP TABLE t1;

Environment

[root@dev bin]# ./mysqld --version
./mysqld  Ver 5.7.36_v1.0.0_beta-StoneDB for Linux on x86_64 (build-)
build information as follow: 
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7
        Branch name: stonedb-5.7
        Last commit ID: eed32f6
        Last commit time: Date:   Wed Aug 3 11:19:48 2022 +0800
        Build time: Date: Wed Aug  3 13:01:31 CST 2022

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[adofsauron]

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE t1 (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> a VARCHAR(100) CHARACTER SET ARMSCII8
    -> )engine=tianmu;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES(x'616263AC646566');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES(x'FFA5A4ABACA9');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t1;
+----+---------+
| id | a       |
+----+---------+
|  1 | abc-def |
|  2 | '(),-.  |
+----+---------+
2 rows in set (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES('FFA5A4ABACA9');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+--------------+
| id | a            |
+----+--------------+
|  1 | abc-def      |
|  2 | '(),-.       |
|  3 | FFA5A4ABACA9 |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from t1 where a='FFA5A4ABACA9';
+----+--------------+
| id | a            |
+----+--------------+
|  3 | FFA5A4ABACA9 |
+----+--------------+
1 row in set (0.00 sec)

[shangyanwen]

stonedb 5.7_v1.0.1,Reverting to this bug, the problem remains

[adofsauron]

ACK

[adofsauron]

View the contents of the data file for column ARMSCII8

[root@localhost 1]# pwd
/stonedb57/install/data/d64/t1.tianmu/columns/1
[root@localhost 1]# 
[root@localhost 1]# hexdump  -C DATA
00000000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004000  07 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00004010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00024000  61 62 63 ac 64 65 66 00  00 00 00 00 00 00 00 00  |abc.def.........|
00024010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00028000  00 00 00 00 00 00 00 07  00 06 00 00 00 00 00 00  |................|
00028010  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00048000  00 00 00 00 00 00 00 61  62 63 ac 64 65 66 ff a5  |.......abc.def..|
00048010  a4 ab ac a9                                       |....|
00048014
[root@localhost 1]# 

[adofsauron]

The problem is with the character set. If innodb's utf8 encoding is used to insert the data, the HEX query will not be able to find it

| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |

[adofsauron]

Compare INNODB engine UTF8 character set

mysql> CREATE TABLE t1 (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     a VARCHAR(100) CHARACTER SET utf8
    -> ) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> INSERT INTO t1 (a) VALUES(x'616263AC646566');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES(x'FFA5A4ABACA9');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 | abc  |
|  2 |      |
+----+------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT *, HEX(a) FROM t1 WHERE a = x'616263AC646566';
Empty set (0.00 sec)

[adofsauron]

Character set problem, not solved, deferred processing

[wisehead]

I suggest close this issue, since ARMSCII8 is not commonly used for Chinese customers.

[Double0101]

ARMSCII8 is a charset for Armenian. It is not commonly used for Chinese customers.