INSERT INTO...SELECT issue in MariaDB Columnstore

Question

I have a new installation of MariaDB 10.5.8 which includes Columnstore as a plugin. I'm facing an issue when I try to insert rows into a columnstore table from a select statement. I have narrowed it down to a test case that consistently replicates the problem.

If I create the table below:

CREATE TABLE `test_table` (
    `id` INT(11) NULL DEFAULT NULL,
    `code` VARCHAR(1) NULL DEFAULT NULL,
    `enter_dt` DATE NULL DEFAULT NULL
)
ENGINE=Columnstore

and run the following insert command:

INSERT INTO test_table(id,code,enter_dt)
SELECT 1,'M',date(NOW()) FROM dual;

I get the error:

However, if the VARCHAR column comes after the DATE column, the error goes away:

CREATE TABLE `test_table` (
    `id` INT(11) NULL DEFAULT NULL,
    `enter_dt` DATE NULL DEFAULT NULL,
    `code` VARCHAR(1) NULL DEFAULT NULL
)
ENGINE=Columnstore

Has anyone experience a similar issue?

The [DB fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=6b6dc4a5da5caca70274444edb7bf03e) does not yet have the `columnstore` engine available, so I was not able to check, but does it matter that the insert statement tries to insert the date to the `code` column and the code to the `date_dt` column? — Paul T., Jan 05 '21 at 03:54
@PaulT. Thank you for the reply. It was my mistake while transcribing. I have corrected the example. — Juan Arias, Jan 05 '21 at 12:45

score 1 · Answer 1 · answered Jan 06 '21 at 14:26

I tried the snippet with the latest MCS code that will be shiped with the next community release 10.5.9 of MDB. It looks like it works. I'll try to test it with the packages shiped but could you tell me if the error is the same when you use a native MDB client w/o any GUI connection managers? Here are my results:

MariaDB [test]> show status like '%columnstore%';                                                                                                                                         

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Columnstore_commit_hash | eec5dd6c0 |
| Columnstore_version     | 5.5.1     |
+-------------------------+-----------+
2 rows in set (0.002 sec)

MariaDB [test]> CREATE TABLE `test_table` (
    ->     `id` INT(11) NULL DEFAULT NULL,
    ->     `code` VARCHAR(1) NULL DEFAULT NULL,
    ->     `enter_dt` DATE NULL DEFAULT NULL
    -> )
    -> ENGINE=Columnstore;
Query OK, 0 rows affected (0.768 sec)

MariaDB [test]> INSERT INTO test_table(id,code,enter_dt)
    -> SELECT 1,'M',date(NOW()) FROM dual;
Query OK, 1 row affected (1.353 sec)
Records: 1  Duplicates: 0  Warnings: 0
MariaDB [test]> select * from test_table;
+------+------+------------+
| id   | code | enter_dt   |
+------+------+------------+
|    1 | M    | 2021-01-06 |
+------+------+------------+
1 row in set (0.134 sec)

Thanks for your answer. I did try it without a GUI connection manager. I got the values saturated message. I'll try with the new version. — Juan Arias, Jan 07 '21 at 14:34

INSERT INTO...SELECT issue in MariaDB Columnstore

1 Answers1