the result of prepare is different from that of direct query

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
DROP TABLE IF EXISTS `IDT_MC10146`;
CREATE TABLE `IDT_MC10146` (
  `COL1` bit(4) DEFAULT NULL,
  `COL2` bit(4) DEFAULT NULL,
  `COL3` bit(4) DEFAULT NULL,
  KEY `U_M_COL` (`COL1`,`COL2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `IDT_MC10146` VALUES (0x0F, 0x00, 0x08);
prepare stmt from 'select * from IDT_MC10146 where col1 in (?);';
set @a=0x3135;
execute stmt using@a;
select * from IDT_MC10146 where col1 in (0x3135);

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

MySQL [test]> prepare stmt from 'select * from IDT_MC10146 where col1 in (?);';
Query OK, 0 rows affected (0.001 sec)
Statement prepared

MySQL [test]> set @a=0x3135;
Query OK, 0 rows affected (0.000 sec)

MySQL [test]> execute stmt using@a;
Empty set (0.000 sec)

MySQL [test]> select * from IDT_MC10146 where col1 in (0x3135);
Empty set (0.001 sec)

3. What did you see instead (Required)

mysql> prepare stmt from 'select * from IDT_MC10146 where col1 in (?);';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=0x3135;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using@a;
+------------+------------+------------+
| COL1       | COL2       | COL3       |
+------------+------------+------------+
| 0x0F       | 0x00       | 0x08       |
+------------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from IDT_MC10146 where col1 in (0x3135);
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

master 745d20a
release-5.0-rc 64994e9
release-4.0 8e68350

[guo-shaoge]

/assign @guo-shaoge

[guo-shaoge]

Looks like same problem: #23125

[guo-shaoge]

root cause: When rewrite GetVar expr, we use ETString as result type. (type of session var a is TypeVarString, we deduce result type of GetVar expr is string).

So we convert 0x3135 as string, which is "15", which equals 0x0F.

Fix suggestion: But we don't have BinaryLiteral/HexLiteral type in EvalType(only ETInt, ETReal, ETString...). So result type of any expr cannot be BinaryLiteral/HexLiteral. (see scalar_function.go:Eval(). Result type of an expression is related to eval type)

Maybe we should use result type of an expression to choose evalXXX function. Then add evalHex function for GetVar expr.

PS: But the better way maybe no type switch in runtime, use function pointer to eval. These two issues(#14370 #23114) are also related. Maybe we should consider changing evalXXX function when we refactor our engine.

[guo-shaoge]

If IDT_MC10146 has no index on COL1, result is different. Report a bug for MySQL. bug report

version: Server version: 8.0.23 MySQL Community Server - GPL

drop table t1_no_idx;
create table t1_no_idx(id int, col_bit bit(16));
insert into t1_no_idx values(1, 0x3135);
insert into t1_no_idx values(2, 0x0f);
prepare stmt from 'select * from t1_no_idx where col_bit = ?';                                                                                                                                                                                                                set @a = 0x3135;
execute stmt using @a;
prepare stmt1 from 'select * from t2 where col_bit in (?)';
execute stmt1 using @a;
mysql> execute stmt1 using @a;
+------+------------------+
| id   | col_bit          |
+------+------------------+
|    2 | 0x000F           |
+------+------------------+
1 row in set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions