[MCOL-5074] Crash in SELECT from empty table with count(), CASE and GROUP BY Created: 2022-05-06  Updated: 2023-11-17  Resolved: 2022-07-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 5.6.3, 6.2.1
Fix Version/s: 22.08.1

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2021-17

 Description   

Consider the following simple test:
The version

MariaDB [(none)]> show status like 'columnstore%';
 
+-------------------------+---------+
 
| Variable_name      | Value  |
 
+-------------------------+---------+
 
| Columnstore_commit_hash | 05f7e8f |
 
| Columnstore_version   | 6.2.1  |
 
+-------------------------+---------+
 
2 rows in set (0.002 sec)


 
 
[root@51b31614246a /]# mysql
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 4
 
Server version: 10.6.7-3-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> create database test;
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [(none)]> use test;
 
Database changed
 
CREATE TABLE `d_account` (
 
   `acct_sk` int(11) unsigned NOT NULL,
 
   `acct_id` varchar(128) NOT NULL DEFAULT 'None',
 
   `db_source_sk` int(11) unsigned NOT NULL,
 
   `curr_in` tinyint(4) NOT NULL DEFAULT 1,
 
   `rec_load_dt` datetime NOT NULL,
 
   `rec_eff_dt` datetime NOT NULL,
 
   `rec_exp_dt` datetime NOT NULL,
 
   `prim_comp_id` varchar(36) NOT NULL DEFAULT '0',
 
   `sales_mtn` varchar(21) NOT NULL DEFAULT 'None',
 
   `prim_comp_nm` varchar(255) NOT NULL DEFAULT 'None',
 
   `prim_comp_org_cntct_frst_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_comp_org_cntct_lst_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_comp_org_cntct_eml` varchar(255) NOT NULL DEFAULT 'None',
 
   `prim_comp_org_cntct_telephone` varchar(50) NOT NULL DEFAULT 'None',
 
   `prim_comp_org_cntct_telephone_ext` varchar(50) NOT NULL DEFAULT 'None',
 
   `prim_comp_tech_cntct_frst_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_comp_tech_cntct_lst_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_comp_tech_cntct_eml` varchar(255) NOT NULL DEFAULT 'None',
 
   `prim_comp_tech_cntct_telephone` varchar(50) NOT NULL DEFAULT 'None',
 
   `prim_comp_tech_cntct_telephone_ext` varchar(50) NOT NULL DEFAULT 'None',
 
   `prim_billing_cntct_first_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_billing_cntct_lst_nm` varchar(128) NOT NULL DEFAULT 'None',
 
   `prim_billing_cntct_eml` varchar(255) NOT NULL DEFAULT 'None',
 
   `prim_billing_cntct_off_phone` varchar(255) NOT NULL DEFAULT 'None',
 
   `emp_id` varchar(128) NOT NULL DEFAULT 'None',
 
   `emp_sk` int(11) unsigned NOT NULL DEFAULT 0,
 
   `cust_addr_1` varchar(128) NOT NULL DEFAULT 'None',
 
   `cust_addr_2` varchar(128) NOT NULL DEFAULT 'None',
 
   `cust_city` varchar(128) NOT NULL DEFAULT 'None',
 
   `cust_st` varchar(128) NOT NULL DEFAULT 'None',
 
   `cust_zip` varchar(40) NOT NULL DEFAULT 'None',
 
   `cust_cntry` varchar(128) NOT NULL DEFAULT 'None',
 
   `cert_central_flag` tinyint(4) NOT NULL DEFAULT 0,
 
   `dcj_cust_flag` tinyint(4) NOT NULL DEFAULT 0,
 
   `partner_subacct_fl` tinyint(4) NOT NULL DEFAULT 0,
 
   `acct_created_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
   `acct_language` varchar(255) NOT NULL DEFAULT 'None',
 
   `created_date` datetime NOT NULL,
 
   `modified_date` datetime NOT NULL,
 
   `hash_key` varchar(256) DEFAULT NULL,
 
   `batch_id` bigint(20) NOT NULL DEFAULT 0,
 
   `partner_flag` tinyint(3) NOT NULL DEFAULT 0,
 
   `acct_status` varchar(64) NOT NULL DEFAULT 'None',
 
   `parent_acct_id` varchar(128) NOT NULL DEFAULT 'None',
 
   `parent_acct_sk` int(11) unsigned NOT NULL DEFAULT 0,
 
   `account_type` varchar(100) NOT NULL DEFAULT 'None',
 
   `wss_imported_acct_fl` tinyint(4) NOT NULL DEFAULT 0,
 
   `oem_account_id` varchar(45) NOT NULL DEFAULT '0',
 
   `acct_close_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
   `referred_acct_fl` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Not a complete view of partner end users',
 
   `customer_subacct_fl` tinyint(4) NOT NULL DEFAULT 0,
 
   `referred_acct_id` varchar(128) NOT NULL DEFAULT '0',
 
   `storefront` varchar(128) NOT NULL DEFAULT 'None',
 
   `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
 
   `emergency_contact_email` varchar(1250) DEFAULT 'None',
 
   `is_testing` varchar(10) NOT NULL DEFAULT 'None',
 
   `all_notifications_contact` varchar(255) NOT NULL DEFAULT 'None',
 
   `active_accts` tinyint(4) NOT NULL DEFAULT 0,
 
   `last_activity_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
   `has_active_contract` tinyint(4) DEFAULT 0,
 
   `org_sk` int(11) unsigned NOT NULL DEFAULT 0,
 
   `discount_rate_nm` varchar(64) NOT NULL DEFAULT 'None',
 
   `discount_rate_descr` text NOT NULL DEFAULT 'None',
 
   `discount_rate_currency` varchar(3) NOT NULL DEFAULT 'USD',
 
   `percent_discount` tinyint(4) NOT NULL DEFAULT 0,
 
   `percent_discount_lifetime` varchar(20) NOT NULL DEFAULT '0',
 
   `dr_created_by_staff_id` int(11) NOT NULL DEFAULT 0,
 
   `referred_acct_sk` int(11) DEFAULT NULL
 
 ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
 
 
 
MariaDB [test]>  select
 
  sales_mtn ,
 
  active_accts,DATE_FORMAT(acct_created_dt, '%Y-%m-%d') act_created_dt,
 
  case when da.sales_mtn = 'Partner' then count( acct_id) end as Partner_active_accts,
 
  case when da.sales_mtn = 'Retail' then count( acct_id) end as Retail_active_accts,
 
  case when da.sales_mtn = 'Mid-Market' then count( acct_id) end as MMKT_active_accts,
 
  case when da.sales_mtn = 'Enterprise' then count( acct_id) end as ENT_active_accts,
 
  case when da.sales_mtn in ('TSG','Trust Solutions Group') then count( acct_id) end +
 
  case when da.sales_mtn = 'Trust Solutions Group' then count( acct_id) end as TSG_active_accounts
 
  from d_account da;
 
 
 
ERROR 2013 (HY000): Lost connection to server during query
 
MariaDB [test]> exit
 
Bye

With empty table we get the crash with the following details in the error log:

[root@51b31614246a /]# tail -140 /var/log/mariadb/columnstore/mariadb-error.log  
2022-05-06  6:24:01 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2022-05-06  6:24:01 0 [Note] InnoDB: 10.6.7 started; log sequence number 52149; transaction id 33
 
2022-05-06  6:24:01 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2022-05-06  6:24:01 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
 
2022-05-06  6:24:01 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
 
2022-05-06  6:24:01 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
 
2022-05-06  6:24:01 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2022-05-06  6:24:01 0 [Note] Server socket created on IP: '::'.
 
2022-05-06  6:24:02 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
2022-05-06  6:24:02 0 [Note] InnoDB: Buffer pool(s) load completed at 220506  6:24:02
 
mariadbd: /usr/include/boost/smart_ptr/shared_ptr.hpp:734: typename boost::detail::sp_member_access<T>::type boost::shared_ptr<T>::operator->() const [with T = execplan::SimpleColumn; typename boost::detail::sp_member_access<T>::type = execplan::SimpleColumn*]: Assertion `px != 0' failed.
 
220506  6:32:09 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.6.7-3-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467735 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fa1770541d8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fa1a39fe3d8 thread_stack 0x49000
 
??:0(my_print_stacktrace)[0x563b976665ae]
 
??:0(handle_fatal_signal)[0x563b9715f5c5]
 
sigaction.c:0(__restore_rt)[0x7fa1a112fc20]
 
:0(__GI_raise)[0x7fa1a0d8f37f]
 
:0(__GI_abort)[0x7fa1a0d79db5]
 
loadmsgcat.c:0(_nl_load_domain.cold.0)[0x7fa1a0d79c89]
 
assert.c:0(.annobin_assert.c_end)[0x7fa1a0d87a76]
 
??:0(cal_impl_if::check_sum_func_item(Item const*, void*))[0x7fa195928473]
 
??:0(cal_impl_if::buildPredicateItem(Item_func*, cal_impl_if::gp_walk_info*))[0x7fa1959420db]
 
??:0(cal_impl_if::gp_walk(Item const*, void*))[0x7fa19595135e]
 
??:0(cal_impl_if::buildParseTree(Item_func*, cal_impl_if::gp_walk_info&, bool&))[0x7fa19592dbb6]
 
??:0(cal_impl_if::buildCaseFunction(Item_func*, cal_impl_if::gp_walk_info&, bool&))[0x7fa195936658]
 
??:0(cal_impl_if::buildFunctionColumn(Item_func*, cal_impl_if::gp_walk_info&, bool&, bool))[0x7fa195936f58]
 
??:0(cal_impl_if::buildReturnedColumn(Item*, cal_impl_if::gp_walk_info&, bool&, bool))[0x7fa195935cbc]
 
??:0(cal_impl_if::buildArithmeticColumn(Item_func*, cal_impl_if::gp_walk_info&, bool&))[0x7fa195934376]
 
??:0(cal_impl_if::buildFunctionColumn(Item_func*, cal_impl_if::gp_walk_info&, bool&, bool))[0x7fa195936d62]
 
??:0(cal_impl_if::getSelectPlan(cal_impl_if::gp_walk_info&, st_select_lex&, boost::shared_ptr<execplan::CalpontSelectExecutionPlan>&, bool, bool, std::vector<Item*, std::allocator<Item*> > const&))[0x7fa195945261]
 
??:0(cal_impl_if::cs_get_select_plan(ha_columnstore_select_handler*, THD*, boost::shared_ptr<execplan::CalpontSelectExecutionPlan>&, cal_impl_if::gp_walk_info&))[0x7fa195948d16]
 
??:0(ha_mcs_impl_pushdown_init(mcs_handler_info*, TABLE*))[0x7fa1958fd148]
 
??:0(create_columnstore_select_handler(THD*, st_select_lex*))[0x7fa1958e473e]
 
??:0(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x563b96facb13]
 
??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x563b96fad2ae]
 
??:0(LEX::mark_first_table_as_inserting())[0x563b96f49b6f]
 
??:0(mysql_execute_command(THD*, bool))[0x563b96f521da]
 
??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x563b96f45507]
 
??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x563b96f5005e]
 
??:0(do_command(THD*, bool))[0x563b96f50d65]
 
??:0(do_handle_one_connection(CONNECT*, bool))[0x563b9704bbf7]
 
??:0(handle_one_connection)[0x563b9704bf3d]
 
??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x563b97379b7e]
 
pthread_create.c:0(start_thread)[0x7fa1a112517a]
 
:0(__GI___clone)[0x7fa1a0e54dc3]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fa17707daf0): select
 
sales_mtn ,
 
active_accts,DATE_FORMAT(acct_created_dt, '%Y-%m-%d') act_created_dt,
 
case when da.sales_mtn = 'Partner' then count( acct_id) end as Partner_active_accts,
 
case when da.sales_mtn = 'Retail' then count( acct_id) end as Retail_active_accts,
 
case when da.sales_mtn = 'Mid-Market' then count( acct_id) end as MMKT_active_accts,
 
case when da.sales_mtn = 'Enterprise' then count( acct_id) end as ENT_active_accts,
 
case when da.sales_mtn in ('TSG','Trust Solutions Group') then count( acct_id) end +
 
case when da.sales_mtn = 'Trust Solutions Group' then count( acct_id) end as TSG_active_accounts
 
from d_account da
 
 
 
Connection ID (thread ID): 4
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=on,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off
 
 
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /var/lib/mysql
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             unlimited            unlimited            processes 
Max open files            1048576              1048576              files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       14512                14512                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E
 
 
 
220506 06:32:11 mysqld_safe Number of processes running now: 0
 
220506 06:32:11 mysqld_safe mysqld restarted
 
2022-05-06  6:32:11 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 994 ...
 
220506  6:32:11 Columnstore: Started; Version: 6.2.1-1
 
2022-05-06  6:32:11 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2022-05-06  6:32:11 0 [Note] InnoDB: Number of pools: 1
 
2022-05-06  6:32:11 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2022-05-06  6:32:11 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
 
2022-05-06  6:32:11 0 [Note] InnoDB: Using Linux native AIO
 
2022-05-06  6:32:11 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
 
2022-05-06  6:32:11 0 [Note] InnoDB: Completed initialization of buffer pool
 
2022-05-06  6:32:12 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=52149,52149
 
2022-05-06  6:32:12 0 [Note] InnoDB: 128 rollback segments are active.
 
2022-05-06  6:32:12 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
 
2022-05-06  6:32:12 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2022-05-06  6:32:12 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2022-05-06  6:32:12 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2022-05-06  6:32:12 0 [Note] InnoDB: 10.6.7 started; log sequence number 52513; transaction id 33
 
2022-05-06  6:32:12 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
 
2022-05-06  6:32:12 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2022-05-06  6:32:12 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
 
2022-05-06  6:32:12 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.
 
2022-05-06  6:32:12 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2022-05-06  6:32:12 0 [Note] Server socket created on IP: '::'.
 
2022-05-06  6:32:12 0 [Note] InnoDB: Buffer pool(s) load completed at 220506  6:32:12
 
2022-05-06  6:32:12 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
[root@51b31614246a /]#



 Comments   
Comment by David Hall (Inactive) [ 2022-06-20 ]

I've narrowed a test case down to this:

 select
 
  sales_mtn,
 
  case when da.sales_mtn in ('TSG','Trust Solutions Group') then count( acct_id) end
 
  from d_account da group by sales_mtn;

This implies the issue lies with including the IN clause in the WHEN

Leaving out that particular CASE line

 select
 
  sales_mtn ,
 
  active_accts,DATE_FORMAT(acct_created_dt, '%Y-%m-%d') act_created_dt,
 
  case when da.sales_mtn = 'Partner' then count( acct_id) end as Partner_active_accts,
 
  case when da.sales_mtn = 'Retail' then count( acct_id) end as Retail_active_accts,
 
  case when da.sales_mtn = 'Mid-Market' then count( acct_id) end as MMKT_active_accts,
 
  case when da.sales_mtn = 'Enterprise' then count( acct_id) end as ENT_active_accts,
 
  case when da.sales_mtn = 'Trust Solutions Group' then count( acct_id) end as TSG_active_accounts
 
  from d_account da group by sales_mtn, active_accts, act_created_dt;
 
Empty set (0.109 sec)

Doesn't crash. Note that I added the required GROUP BY fields

Comment by David Hall (Inactive) [ 2022-06-20 ]

Somewhere in the building of the select plan, we're being presented with a NULL pointer for a value the code expects to be there. I will attempt to narrow down how we get to this state.

Comment by Daniel Lee (Inactive) [ 2022-07-15 ]

Build tested: 22.08-1 (#4998)

For the reported test case, it no longer crash, but returned the following error instead:

ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`da`.`sales_mtn`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.

Innodb returned the following:

+-----------+--------------+----------------+----------------------+---------------------+-------------------+------------------+---------------------+
 
| sales_mtn | active_accts | act_created_dt | Partner_active_accts | Retail_active_accts | MMKT_active_accts | ENT_active_accts | TSG_active_accounts |
 
+-----------+--------------+----------------+----------------------+---------------------+-------------------+------------------+---------------------+
 
| NULL      |         NULL | NULL           |                 NULL |                NULL |              NULL |             NULL |                NULL |
 
+-----------+--------------+----------------+----------------------+---------------------+-------------------+------------------+---------------------+
 
1 row in set (0.001 sec)

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