SELECT joining two empty Columnstore tables and involving recursive CTE crashes server

[root@c2eec0bd325e /]# 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

MariaDB [test]> CREATE TABLE `d_currency` (   `currency_sk` int(11) unsigned NOT NULL COMMENT 'autoincrement=1',   `exchange_rate_date` date NOT NULL DEFAULT '0000-00-00',   `from_currency_code` char(3) NOT NULL DEFAULT 'Non',   `from_currency_desc` char(20) NOT NULL DEFAULT 'None',   `to_currency_code` char(3) NOT NULL DEFAULT 'Non',   `exchange_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,   `to_currency_desc` char(20) NOT NULL DEFAULT 'None',   `rate_src` varchar(200) NOT NULL DEFAULT 'None',   `current_year_fl` tinyint(4) NOT NULL DEFAULT 0,   `curr_in` tinyint(4) NOT NULL DEFAULT 1,   `rec_eff_dt` date NOT NULL DEFAULT '1970-01-01',   `rec_exp_dt` date NOT NULL DEFAULT '2099-01-01',   `created_date` datetime NOT NULL,   `modified_date` datetime NOT NULL,   `hash_key` varchar(64) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;

Query OK, 0 rows affected (12.331 sec)

MariaDB [test]> CREATE TABLE `f_bookings` (   `bookings_sk` int(11) unsigned NOT NULL,   `booking_id` varchar(200) NOT NULL DEFAULT '0',   `db_source_sk` int(11) unsigned NOT NULL,   `booking_type` varchar(200) NOT NULL DEFAULT 'None',   `booking_ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `valid_from_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `valid_end_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `status` varchar(200) NOT NULL DEFAULT 'None',   `order_id` varchar(50) NOT NULL DEFAULT 'None',   `ord_sk` int(11) unsigned NOT NULL DEFAULT 0,   `acct_id` varchar(128) NOT NULL DEFAULT 'None',   `acct_sk` int(11) unsigned NOT NULL DEFAULT 0,   `oppor_id` char(18) NOT NULL DEFAULT 'None',   `oppor_sk` int(11) unsigned NOT NULL DEFAULT 0,   `product_id` varchar(128) NOT NULL DEFAULT '0',   `prod_sk` int(11) unsigned NOT NULL DEFAULT 0,   `original_prod_sk` int(11) NOT NULL DEFAULT 0,   `currency_id` varchar(200) NOT NULL DEFAULT 'None',   `qty` int(11) NOT NULL DEFAULT 0,   `purchased_names` int(11) NOT NULL DEFAULT 0,   `purchased_wc_names` int(11) NOT NULL DEFAULT 0,   `local_amt` decimal(10,2) NOT NULL DEFAULT 0.00,   `sales_rep` varchar(50) NOT NULL DEFAULT 'None',   `order_platform` varchar(200) NOT NULL DEFAULT 'None',   `lifetime` int(11) NOT NULL DEFAULT 0,   `tax_amt` decimal(10,2) NOT NULL DEFAULT 0.00,   `multi_yr_fl` tinyint(4) NOT NULL DEFAULT 0,   `multi_yr_valid_till_dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `pymnt_mthd` varchar(200) NOT NULL DEFAULT 'None',   `emp_sk` int(11) NOT NULL DEFAULT 0,   `booking_sales_mtn` varchar(200) NOT NULL DEFAULT 'None',   `bus_unit` varchar(20) NOT NULL DEFAULT 'None',   `created_date` datetime NOT NULL,   `modified_date` datetime NOT NULL,   `batch_id` bigint(20) NOT NULL DEFAULT 0,   `partner_rep` varchar(200) NOT NULL DEFAULT 'None',   `partner_name` varchar(200) NOT NULL DEFAULT 'None',   `sub_class` varchar(200) NOT NULL DEFAULT 'None',   `premium_vs_standard` varchar(20) NOT NULL DEFAULT 'Standard',   `is_frozen` tinyint(4) DEFAULT NULL,   `is_auto_invoice` tinyint(4) NOT NULL DEFAULT 0,   `sales_region` varchar(200) NOT NULL DEFAULT 'None',   `bookings_country` varchar(200) NOT NULL DEFAULT 'None',   `state_code` varchar(200) NOT NULL DEFAULT 'None' ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (19.321 sec)

MariaDB [test]> WITH RECURSIVE periods AS (     SELECT         LAST_DAY(CURDATE()) AS period_end     UNION ALL     SELECT         period_end - INTERVAL 1 MONTH AS period_end     FROM          periods     WHERE          period_end - INTERVAL 1 MONTH >= (LAST_DAY(CURDATE()) - INTERVAL 3 MONTH) ) SELECT      periods.period_end,     SUM(ROUND(local_amt / current_fx.exchange_rate, 2)) AS apttus_arr FROM periods INNER JOIN f_bookings ON      f_bookings.booking_type IN ('tsg_apttus')  AND      periods.period_end BETWEEN f_bookings.valid_from_dt AND f_bookings.valid_end_dt LEFT JOIN d_currency current_fx ON         f_bookings.currency_id = current_fx.from_currency_code AND         YEAR(CURDATE()) = YEAR(current_fx.rec_eff_dt) GROUP BY     1;

ERROR 2013 (HY000): Lost connection to server during query

MariaDB [test]> select @@datadir, @@log_error;

ERROR 2006 (HY000): Server has gone away

No connection. Trying to reconnect...

Connection id:    3

Current database: test

+-----------------+------------------------------------------------+

| @@datadir       | @@log_error                                    |

+-----------------+------------------------------------------------+

| /var/lib/mysql/ | /var/log/mariadb/columnstore/mariadb-error.log |

+-----------------+------------------------------------------------+

1 row in set (0.012 sec)

MariaDB [test]> exit

Bye

[root@c2eec0bd325e /]# tail -100 /var/log/mariadb/columnstore/mariadb-error.log

2022-08-19 12:22:42 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-08-19 12:22:42 0 [Note] InnoDB: 10.6.7 started; log sequence number 51735; transaction id 33

2022-08-19 12:22:42 0 [Note] Plugin 'FEEDBACK' is disabled.

2022-08-19 12:22:42 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-08-19 12:22:42 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.

2022-08-19 12:22:42 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

2022-08-19 12:22:42 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:22:42

2022-08-19 12:22:42 0 [Note] Server socket created on IP: '0.0.0.0'.

2022-08-19 12:22:42 0 [Note] Server socket created on IP: '::'.

2022-08-19 12:22:42 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-08-19 12:22:44 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown

2022-08-19 12:22:44 0 [Note] InnoDB: FTS optimize thread exiting.

2022-08-19 12:22:44 server_audit: STOPPED

2022-08-19 12:22:44 0 [Note] InnoDB: Starting shutdown...

2022-08-19 12:22:44 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool

2022-08-19 12:22:44 0 [Note] InnoDB: Buffer pool(s) dump completed at 220819 12:22:44

2022-08-19 12:22:45 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"

2022-08-19 12:22:45 0 [Note] InnoDB: Shutdown completed; log sequence number 52149; transaction id 34

2022-08-19 12:22:45 0 [Note] /usr/sbin/mariadbd: Shutdown complete

220819 12:22:45 mysqld_safe mysqld from pid file /var/lib/mysql/c2eec0bd325e.pid ended

220819 12:22:54 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql

2022-08-19 12:22:54 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 850 ...

220819 12:22:54 Columnstore: Started; Version: 6.2.1-1

2022-08-19 12:22:54 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2022-08-19 12:22:54 0 [Note] InnoDB: Number of pools: 1

2022-08-19 12:22:54 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2022-08-19 12:22:54 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2022-08-19 12:22:54 0 [Note] InnoDB: Using Linux native AIO

2022-08-19 12:22:54 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728

2022-08-19 12:22:54 0 [Note] InnoDB: Completed initialization of buffer pool

2022-08-19 12:22:54 0 [Note] InnoDB: 128 rollback segments are active.

2022-08-19 12:22:54 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2022-08-19 12:22:54 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2022-08-19 12:22:54 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-08-19 12:22:54 0 [Note] InnoDB: 10.6.7 started; log sequence number 52149; transaction id 33

2022-08-19 12:22:54 0 [Note] Plugin 'FEEDBACK' is disabled.

2022-08-19 12:22:54 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-08-19 12:22:54 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.

2022-08-19 12:22:54 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

2022-08-19 12:22:55 0 [Note] Server socket created on IP: '0.0.0.0'.

2022-08-19 12:22:55 0 [Note] Server socket created on IP: '::'.

2022-08-19 12:22:55 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:22:55

2022-08-19 12:22:55 0 [Note] /usr/sbin/mariadbd: ready for connections.

Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

220819 12:30:56 mysqld_safe Number of processes running now: 0

220819 12:30:56 mysqld_safe mysqld restarted

2022-08-19 12:30:56 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 962 ...

220819 12:30:57 Columnstore: Started; Version: 6.2.1-1

2022-08-19 12:30:57 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2022-08-19 12:30:57 0 [Note] InnoDB: Number of pools: 1

2022-08-19 12:30:57 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2022-08-19 12:30:57 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2022-08-19 12:30:57 0 [Note] InnoDB: Using Linux native AIO

2022-08-19 12:30:57 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728

2022-08-19 12:30:57 0 [Note] InnoDB: Completed initialization of buffer pool

2022-08-19 12:30:57 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=52149,52149

2022-08-19 12:30:57 0 [Note] InnoDB: 128 rollback segments are active.

2022-08-19 12:30:57 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"

2022-08-19 12:30:57 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2022-08-19 12:30:57 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2022-08-19 12:30:57 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-08-19 12:30:57 0 [Note] InnoDB: 10.6.7 started; log sequence number 52513; transaction id 33

2022-08-19 12:30:57 0 [Note] Plugin 'FEEDBACK' is disabled.

2022-08-19 12:30:57 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-08-19 12:30:57 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.

2022-08-19 12:30:57 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

2022-08-19 12:30:57 0 [Note] Server socket created on IP: '0.0.0.0'.

2022-08-19 12:30:57 0 [Note] Server socket created on IP: '::'.

2022-08-19 12:30:57 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:30:57

2022-08-19 12:30:57 0 [Note] /usr/sbin/mariadbd: ready for connections.

Version: '10.6.7-3-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

220819 12:34:15 mysqld_safe Number of processes running now: 0

220819 12:34:15 mysqld_safe mysqld restarted

2022-08-19 12:34:15 0 [Note] /usr/sbin/mariadbd (server 10.6.7-3-MariaDB) starting as process 1024 ...

220819 12:34:15 Columnstore: Started; Version: 6.2.1-1

2022-08-19 12:34:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2022-08-19 12:34:15 0 [Note] InnoDB: Number of pools: 1

2022-08-19 12:34:15 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions

2022-08-19 12:34:15 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)

2022-08-19 12:34:15 0 [Note] InnoDB: Using Linux native AIO

2022-08-19 12:34:15 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728

2022-08-19 12:34:15 0 [Note] InnoDB: Completed initialization of buffer pool

2022-08-19 12:34:15 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=52149,52149

2022-08-19 12:34:15 0 [Note] InnoDB: 128 rollback segments are active.

2022-08-19 12:34:15 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"

2022-08-19 12:34:15 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2022-08-19 12:34:15 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2022-08-19 12:34:15 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-08-19 12:34:15 0 [Note] InnoDB: 10.6.7 started; log sequence number 52525; transaction id 33

2022-08-19 12:34:15 0 [Note] Plugin 'FEEDBACK' is disabled.

2022-08-19 12:34:15 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.

2022-08-19 12:34:15 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

2022-08-19 12:34:15 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-08-19 12:34:16 0 [Note] Server socket created on IP: '0.0.0.0'.

2022-08-19 12:34:16 0 [Note] Server socket created on IP: '::'.

2022-08-19 12:34:16 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-08-19 12:34:16 0 [Note] InnoDB: Buffer pool(s) load completed at 220819 12:34:16

[root@c2eec0bd325e /]#