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)

    XMLWordPrintable

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

          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.