bug: Query result set error，TPCH(10G) Q11

[shangyanwen]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

##The following error occurs when running Q11 on stonedb

ERROR 1105 (HY000): Numeric result of an expression is too large and cannot be handled by tianmu. Please use an explicit cast to a data type handled by tianmu, e.g. CAST(<expr> AS DECIMAL(18,6)).

Expected behavior

The query result set should appear empty

How To Reproduce

1、Build the test environment of TPCH ,Download 10 GB test data address:
2、Example Import 10 GB data
3、Execute the following SQL

select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'MOZAMBIQUE'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.0001000000
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = 'MOZAMBIQUE'
                )
order by
        value desc;

Environment

[root@localhost bin]# ./mysql --version
./mysql  Ver 14.14 Distrib 5.7.36-StoneDB, for Linux (x86_64) using  EditLine wrapper
[root@localhost bin]# ./mysqld --version
./mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow: 
        Repository address: https://github.com/stoneatom/stonedb.git:HEAD
        Branch name: HEAD
        Last commit ID: b44a51ce8
        Last commit time: Date:   Fri Dec 2 16:11:06 2022 +0000
        Build time: Date: Mon Dec  5 06:12:44 UTC 2022

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[shangyanwen]

Address to download 10 GB of test data：http://192.168.30.30/test/tpch_10g_syw/

[isredstar]

v1.0.1-Q11-1G
838 rows in set (0.59 sec)
v1.0.2-Q11-1G
838 rows in set (2.24 sec)
it is consistent

[lujiashun]

#0  decimal2longlong (from=0x7ff93683eac0, to=0x7ff93683eb58)
    at /data/codebase/stonedb/strings/decimal.c:1178
#1  0x0000000001d49d79 in my_decimal2int (mask=4294967295, d=0x7ff93683eb60, unsigned_flag=0 '\000',
    l=0x7ff93683eb58) at /data/codebase/stonedb/sql/my_decimal.h:403
#2  0x00000000030062fc in Tianmu::core::MysqlExpression::ItemDecimal2ValueOrNull (item=0x7ff5ec0140d0,
    dec_scale=12) at /data/codebase/stonedb/storage/tianmu/core/mysql_expression.cpp:515
#3  0x0000000003005fbc in Tianmu::core::MysqlExpression::Evaluate (this=0x7ff5ec0bad30)
    at /data/codebase/stonedb/storage/tianmu/core/mysql_expression.cpp:463
#4  0x0000000002e0a20f in Tianmu::vcolumn::ExpressionColumn::GetValueInt64Impl (this=0x7ff5ec0b7990,
    mit=...) at /data/codebase/stonedb/storage/tianmu/vc/expr_column.cpp:119
#5  0x0000000002d08014 in Tianmu::vcolumn::VirtualColumnBase::GetValueInt64 (this=0x7ff5ec0b7990, mit=...)
    at /data/codebase/stonedb/storage/tianmu/vc/virtual_column_base.h:93
#6  0x0000000002f8a2bd in Tianmu::core::AggregationAlgorithm::AggregateFillOutput (this=0x7ff93683f6a0,
    gbw=..., gt_pos=0, omit_by_offset=@0x7ff93683f728: 0)
    at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:687
#7  0x0000000002f88b77 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (
    this=0x7ff93683f6a0, gbw=..., limit=@0x7ff93683f2f8: 1, offset=@0x7ff93683f728: 0, sender=0x0,
    limit_less_than_no_groups=false, force_parall=false)
    at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:363
#8  0x0000000002f87d94 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7ff93683f6a0,
    just_distinct=false, limit=@0x7ff93683f720: -1, offset=@0x7ff93683f728: 0, sender=0x0)
    at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:204
#9  0x0000000002d04936 in Tianmu::core::TempTable::Materialize (this=0x7ff5ec0b5420, in_subq=false,
    sender=0x0, lazy=false) at /data/codebase/stonedb/storage/tianmu/core/temp_table.cpp:2063
--Type <RET> for more, q to quit, c to continue without paging--
#10 0x0000000002d06304 in Tianmu::core::TempTableForSubquery::Materialize (this=0x7ff5ec0b5420, in_subq=false, sender=0x0, lazy=false)
    at /data/codebase/stonedb/storage/tianmu/core/temp_table.cpp:2305
#11 0x0000000002e1ff60 in Tianmu::vcolumn::SubSelectColumn::PrepareSubqResult (this=0x7ff5ec0b6f90, mit=..., exists_only=false)
    at /data/codebase/stonedb/storage/tianmu/vc/subselect_column.cpp:401
