[MCOL-173] "INSERT INTO tableName SELECT 42,100" not working correctly Created: 2016-06-23  Updated: 2016-09-15  Resolved: 2016-09-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc, ExeMgr
Affects Version/s: 1.0.1
Fix Version/s: 1.0.3

Type: Bug Priority: Critical
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
PartOf
is part of MCOL-280 Beta issues Closed
Sprint: 1.0.3

 Description   

This bug was reported by a MariaDB ColumnStore Google Group user. The issue also occur when "engine=columnstore" is used in the create table statement.

This issue is NOT in InfiniDB 4.6.0-1.

SET default_storage_engine=columnstore;

CREATE TABLE `pipo` ( `id_unique` bigint(12) NOT NULL DEFAULT '999999999999', `top_top_email_valide` decimal(18,0) DEFAULT NULL);

MariaDB [mcs]> INSERT INTO pipo SELECT 42,100;
Query OK, 1 row affected (1.56 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mcs]> SELECT * FROM pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 NULL

-------------------------------+
1 row in set (0.06 sec)

            1. NULL value for the 2nd column is incorrect

MariaDB [mcs]> INSERT INTO pipo VALUES (111,100);
Query OK, 1 row affected (0.14 sec)

MariaDB [mcs]> SELECT * FROM pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 NULL
111 100

-------------------------------+
2 rows in set (0.01 sec)

No bug with NOT NULL (also DEFAULT NULL ) :

MariaDB [mcs]> CREATE TABLE `pipo` ( `id_unique` bigint(12) NOT NULL DEFAULT '999999999999', `top_top_email_valide` decimal(18,0) NOT NULL);
Query OK, 0 rows affected (0.17 sec)

MariaDB [mcs]> INSERT INTO pipo SELECT 42,100;
Query OK, 1 row affected (1.60 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mcs]> SELECT * FROM pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 100

-------------------------------+
1 row in set (0.04 sec)

MariaDB [mcs]> INSERT INTO pipo VALUES (111,100);
Query OK, 1 row affected (0.13 sec)

MariaDB [mcs]> SELECT * FROM pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 100
111 100

-------------------------------+
2 rows in set (0.01 sec)

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-09-09 ]

This is only when 

infinidb_use_import_for_batchinsert=1

which is the default.

Comment by Andrew Hutchings (Inactive) [ 2016-09-09 ]

Code in https://github.com/mariadb-corporation/mariadb-columnstore-engine/pull/8

Hopefully the QA for this should be pretty straight forward.

Comment by Daniel Lee (Inactive) [ 2016-09-15 ]

Build verified:

mscadmin> getsoft
getsoftwareinfo Thu Sep 15 12:06:24 2016

Name : mariadb-columnstore-platform
Version : 1.0.3
Release : 1
Architecture: x86_64
Install Date: Thu 15 Sep 2016 11:39:01 AM CDT
Group : Applications
Size : 25431329
License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
Signature : (none)
Source RPM : mariadb-columnstore-1.0.3-1.src.rpm
Build Date : Thu 15 Sep 2016 09:56:11 AM CDT

MariaDB [mytest]> CREATE TABLE `pipo` ( `id_unique` bigint(12) NOT NULL DEFAULT '999999999999', `top_top_email_valide` decimal(18,0) DEFAULT NULL) engine=columnstore;
Query OK, 0 rows affected (1.15 sec)

MariaDB [mytest]> INSERT INTO pipo SELECT 42,100;
Query OK, 1 row affected (3.50 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select * from pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 100

-------------------------------+
1 row in set (0.08 sec)

MariaDB [mytest]> drop table pipo;
Query OK, 0 rows affected (0.93 sec)

MariaDB [mytest]> SET default_storage_engine=columnstore;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mytest]> CREATE TABLE `pipo` ( `id_unique` bigint(12) NOT NULL DEFAULT '999999999999', `top_top_email_valide` decimal(18,0) DEFAULT NULL);
Query OK, 0 rows affected (0.31 sec)

MariaDB [mytest]> show create table pipo;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

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

pipo CREATE TABLE `pipo` (
`id_unique` bigint(12) NOT NULL DEFAULT '999999999999',
`top_top_email_valide` decimal(18,0) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mytest]> INSERT INTO pipo SELECT 42,100;
Query OK, 1 row affected (1.83 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> SELECT * FROM pipo;
-------------------------------+

id_unique top_top_email_valide

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

42 100

-------------------------------+
1 row in set (0.06 sec)

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