[MCOL-301] aggregation over boolean expression fails with error 1178 Created: 2016-09-16  Updated: 2017-03-31  Resolved: 2017-01-16

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.2
Fix Version/s: 1.0.7

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

Sprint: 2016-24, 2016-25, 2017-01

 Description   

Pierre-Adrien Coustillas reported on google group:
https://groups.google.com/forum/#!topic/mariadb-columnstore/d-R4wWTXvP8

MariaDB [mcs]> SELECT sum((valeur=1) * (1=1) ) FROM histo__agregats LIMIT 2;
ERROR 1178 (42000): The storage engine for the table doesn't support Un-recognized Arithmetic Operand

A workaround is:
MariaDB [mcs]> SELECT sum(if(valeur=1,1,0) * (1=1) ) FROM histo__agregats LIMIT 2;
--------------------------------

sum(if(valeur=1,1,0) * (1=1) )

--------------------------------

4509127

--------------------------------
1 row in set (0.48 sec)

Can reproduce the behavior.



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-12-16 ]

This Jira is actually two bugs, the first is handing of SUM() with a constant inside the second is the handling of an arithmetic on functions inside an aggregate. There is a commit for each in my pull request.

Suggested test:

create table t1 (a int) engine=columnstore;
insert into t1 values (1),(1),(2),(2),(3),(3);
select sum(1) from t1;
select sum((a=1)*(1=1) from t1;

Before the fix the first query will return '6' and the second query will error.
With the fix the first query should return '1' and the second query should return '2'.

Comment by Daniel Lee (Inactive) [ 2017-01-16 ]

Build verified: Github source

[root@localhost mariadb-columnstore-server]# git show
commit 83b0e5c54a644bc31461752cf73f0e1140586d39
Merge: b975814 93c1c7e
Author: david hill <david.hill@mariadb.com>
Date: Thu Jan 12 09:27:28 2017 -0600

Merge pull request #26 from mariadb-corporation/MCOL-500

Update README.md

MariaDB [mytest]> create table t1 (a int) engine=columnstore;
Query OK, 0 rows affected (0.08 sec)

MariaDB [mytest]> insert into t1 values (1),(1),(2),(2),(3),(3);
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select sum(1) from t1;
--------

sum(1)

--------

1

--------
1 row in set (0.13 sec)

MariaDB [mytest]> select sum((a=1)*(1=1)) from t1;
------------------

sum((a=1)*(1=1))

------------------

2

------------------
1 row in set (0.01 sec)

MariaDB [mytest]> select sum((a=1)*(1=1)) from t1 limit 2;
------------------

sum((a=1)*(1=1))

------------------

2

------------------
1 row in set (0.00 sec)

MariaDB [mytest]>

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