[MDEV-8726] IF(x, SUM(y), z) returns {0, NULL} and mangles results Created: 2015-09-02  Updated: 2015-10-23  Resolved: 2015-09-04

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0.21, 10.1.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 0
Labels: None


 Description   

When executing a query that contains IF(x, SUM(\y), z), the results sometimes get mangled because the operation returns

{0, NULL}

for a single column in MariaDB.

For example:

MariaDB [db1]> SELECT 'marker1',
 
    ->   IF(1, SUM(1), 1) AS IF_SUM1,
 
    ->   'marker2',
 
    ->   IF(1, SUM(2), 2) AS IF_SUM2,
 
    ->   'marker3',
 
    ->   'marker4'
 
    -> FROM mysql.user t1
 
    -> WHERE t1.max_questions = 1;
 
+---------+---------+---------+---------+---------+---------+
 
| marker1 | IF_SUM1 | marker2 | IF_SUM2 | marker3 | marker4 |
 
+---------+---------+---------+---------+---------+---------+
 
| marker1 |       0 | NULL    | marker2 | 0       | NULL    |
 
+---------+---------+---------+---------+---------+---------+
 
1 row in set (0.00 sec)

The same query works normally on MySQL 5.5:

mysql> SELECT 'marker1',
 
    ->   IF(1, SUM(1), 1) AS IF_SUM1,
 
    ->   'marker2',
 
    ->   IF(1, SUM(2), 2) AS IF_SUM2,
 
    ->   'marker3',
 
    ->   'marker4'
 
    -> FROM mysql.user t1
 
    -> WHERE t1.max_questions = 1;
 
+---------+---------+---------+---------+---------+---------+
 
| marker1 | IF_SUM1 | marker2 | IF_SUM2 | marker3 | marker4 |
 
+---------+---------+---------+---------+---------+---------+
 
| marker1 |    NULL | marker2 |    NULL | marker3 | marker4 |
 
+---------+---------+---------+---------+---------+---------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Sergei Petrunia [ 2015-09-03 ]

GeoffMontee, can you submit the dump of the dataset that you were using? Also, the testcase uses mysql.user. Is it essential that a system table is used or the issue can be repeated with non-system table as well?

Comment by Geoff Montee (Inactive) [ 2015-09-03 ]

Hi psergey,

This can be reproduced with non-system tables as well, but I shouldn't attach the dump to the JIRA, since it is customer data. I'll send you an email with the relevant information.

Comment by Elena Stepanova [ 2015-09-03 ]

GeoffMontee, psergey,
I think we already have this one, MDEV-8663, at least it looks the same to me. Does everyone agree?

Comment by Oleksandr Byelkin [ 2015-09-03 ]

I've fixed, MDEV-8663, so please send me test suite, or check is that fix fixes this problem please.

Comment by Geoff Montee (Inactive) [ 2015-09-03 ]

Hi sanja,

I've emailed you the data that I used to reproduce this issue.

Comment by Oleksandr Byelkin [ 2015-09-04 ]

It is a duplicate of MDEV-8663 (I checked, the problem fixed bug the fix of MDEV-8663).

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