Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.33a, 5.5.40, 10.0.14
-
None
-
openSUSE 13.1, mariadb-5.5.33-2.2.x86_64 (from official distribution repo)
-
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 |
