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

Semi join produce wrong results

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4, 11.8
    • 11.4, 11.8
    • Optimizer
    • None
    • ubuntu 22.04

    Description

      CREATE TABLE IF NOT EXISTS t0(c0 FLOAT);
      CREATE TABLE IF NOT EXISTS t1 LIKE t0;
      SET SESSION internal_tmp_mem_storage_engine = MEMORY;
      DROP INDEX c0 ON t1;
      INSERT INTO t1(c0) VALUES(NULL);
      INSERT INTO t1(c0) VALUES(NULL);
      INSERT INTO t1(c0) VALUES(1);
      INSERT INTO t1(c0) VALUES(2);
      UPDATE t1 SET c0=(IF(t1.c0, NULL, t1.c0)) IS FALSE;
      UPDATE t1 SET c0=COALESCE((- (t1.c0)), (3) IS TRUE);
      INSERT INTO t0(c0) VALUES(NULL);
      INSERT INTO t0(c0) VALUES(NULL);
      INSERT INTO t0(c0) VALUES("0");
      INSERT INTO t0(c0) VALUES(4);
      INSERT INTO t0(c0) VALUES(5);
      INSERT INTO t0(c0) VALUES(6);

      SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
      –

      { NULL, NULL, 0, 4, 5, 6 }
      SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
      --{ 0 }
      SELECT t0.c0 FROM t0;
      –{ NULL, NULL, 0, 4, 5, 6 }

      When using the same join conditions, the output of semi-join and anti-join are contradictory. This indicates that semi join produce wrong results.

      Attachments

        Activity

          elenst Elena Stepanova added a comment -

          In simpler terms:

          CREATE TABLE t0 (c0 FLOAT);
          		 
          INSERT INTO t0 VALUES (NULL),(NULL),(0),(4);
          		 
           
          		 
          CREATE TABLE t1 (c0 FLOAT);
          		 
          INSERT INTO t1(c0) VALUES(0),(0);
          		 
          UPDATE t1 SET c0 = -c0;
          		 
           
          		 
          SELECT t0.c0, t1.c0 FROM t1, t0 WHERE t0.c0 = t1.c0;
          		 
           
          		 
          SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
          		 
          SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);

          11.4 ef966af801afc2a07222b5df65dddd52c77431dd

          		 
          SELECT t0.c0, t1.c0 FROM t1, t0 WHERE t0.c0 = t1.c0;
          		 
          c0	c0
          		 
          0	-0
          		 
          0	-0
          		 
          SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
          		 
          c0
          		 
          NULL
          		 
          NULL
          		 
          0
          		 
          4
          		 
          SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
          		 
          c0
          		 
          0

          The issue here is the 0 row which appears in both result sets. So, it's about comparing float 0 to -0.
          I'll leave it to psergei or maybe bar to decide how much of a problem it is.

          The difference currently shows with default settings on 11.4 and higher, originally starting from 11.0, but that's probably because the plan changed in 11.0 along with big optimizer changes. I suppose if the same plans are triggered on earlier versions, the result could be the same.

          11.4 ef966af801afc2a07222b5df65dddd52c77431dd

          		 
          explain extended SELECT t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
          		 
          id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
          		 
          1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
          		 
          2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
          		 
          Warnings:
          		 
          Note	1276	Field or reference 'test.t0.c0' of SELECT #2 was resolved in SELECT #1
          		 
          Note	1003	/* select#1 */ select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where !(`test`.`t0`.`c0` is not null and <expr_cache><`test`.`t0`.`c0`>(<in_optimizer>(`test`.`t0`.`c0`,`test`.`t0`.`c0` in ( <materialize> (/* select#2 */ select `test`.`t1`.`c0` from `test`.`t1` where `test`.`t1`.`c0` is not null ), <primary_index_lookup>(`test`.`t0`.`c0` in <temporary table> on distinct_key where `test`.`t0`.`c0` = `<subquery2>`.`c0`)))))
          		 
          explain extended SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0);
          		 
          id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
          		 
          1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	
          1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
          		 
          Warnings:
          		 
          Note	1276	Field or reference 'test.t0.c0' of SELECT #2 was resolved in SELECT #1
          		 
          Note	1003	select `test`.`t0`.`c0` AS `c0` from `test`.`t0` semi join (`test`.`t1`) where `test`.`t1`.`c0` = `test`.`t0`.`c0`

          elenst Elena Stepanova added a comment - In simpler terms: CREATE TABLE t0 (c0 FLOAT ); INSERT INTO t0 VALUES ( NULL ),( NULL ),(0),(4);   CREATE TABLE t1 (c0 FLOAT ); INSERT INTO t1(c0) VALUES (0),(0); UPDATE t1 SET c0 = -c0;   SELECT t0.c0, t1.c0 FROM t1, t0 WHERE t0.c0 = t1.c0;   SELECT t0.c0 FROM t0 WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); SELECT t0.c0 FROM t0 WHERE EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); 11.4 ef966af801afc2a07222b5df65dddd52c77431dd SELECT t0.c0, t1.c0 FROM t1, t0 WHERE t0.c0 = t1.c0; c0 c0 0 -0 0 -0 SELECT t0.c0 FROM t0 WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); c0 NULL NULL 0 4 SELECT t0.c0 FROM t0 WHERE EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); c0 0 The issue here is the 0 row which appears in both result sets. So, it's about comparing float 0 to -0. I'll leave it to psergei or maybe bar to decide how much of a problem it is. The difference currently shows with default settings on 11.4 and higher, originally starting from 11.0, but that's probably because the plan changed in 11.0 along with big optimizer changes. I suppose if the same plans are triggered on earlier versions, the result could be the same. 11.4 ef966af801afc2a07222b5df65dddd52c77431dd explain extended SELECT t0.c0 FROM t0 WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 4 100.00 Using where 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t0.c0' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where !(`test`.`t0`.`c0` is not null and <expr_cache><`test`.`t0`.`c0`>(<in_optimizer>(`test`.`t0`.`c0`,`test`.`t0`.`c0` in ( <materialize> ( /* select#2 */ select `test`.`t1`.`c0` from `test`.`t1` where `test`.`t1`.`c0` is not null ), <primary_index_lookup>(`test`.`t0`.`c0` in < temporary table > on distinct_key where `test`.`t0`.`c0` = `<subquery2>`.`c0`))))) explain extended SELECT t0.c0 FROM t0 WHERE EXISTS ( SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 4 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 50.00 Using where ; FirstMatch(t0); Using join buffer (flat, BNL join ) Warnings: Note 1276 Field or reference 'test.t0.c0' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t0`.`c0` AS `c0` from `test`.`t0` semi join (`test`.`t1`) where `test`.`t1`.`c0` = `test`.`t0`.`c0`

          People

            psergei Sergei Petrunia
            jinhui lai jinhui lai
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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