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 |
Breakpoint 6, evaluate_join_record (join=0x10ca35af0, join_tab=0x10ca37278, error=0) at /Users/varun/MariaDB/10.1/10.1/sql/sql_select.cc:18375
p join_tab->select_cond
$115 = (COND *) 0x0
join_tab->select_cond should not be NULL for the above query.
EXPLAIN
|
UPDATE a SET b_id = (SELECT b.b_id FROM b WHERE b.c_id = a.c_id);
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY a index NULL PRIMARY 4 NULL 1 |
2 DEPENDENT SUBQUERY b ref idx_c_id idx_c_id 768 test.a.c_id 1 Using index |
Discussion results:
It does make an index lookup:
EXPLAIN
|
UPDATE a SET b_id = (SELECT b.b_id FROM b WHERE b.c_id = a.c_id);
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY a index NULL PRIMARY 4 NULL 1
|
2 DEPENDENT SUBQUERY b ref idx_c_id idx_c_id 768 test.a.c_id 1 Using index
|
and the index lookup searches for NULL values (which it should not do because
they won't satisfy b.c_id = a.c_id.
If one changes the UPDATE to be a SELECT, then the error disappears: the
optimizer does check for NULL before making the lookup, and doesn't produce
wrong result.
Still need to figure out why does SELECT makes the NULL check while UPDATE
doesn't.
Need to look at why JOIN::outer_ref_cond is NULL for UPDATE, but not NULL for
SELECT.
| 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 ] |
Thanks for the report.
Reproducible with current 5.5-10.2.
Not reproducible with MySQL 5.5-5.7.
WIth MyISAM it works okay.
EITS doesn't seem to help.