[MDEV-6919] wrong result from subquery - too much optimization? Created: 2014-10-23  Updated: 2015-08-04  Resolved: 2015-08-04

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 5.3.12, 5.5.33a, 5.5.40, 10.0.14
Fix Version/s: 5.5.43, 10.0.18

Type: Bug Priority: Major
Reporter: Christian Boltz Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: None
Environment:

openSUSE 13.1, mariadb-5.5.33-2.2.x86_64 (from official distribution repo)

Issue Links:
Duplicate
duplicates MDEV-6892 WHERE does not apply Closed
is duplicated by MDEV-6972 Left joined subquery gives wrong result Closed
Sprint: 5.5.45

 Description   

MariaDB gives me a wrong result when using a subquery.

The affected query is (scroll down for full reproducer with table structure):

SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;

It looks like MariaDB optimizes the subquery to "always 1" if the other fields from the subquery are unused or only used in the JOIN condition.

The workaround is to add __mailbox_username to the outer SELECT. This adds a superfluous column to the result, but gives the correct result for is_mailbox.

This broke alias deletion in PostfixAdmin, see https://sourceforge.net/p/postfixadmin/bugs/325/

The query works fine in MySQL (I tested on mysql-community-server-5.6.17-3.1.x86_64 on current openSUSE Factory), so this bug is specific to MariaDB.

Full reproducer:

a) create tables and insert some test rows:

CREATE TABLE `alias` (
 
  `address` varchar(255) NOT NULL,
 
  `goto` text NOT NULL,
 
  `domain` varchar(255) NOT NULL,
 
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `active` tinyint(1) NOT NULL DEFAULT '1',
 
  PRIMARY KEY (`address`),
 
  KEY `domain` (`domain`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases' ;
 
 
 
CREATE TABLE `mailbox` (
 
  `username` varchar(255) NOT NULL,
 
  `password` varchar(255) NOT NULL,
 
  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
 
  `maildir` varchar(255) NOT NULL,
 
  `quota` bigint(20) NOT NULL DEFAULT '0',
 
  `local_part` varchar(255) NOT NULL,
 
  `domain` varchar(255) NOT NULL,
 
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `active` tinyint(1) NOT NULL DEFAULT '1',
 
  PRIMARY KEY (`username`),
 
  KEY `domain` (`domain`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes' ;
 
 
 
insert into mailbox (username,domain) values ('mailbox@example.com', 'example.com');
 
 
 
insert into alias (address, goto, domain) values ('alias@example.com', 'foo@example.com', 'example.com'), ('mailbox@example.com', 'mailbox@example.com', 'example.com');

b) now run the query:

SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
 
| address             | is_mailbox | __is_mailbox |
 
| alias@example.com   |          1 |            1 |
 
| mailbox@example.com |          1 |            1 |
 
2 rows in set (0.00 sec)

Needless to say that the result for alias@example.com is wrong.

EXPLAIN clearly shows that the subquery is optimized away:

EXPLAIN SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias
LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias index NULL PRIMARY 257 NULL 2 Using index

Now add __mailbox_username to the outer SELECT:

SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias
LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

address is_mailbox __is_mailbox __mailbox_username
alias@example.com 0 NULL NULL
mailbox@example.com 1 1 mailbox@example.com

2 rows in set (0.01 sec)

The modified query contains the correct result (and a superfluous column in the resultset).

For completeness, here's the EXPLAIN for the working query:

EXPLAIN SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias index NULL PRIMARY 257 NULL 2 Using index
1 SIMPLE mailbox eq_ref PRIMARY PRIMARY 257 bugtest.alias.address 1 Using where; Using index


 Comments   
Comment by Elena Stepanova [ 2014-10-23 ]

Thanks for the report. As a workaround, please try to set optimizer_switch='derived_merge=off'.

It is likely to be a duplicate of MDEV-6892, but I'm assigning it to sanja to make sure that the fix covers both test cases.

Comment by Oleksandr Byelkin [ 2015-08-02 ]

It looks like the bug is already fixed:

CREATE TABLE `alias` (
`address` varchar(255) NOT NULL,
`goto` text NOT NULL,
`domain` varchar(255) NOT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`address`),
KEY `domain` (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases' ;
CREATE TABLE `mailbox` (
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 NOT NULL,
`maildir` varchar(255) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT '0',
`local_part` varchar(255) NOT NULL,
`domain` varchar(255) NOT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`username`),
KEY `domain` (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes' ;
insert into mailbox (username,domain) values ('mailbox@example.com', 'example.com');
Warnings:
Warning 1364 Field 'password' doesn't have a default value
Warning 1364 Field 'name' doesn't have a default value
Warning 1364 Field 'maildir' doesn't have a default value
Warning 1364 Field 'local_part' doesn't have a default value
insert into alias (address, goto, domain) values ('alias@example.com', 'foo@example.com', 'example.com'), ('mailbox@example.com', 'mailbox@example.com', 'example.com');
SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias
LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;
address is_mailbox __is_mailbox
alias@example.com 0 NULL
mailbox@example.com 1 1
SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias
LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;
address is_mailbox __is_mailbox __mailbox_username
alias@example.com 0 NULL NULL
mailbox@example.com 1 1 mailbox@example.com
drop table alias, mailbox;

Comment by Elena Stepanova [ 2015-08-04 ]

On revision 20109712aeb3d23e5e975780897ad236cbcd2ddc "(MDEV-6892: WHERE does not apply)":

MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
 
+---------------------+------------+--------------+
 
| address             | is_mailbox | __is_mailbox |
 
+---------------------+------------+--------------+
 
| alias@example.com   |          0 |         NULL |
 
| mailbox@example.com |          1 |            1 |
 
+---------------------+------------+--------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
 
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
 
| id   | select_type | table   | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra                    |
 
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
 
|    1 | SIMPLE      | alias   | index  | NULL          | PRIMARY | 257     | NULL               |    2 |   100.00 | Using index              |
 
|    1 | SIMPLE      | mailbox | eq_ref | PRIMARY       | PRIMARY | 257     | test.alias.address |    1 |   100.00 | Using where; Using index |
 
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
 
2 rows in set, 1 warning (0.00 sec)

On 8cbaafd22b145512cc91f7b512290320849e77bd (the revision right before 20109712aeb3d23e5e975780897ad236cbcd2ddc "(MDEV-6892: WHERE does not apply)")

MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
    -> LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
 
+---------------------+------------+--------------+
 
| address             | is_mailbox | __is_mailbox |
 
+---------------------+------------+--------------+
 
| alias@example.com   |          1 |            1 |
 
| mailbox@example.com |          1 |            1 |
 
+---------------------+------------+--------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | alias | index | NULL          | PRIMARY | 257     | NULL |    2 |   100.00 | Using index |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)

So, I think we can be reasonably sure the bug was fixed along with MDEV-6892.

Generated at Thu Feb 08 07:15:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.