[MCOL-944] coalesce with count(distinct) can lead to incorrect results Created: 2017-09-27  Updated: 2017-10-25  Resolved: 2017-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.0.11, 1.1.0
Fix Version/s: 1.0.12, 1.1.1

Type: Bug Priority: Critical
Reporter: David Thompson (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Sprint: 2017-20, 2017-21

 Description   

to reproduce:

CREATE TABLE test( 
id BIGINT(20) NULL DEFAULT NULL, 
integerfield INT(10) UNSIGNED NULL DEFAULT NULL,
 
 integerfield2 INT(10) UNSIGNED NULL DEFAULT NULL )
 
ENGINE=Columnstore;
 
 
 
INSERT INTO test
 
VALUES (1, NULL,1), (2, NULL,1), (1,NULL,1);
 
 
 
SELECT COUNT(distinct id),        
SUM(integerfield),        
SUM(integerfield2),        
coalesce(SUM(integerfield),0),        
coalesce(SUM(integerfield2),0),        
coalesce(SUM(integerfield),0) + coalesce(SUM(integerfield2),0) 
FROM   test;

The last column value is -9223372036854775805 instead of 3. Removing the count(distinct) results in the correct result.

 Comments   
Comment by David Thompson (Inactive) [ 2017-09-27 ]

The very large negative value is almost certainly because null is being interpreted as the internal numeric value for null rather than null.

This also behaves similarly with ifnull so doesn't appear to be function related.

Comment by Andrew Hutchings (Inactive) [ 2017-10-02 ]

problem appears to be the unsigned int null value is being cast to a signed int and then a decimal so loses the null (since signed and unsigned null are different magic values).

Comment by Andrew Hutchings (Inactive) [ 2017-10-02 ]

The count(DISTINCT) was forcing the aggregation step to use signed BIGINT for an unsigned column which was causing NULL values to not work correctly.

Pull requests for 1.0, 1.1 and develop.

For QA, this is how it should look:

SELECT COUNT(distinct id),         SUM(integerfield),         SUM(integerfield2),         coalesce(SUM(integerfield),0),         coalesce(SUM(integerfield2),0),         coalesce(SUM(integerfield),0) + coalesce(SUM(integerfield2),0)  FROM   test\G
 
*************************** 1. row ***************************
 
                                            COUNT(distinct id): 2
 
                                             SUM(integerfield): NULL
 
                                            SUM(integerfield2): 3
 
                                 coalesce(SUM(integerfield),0): 0
 
                                coalesce(SUM(integerfield2),0): 3
 
coalesce(SUM(integerfield),0) + coalesce(SUM(integerfield2),0): 3
 
1 row in set (0.02 sec)

Comment by Daniel Lee (Inactive) [ 2017-10-25 ]

Build verified: Github source for 1.1.1-1

/root/columnstore/mariadb-columnstore-server
commit f6cd94ea167789970db7b5b501569a6549495d10
Merge: 3d846d3 91b2553
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 09:15:58 2017 -0500

Merge pull request #72 from mariadb-corporation/MCOL-982

MCOL-982 Merge MariaDB 10.2.9

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 751f9fbd2f26026983915a0677d6d600be273073
Author: david hill <david.hill@mariadb.com>
Date: Tue Oct 24 14:05:48 2017 -0500

removed duplicaue entries

Build verified: Github source for 1.0.12-1

/root/columnstore/mariadb-columnstore-server
commit a42eb6d1e74e44c9e8fd9bb8290e6ce7dbf909f5
Merge: 2965fc8 6a14ced
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 3 10:12:33 2017 -0500

Merge pull request #69 from mariadb-corporation/MCOL-940

MCOL-940

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 22f5c04f854d8571fa81ac33a26edc256d3acd48
Merge: 2f19dc2 c132f1b
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 17:39:45 2017 -0500

Merge pull request #300 from mariadb-corporation/MCOL-973-1.0

MCOL-973 remove reference to TEXT in the 1.0 code

Verified with test case in ticket description.

Generated at Thu Feb 08 02:24:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.