[MDEV-36215] The projection operation caused a logical error - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.7.2, 10.5, 10.6, 10.11, 11.4, 11.8
Fix Version/s: 10.11, 11.4, 11.8
Component/s: None
Labels:
None
Environment:
docker

Description

These two equivalent queries produce contradictory results:

SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

sudo docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest

CREATE TABLE t0(c0 FLOAT);

CREATE TABLE t1(c1 FLOAT);

INSERT INTO t0 VALUES (NULL), (NULL);

INSERT INTO t1 VALUES (1.0);

CREATE UNIQUE INDEX i0 USING BTREE ON t0(c0);

SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

+------+

| c1   |

+------+

|    1 |

+------+

1 row in set (0.001 sec)

SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

+------+------+

| c1   | c0   |

+------+------+

|    1 | NULL |

|    1 | NULL |

+------+------+

2 rows in set (0.001 sec)

Attachments

Activity

Alice Sherepa added a comment - 2025-03-04 12:48

Thanks! I repeated as described on 10.5-11.8

MariaDB [test]> explain extended SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |

+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL  | 1    |   100.00 |                          |

|    1 | SIMPLE      | t0    | ref  | i0            | i0   | 5       | const | 2    |   100.00 | Using where; Using index |

+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+

2 rows in set, 1 warning (0,003 sec)

Note (Code 1003): select `test`.`t1`.`c1` AS `c1`,`test`.`t0`.`c0` AS `c0` from `test`.`t1` left join `test`.`t0` on(`test`.`t0`.`c0` is null) where 1

MariaDB [test]> explain extended SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 |       |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0,002 sec)

Note (Code 1003): select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1

MariaDB [test]> alter table t0 drop index i0;

Query OK, 0 rows affected (0,036 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]>  SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);

+------+

| c1   |

+------+

|    1 |

|    1 |

+------+

2 rows in set (0,005 sec)

Alice Sherepa added a comment - 2025-03-04 12:48 Thanks! I repeated as described on 10.5-11.8 MariaDB [test]> explain extended SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL); +------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | | 1 | SIMPLE | t0 | ref | i0 | i0 | 5 | const | 2 | 100.00 | Using where; Using index | +------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0,003 sec) Note (Code 1003): select `test`.`t1`.`c1` AS `c1`,`test`.`t0`.`c0` AS `c0` from `test`.`t1` left join `test`.`t0` on(`test`.`t0`.`c0` is null) where 1 MariaDB [test]> explain extended SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL); +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0,002 sec) Note (Code 1003): select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1 MariaDB [test]> alter table t0 drop index i0; Query OK, 0 rows affected (0,036 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL); +------+ | c1 | +------+ | 1 | | 1 | +------+ 2 rows in set (0,005 sec)

People

Assignee:: Sergei Petrunia

Reporter:: jinhui lai

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2025-03-04 10:09

Updated:: 2025-03-05 08:08

Git Integration

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

MariaDB Server