| Bug #95856 | "Can't find record" error in SELECT statement | ||
|---|---|---|---|
| Submitted: | 18 Jun 2019 9:45 | Modified: | 18 Jun 2019 9:52 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.16, 5.7.26 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
[18 Jun 2019 9:52]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
[11 Mar 2024 12:58]
ll t
**Environment Details: **
- **Database Version:** Mysql 8.0.16
- **Operating System:** Linux kali 6.6.9-amd64 #1 SMP PREEMPT_DYNAMIC Kali 6.6.9-1kali1 (2024-01-08) x86_64 GNU/Linux
- **Client Information:**mysql Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
**Steps to Reproduce:**
1. **Original Steps:**
- ```sql
CREATE TABLE t0(c0 INT UNIQUE, c1 INT, c2 INT, c3 INT UNIQUE) ENGINE = MyISAM;
INSERT INTO t0(c0) VALUES(DEFAULT), ("a");
INSERT IGNORE INTO t0(c3) VALUES("a"), (1);
REPLACE INTO t0(c1, c0, c3) VALUES(1, 2, 3), (1, "a", "a");
SELECT (NULL) IN (SELECT t0.c3 FROM t0 WHERE t0.c0);
```
2. **Minimal Reproduction Steps:**
- We find that column c1,c2 are unimportant in this sql,so we try to remove them.
- The key colums are c0 and c3, which lead to the error.
- ```
CREATE TABLE t0(c0 INT UNIQUE, c3 INT UNIQUE) ENGINE = MyISAM;
INSERT INTO t0(c0) VALUES(DEFAULT), ("a");
INSERT IGNORE INTO t0(c3) VALUES("a"), (1);
REPLACE INTO t0( c0, c3) VALUES( 2, 3), ( "a", "a");
SELECT (NULL) IN (SELECT t0.c3 FROM t0 WHERE t0.c0);
```
**Expected Behavior vs. Actual Behavior:**
- **Expected Behavior:** NULL
- **Actual Behavior:**unexpected："ERROR 1032 (HY000): Can't find record in 't0'
Description: A SELECT statement results in an error "ERROR 1032 (HY000): Can't find record in 't0'", which is unexpected. How to repeat: CREATE TABLE t0(c0 INT UNIQUE, c1 INT, c2 INT, c3 INT UNIQUE) ENGINE = MyISAM; INSERT INTO t0(c0) VALUES(DEFAULT), ("a"); INSERT IGNORE INTO t0(c3) VALUES("a"), (1); REPLACE INTO t0(c1, c0, c3) VALUES(1, 2, 3), (1, "a", "a"); SELECT (NULL) IN (SELECT t0.c3 FROM t0 WHERE t0.c0); Note that instead of c0, also other column names can be used.