#12 0x0000000002e2026c in Tianmu::vcolumn::SubSelectColumn::IsNullImpl (this=0x7ff5ec0b6f90, mit=...)
    at /data/codebase/stonedb/storage/tianmu/vc/subselect_column.cpp:443
#13 0x0000000002d08046 in Tianmu::vcolumn::VirtualColumnBase::IsNull (this=0x7ff5ec0b6f90, mit=...)
    at /data/codebase/stonedb/storage/tianmu/vc/virtual_column_base.h:112
#14 0x0000000002fc6f94 in Tianmu::core::Descriptor::CheckCondition (this=0x7ff5ecd64f20, mit=...)
    at /data/codebase/stonedb/storage/tianmu/core/descriptor.cpp:1133
#15 0x0000000002fce2a0 in Tianmu::core::DescTreeNode::CheckCondition (this=0x7ff5ecd64f00, mit=...)
    at /data/codebase/stonedb/storage/tianmu/core/descriptor.cpp:2207
#16 0x0000000002f8cac4 in Tianmu::core::TempTable::CheckHavingConditions (this=0x7ff5ec0a6570, it=...)
    at /data/codebase/stonedb/storage/tianmu/core/temp_table.h:366
#17 0x0000000002f8a315 in Tianmu::core::AggregationAlgorithm::AggregateFillOutput (this=0x7ff9368404b0, gbw=..., gt_pos=0,
    omit_by_offset=@0x7ff936840538: 0) at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:694
#18 0x0000000002f88b77 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7ff9368404b0, gbw=...,
    limit=@0x7ff936840108: 313920, offset=@0x7ff936840538: 0, sender=0x0, limit_less_than_no_groups=false, force_parall=false)
    at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:363
#19 0x0000000002f87d94 in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7ff9368404b0, just_distinct=false,
    limit=@0x7ff936840530: -1, offset=@0x7ff936840538: 0, sender=0x0)
    at /data/codebase/stonedb/storage/tianmu/core/aggregation_algorithm.cpp:204
#20 0x0000000002d04936 in Tianmu::core::TempTable::Materialize (this=0x7ff5ec0a6570, in_subq=false, sender=0x7ff5ec0a6450, lazy=false)
    at /data/codebase/stonedb/storage/tianmu/core/temp_table.cpp:2063
#21 0x0000000002c91377 in Tianmu::core::Engine::Execute (this=0x7529be0, thd=0x7ff5ec006010, lex=0x7ff5ec008338,
    result_output=0x7ff5ec022568, unit_for_union=0x0) at /data/codebase/stonedb/storage/tianmu/core/engine_execute.cpp:482
#22 0x0000000002c900d8 in Tianmu::core::Engine::HandleSelect (this=0x7529be0, thd=0x7ff5ec006010, lex=0x7ff5ec008338,
    result=@0x7ff936840cc8: 0x7ff5ec022568, setup_tables_done_option=0, res=@0x7ff936840cc4: 0,
    optimize_after_tianmu=@0x7ff936840cbc: 1, tianmu_free_join=@0x7ff936840cc0: 1, with_insert=0)
    at /data/codebase/stonedb/storage/tianmu/core/engine_execute.cpp:238
#23 0x0000000002d8fb87 in Tianmu::handler::ha_my_tianmu_query (thd=0x7ff5ec006010, lex=0x7ff5ec008338,
    result_output=@0x7ff936840cc8: 0x7ff5ec022568, setup_tables_done_option=0, res=@0x7ff936840cc4: 0,
    optimize_after_tianmu=@0x7ff936840cbc: 1, tianmu_free_join=@0x7ff936840cc0: 1, with_insert=0)
    at /data/codebase/stonedb/storage/tianmu/handler/ha_my_tianmu.cpp:88
#24 0x00000000023d2bf8 in execute_sqlcom_select (thd=0x7ff5ec006010, all_tables=0x7ff5ec015640)
    at /data/codebase/stonedb/sql/sql_parse.cc:5216
#25 0x00000000023cc24f in mysql_execute_command (thd=0x7ff5ec006010, first_level=true) at /data/codebase/stonedb/sql/sql_parse.cc:2855
#26 0x00000000023d3bae in mysql_parse (thd=0x7ff5ec006010, parser_state=0x7ff936841e80) at /data/codebase/stonedb/sql/sql_parse.cc:5654
#27 0x00000000023c9069 in dispatch_command (thd=0x7ff5ec006010, com_data=0x7ff936842610, command=COM_QUERY)
    at /data/codebase/stonedb/sql/sql_parse.cc:1495
