Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10731

Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)

Details

    • 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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          varun Varun Gupta (Inactive) added a comment -

          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.

          varun Varun Gupta (Inactive) added a comment - 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.
          varun Varun Gupta (Inactive) added a comment - - edited 

          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

          varun Varun Gupta (Inactive) added a comment - - edited 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
          psergei Sergei Petrunia added a comment -

          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.

          psergei Sergei Petrunia added a comment - 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.

          People

            varun Varun Gupta (Inactive)
            lars_teuber Lars Teuber
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.