Thank you for the report!
I repeated as described on 10.9-11.3 with InnoDb engine, 10.4-10.6 returned the expected result.
|
10.9 3e0009dc3a771e4dbf2fa4a |
MariaDB [test]> CREATE TABLE t1 ( id int PRIMARY KEY, number int, name varchar(47), street int , KEY k1 (street,number,name)) engine=innodb ;
|
Query OK, 0 rows affected (0,051 sec)
|
|
|
MariaDB [test]> INSERT INTO t1 (id, number, name, street) VALUES (100733476, 14, NULL, 1115569);
|
Query OK, 1 row affected (0,003 sec)
|
|
|
MariaDB [test]> SELECT b1.id
|
-> FROM t1 b1
|
-> JOIN t1 b2 ON ( b1.street = b2.street AND b1.number <=> b2.number AND b1.name <=> b2.name ) ;
|
Empty set (0,000 sec)
|
|
|
MariaDB [test]> explain extended SELECT b1.id FROM t1 b1 JOIN t1 b2 ON ( b1.street = b2.street AND b1.number <=> b2.number AND b1.name <=> b2.name );
|
+------+-------------+-------+-------+---------------+------+---------+--------------------------------------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+--------------------------------------------+------+----------+--------------------------+
|
| 1 | SIMPLE | b1 | index | k1 | k1 | 60 | NULL | 1 | 100.00 | Using where; Using index |
|
| 1 | SIMPLE | b2 | ref | k1 | k1 | 60 | test.b1.street,test.b1.number,test.b1.name | 1 | 100.00 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+--------------------------------------------+------+----------+--------------------------+
|
2 rows in set, 1 warning (0,001 sec)
|
|
|
Note (Code 1003): select `test`.`b1`.`id` AS `id` from `test`.`t1` `b1` join `test`.`t1` `b2` where `test`.`b2`.`street` = `test`.`b1`.`street` and `test`.`b1`.`number` <=> `test`.`b2`.`number` and `test`.`b1`.`name` <=> `test`.`b2`.`name`
|
|
|
MariaDB [test]> alter table t1 drop index k1;
|
Query OK, 0 rows affected (0,037 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT b1.id
|
-> FROM t1 b1
|
-> JOIN t1 b2 ON ( b1.street = b2.street AND b1.number <=> b2.number AND b1.name <=> b2.name ) ;
|
+-----------+
|
| id |
|
+-----------+
|
| 100733476 |
|
+-----------+
|
1 row in set (0,001 sec)
|
Is there any movement on this issue? This bug basically forbids the use of the "<=>" operator if you want correct results.
not reproducible on current 10.11-11.3, fixed by b9e210bbf3 commit (MDEV-32555)
The original description incorrectly listed 11.0.3 as the last good version, but that should've been 11.0.2.
It seems to have been fixed in 11.0.4 using the example above, great! I can still reproduce it in 11.0.3.
alice I was just narrowing down the commits 
With your result, can we close this ticket?
Gosselin I guess yes, the only reason why I didn't close it - I'm always in doubt about whether it is worth adding the test case for mtr.
alice that is a good point. Since it is assigned to me, I will add a test case for mtr and go through the usual review and delivery process. Thanks!
Latest 10.11 at git sha bdf65893ddb2ce0552a20e53e2358350521d2d35 works correctly, so the Fixed Versions field may be incorrect. As Alice noted earlier, this is a duplicate of another issue. I'll use this ticket to deliver a test for mtr to 10.11.
For whatever reason, Jira isn't picking up the PR, so I'm linking it here:
https://github.com/MariaDB/server/pull/2984