#28 0x00000000023c7f7e in do_command (thd=0x7ff5ec006010) at /data/codebase/stonedb/sql/sql_parse.cc:1034
#29 0x00000000024f9083 in handle_connection (arg=0x8f647b0)
    at /data/codebase/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#30 0x0000000002bc7cc1 in pfs_spawn_thread (arg=0x8fada10) at /data/codebase/stonedb/storage/perfschema/pfs.cc:2197
#31 0x00007ff940cfeea5 in start_thread () from /lib64/libpthread.so.0
#32 0x00007ff93e7d1b0d in clone () from /lib64/libc.so.6

[lujiashun]

retun E_DEC_OVERFLOW in decimal.c:1178 LINE

(gdb) list 1155,1178
1155    int decimal2longlong(decimal_t *from, longlong *to)
1156    {
1157      dec1 *buf=from->buf;
1158      longlong x=0;
1159      int intg, frac;
1160
1161      for (intg=from->intg; intg > 0; intg-=DIG_PER_DEC1)
1162      {
1163        longlong y=x;
1164        /*
1165          Attention: trick!
1166          we're calculating -|from| instead of |from| here
1167          because |LLONG_MIN| > LLONG_MAX
1168          so we can convert -9223372036854775808 correctly
1169        */
1170        x=x*DIG_BASE - *buf++;
1171        if (unlikely(y < (LLONG_MIN/DIG_BASE) || x > y))
1172        {
1173          /*
1174            the decimal is bigger than any possible integer
1175            return border integer depending on the sign
1176          */
1177          *to= from->sign ? LLONG_MIN : LLONG_MAX;
1178          return E_DEC_OVERFLOW;
(gdb) p from
$10 = (decimal_t *) 0x7ff93683eac0
(gdb) list 1155,1178
1155    int decimal2longlong(decimal_t *from, longlong *to)
1156    {
1157      dec1 *buf=from->buf;
1158      longlong x=0;
1159      int intg, frac;
1160
1161      for (intg=from->intg; intg > 0; intg-=DIG_PER_DEC1)
1162      {
1163        longlong y=x;
1164        /*
1165          Attention: trick!
1166          we're calculating -|from| instead of |from| here
1167          because |LLONG_MIN| > LLONG_MAX
1168          so we can convert -9223372036854775808 correctly
1169        */
1170        x=x*DIG_BASE - *buf++;
1171        if (unlikely(y < (LLONG_MIN/DIG_BASE) || x > y))
1172        {
1173          /*
1174            the decimal is bigger than any possible integer
1175            return border integer depending on the sign
1176          */
1177          *to= from->sign ? LLONG_MIN : LLONG_MAX;
1178          return E_DEC_OVERFLOW;
(gdb) p *from
$11 = {intg = 27, frac = 0, len = 9, sign = 0 '\000', buf = 0x7ff93683eadc}
(gdb) x/9xw from->buf
0x7ff93683eadc: 0x0000004e      0x260f8d07      0x1cc9fec0      0x00000000
0x7ff93683eaec: 0xffffffff      0x00000000      0x00000000      0x00000014
0x7ff93683eafc: 0x00000000

[lujiashun]

add some debug infromation,78638553351483000000 > 9223372036854775808(LLONG_MAX)

(gdb) list 510,530
510
511         char dbug_buff[DECIMAL_MAX_STR_LENGTH+2];
512         dbug_decimal_as_string(dbug_buff, &dec);
513
514         if (dec_scale == -1)
515           err = my_decimal_shift((uint)-1, &dec, item->decimals <= 18 ? item->decimals : 18);
516         else
517           err = my_decimal_shift((uint)-1, &dec, dec_scale <= 18 ? dec_scale : 18);
518         CheckDecimalError(err);
519
520         char dbug_buff2[DECIMAL_MAX_STR_LENGTH+2];
521         dbug_decimal_as_string(dbug_buff2, &dec);
522
523         err = my_decimal2int((uint)-1, &dec, false, (longlong *)&v);
524         CheckDecimalError(err);
525         val->SetFixed(v);
526       }
527       if (item->null_value)
528         return std::make_shared<ValueOrNull>();
529       return val;
530     }
(gdb) x/s dbug_buff
0x7fdec8203aa0: "78638553.351483000000"
(gdb) x/s dbug_buff2
0x7fdec8203b00: "78638553351483000000"

[lujiashun]

1. int128 to store the decimal,the range is as below, the precision is 38(39)
   −170141183460469231731687303715884105728∼170141183460469231731687303715884105727
2. In Mysql, The maximum number of digits for DECIMAL is 65
3. Refer to CK, the max support precesion is 38，
   http://www.devdoc.net/database/ClickhouseDocs_19.4.1.3-docs/data_types/decimal/

[lujiashun]

@hustjieke

[wisehead]

this issue depends on decimal, it'll be fixed after decimal is supported