Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
23.02
-
None
-
None
-
None
Description
The query below does not work in CS 23.10.0 or CS 5.6.8 compared to innodb. Both show no warnings when it should
with a as (select c, cc, cast(sum(i) as decimal(10,3)) as ii from test.t group by c, cc) select c, sum(ii) from a group by c order by c; |
However in CS 23.10.0 it returns all 0's, where as in CS 5.6.8 its bogus out of range numbers
Work Around - columnstore_select_handler=OFF
set session columnstore_select_handler=OFF; with a as (select c, cc, cast(sum(i) as decimal(10,3)) as ii from test.t group by c, cc) select c, sum(ii) from a group by c order by c; |
We noticed in CS 23.10.0 when adding the CAST function as used, we get the 0's result, which is not valid.
# Behaves the same in CS 5.8 & CS 23.10.0 without the CAST( as Decimal) that columnstore and innodb produce the same result |
mariadb -e 'with a as (select c, cc, sum(i) as ii from test.t group by c, cc) select c, sum(ii) from a group by c order by c;' |
mariadb -e 'with a as (select c, cc, sum(i) as ii from test.ti group by c, cc) select c, sum(ii) from a group by c order by c;' |
REPRODUCTION
Setup
yum install wget jq -y
|
wget https://raw.githubusercontent.com/mariadb-corporation/mariadb-columnstore-engine/develop/extra/cs_package_manager.sh; chmod +x cs_package_manager.sh; |
bash cs_package_manager.sh install enterprise 10.6.15-10 --token XXXXXXXXXX |
 |
mariadb -e 'create database if not exists test; use test; drop table if exists test.t; drop table if exists test.ti; create table if not exists test.t (c char(1), cc char(1), i int) engine columnstore;create table if not exists test.ti (c char(1), cc char(1), i int) engine innodb;' |
dnf install -y golang
|
go install github.com/StabbyCutyou/moldova/cmd/moldova@latest |
~/go/bin/moldova -t "{ascii:length:1},{ascii:length:1},{int:max:9999999}" -n 10000 | cpimport -s, test t |
mariadb test -e 'insert into ti select * from t;' |
Query Columnstore 23.10.0 - notice 0's for every result
mariadb --show-warnings -e 'with a as (select c, cc, cast(sum(i) as decimal(10,3)) as ii from test.t group by c, cc) select c, sum(ii) from a group by c order by c;'
|
.....
|
| v | 0.000 |
|
| w | 0.000 |
|
| x | 0.000 |
|
| y | 0.000 |
|
+------+---------+
|
Query InnoDB - also notice all the warnings thrown alerting users of the overflow
mariadb --show-warnings -e 'with a as (select c, cc, cast(sum(i) as decimal(10,3)) as ii from test.ti group by c, cc) select c, sum(ii) from a group by c order by c;'
|
.....
|
| v | 346902230.967 |
|
| w | 349354321.966 |
|
| x | 346987098.966 |
|
| y | 349999999.965 |
|
+------+---------------+
|
Warning (Code 1264): Out of range value for column 'ii' at row 1
|
Warning (Code 1264): Out of range value for column 'ii' at row 1
|
In CS 5.6.8 - no all 0's but still wrong result
mariadb --show-warnings -e 'with a as (select c, cc, cast(sum(i) as decimal(10,3)) as ii from test.t group by c, cc) select c, sum(ii) from a group by c order by c;'
|
.....
|
| v | 1541954575.000 |
|
| n | 1400597401.000 |
|
| s | 1526268264.000 |
|
| e | 1296676521.000 |
|
+------+----------------+
|