Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.17, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
- Windows 7
- Tested with MariaDB versions: 10.1.10, 10.1.13, 10.1.14, 10.1.17
-
10.1.21
Description
Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
Reproduce:
DROP TABLE IF EXISTS a; |
DROP TABLE IF EXISTS b; |
CREATE TABLE a ( |
a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
b_id INT(20) UNSIGNED NULL DEFAULT NULL, |
c_id VARCHAR(255) NULL DEFAULT NULL, |
PRIMARY KEY (a_id) |
)
|
COLLATE = 'utf8_general_ci' |
ENGINE = InnoDB;
|
|
CREATE TABLE b ( |
b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
c_id VARCHAR(255) NULL DEFAULT NULL, |
PRIMARY KEY (b_id), |
INDEX idx_c_id (c_id) |
)
|
COLLATE = 'utf8_general_ci' |
ENGINE = InnoDB;
|
|
INSERT INTO a (b_id, c_id) VALUES (NULL, NULL); |
|
INSERT INTO b (c_id) VALUES (NULL); |
INSERT INTO b (c_id) VALUES (NULL); |
|
UPDATE a SET b_id = (SELECT b.b_id FROM b b WHERE b.c_id = a.c_id); |
Expected result
- No error
Actual result
- "Subquery returns more than 1 row"
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
Reproduce: {code:sql} DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; CREATE TABLE a ( a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, b_id INT(20) UNSIGNED NULL DEFAULT NULL, c_id VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (a_id) ) COLLATE = 'utf8_general_ci' ENGINE = InnoDB; CREATE TABLE b ( b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, c_id VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (b_id), INDEX idx_c_id (c_id) ) COLLATE = 'utf8_general_ci' ENGINE = InnoDB; INSERT INTO a (b_id, c_id) VALUES (NULL, NULL); INSERT INTO b (c_id) VALUES (NULL); INSERT INTO b (c_id) VALUES (NULL); UPDATE a SET b_id = (SELECT b.b_id FROM b b WHERE b.c_id = a.c_id); {code} *Expected result: * - No error *Actual result: * - "Subquery returns more than 1 row" |
Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
Reproduce: {code:sql} DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; CREATE TABLE a ( a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, b_id INT(20) UNSIGNED NULL DEFAULT NULL, c_id VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (a_id) ) COLLATE = 'utf8_general_ci' ENGINE = InnoDB; CREATE TABLE b ( b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, c_id VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (b_id), INDEX idx_c_id (c_id) ) COLLATE = 'utf8_general_ci' ENGINE = InnoDB; INSERT INTO a (b_id, c_id) VALUES (NULL, NULL); INSERT INTO b (c_id) VALUES (NULL); INSERT INTO b (c_id) VALUES (NULL); UPDATE a SET b_id = (SELECT b.b_id FROM b b WHERE b.c_id = a.c_id); {code} *Expected result* - No error *Actual result* - "Subquery returns more than 1 row" |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Component/s | Data Manipulation - Subquery [ 10107 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Component/s | Platform Windows [ 10138 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Assignee | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun [ varun ] |
| Sprint | 10.1.21 [ 130 ] |
| Rank | Ranked higher |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1.22 [ 22502 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 76895 ] | MariaDB v4 [ 150860 ] |