[MDEV-10731] Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242) Created: 2016-09-02  Updated: 2017-02-12  Resolved: 2017-02-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Storage Engine - InnoDB
Affects Version/s: 10.1.17, 5.5, 10.0, 10.1
Fix Version/s: 10.1.22

Type: Bug Priority: Major
Reporter: Lars Teuber Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:
  • Windows 7
  • Tested with MariaDB versions: 10.1.10, 10.1.13, 10.1.14, 10.1.17

Sprint: 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"

 Comments   
Comment by Elena Stepanova [ 2016-09-03 ]

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.

Comment by Varun Gupta (Inactive) [ 2017-01-11 ]

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.

Comment by Varun Gupta (Inactive) [ 2017-01-11 ] 

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

Comment by Sergei Petrunia [ 2017-01-11 ]

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.

Generated at Thu Feb 08 07:44:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.