#
# MCOL-5480 LDI loads values incorrectly for MEDIUMINT, TIME and TIMESTAMP
# when cpimport is used for batch insert
#

--source ../include/have_columnstore.inc

let $DATADIR=`SELECT @@datadir`;

--disable_warnings
DROP DATABASE IF EXISTS mcol_5480;
--enable_warnings
CREATE DATABASE mcol_5480;
USE mcol_5480;
SHOW VARIABLES LIKE 'columnstore_use_import_for_batchinsert';

# Test for signed medium int with timestamp and time data types.
## Signed medium int as the first column
CREATE TABLE t1 (cmediumint MEDIUMINT, ctimestamp TIMESTAMP, ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "-8388608|2020-08-13 03:14:07|11:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "-8388607|2020-08-14 03:14:07|11:58:38|" >> $DATADIR/mcol5480.txt
--exec echo "-1000|2020-08-15 03:14:07|11:58:48|" >> $DATADIR/mcol5480.txt
--exec echo "-1|2020-08-16 03:14:07|11:58:58|" >> $DATADIR/mcol5480.txt
--exec echo "1|2020-08-13 03:14:08|12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "1000|2020-08-13 03:14:09|13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "8388607|2020-08-13 03:14:10|14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "\N|2020-08-13 03:14:11|15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

## Signed medium int as the middle column
CREATE TABLE t1 (ctimestamp TIMESTAMP, cmediumint MEDIUMINT, ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:07|-8388608|11:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-14 03:14:07|-8388607|11:58:38|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-15 03:14:07|-1000|11:58:48|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-16 03:14:07|-1|11:58:58|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:08|1|12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:09|1000|13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:10|8388607|14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:11|\N|15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for unsigned medium int with timestamp and time data types.
## Unsigned medium int as the first column
CREATE TABLE t1 (cmediumint MEDIUMINT UNSIGNED, ctimestamp TIMESTAMP, ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "0|2020-08-12 03:14:08|12:58:18|" >> $DATADIR/mcol5480.txt
--exec echo "1|2020-08-13 03:14:08|12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "1000|2020-08-13 03:14:09|13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "8388607|2020-08-13 03:14:10|14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "\N|2020-08-13 03:14:11|15:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "16777215|2020-08-13 03:14:11|15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

## Unsigned medium int as the middle column
CREATE TABLE t1 (ctimestamp TIMESTAMP, cmediumint MEDIUMINT UNSIGNED, ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "2020-08-12 03:14:08|0|12:58:18|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:08|1|12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:09|1000|13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:10|8388607|14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:11|\N|15:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:11|16777215|15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for only signed medium int data type
CREATE TABLE t1 (cmediumint MEDIUMINT) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "-8388608|" >> $DATADIR/mcol5480.txt
--exec echo "-8388607|" >> $DATADIR/mcol5480.txt
--exec echo "-1000|" >> $DATADIR/mcol5480.txt
--exec echo "-1|" >> $DATADIR/mcol5480.txt
--exec echo "1|" >> $DATADIR/mcol5480.txt
--exec echo "1000|" >> $DATADIR/mcol5480.txt
--exec echo "8388607|" >> $DATADIR/mcol5480.txt
--exec echo "\N|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for only unsigned medium int data type
CREATE TABLE t1 (cmediumint MEDIUMINT UNSIGNED) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "0|" >> $DATADIR/mcol5480.txt
--exec echo "1|" >> $DATADIR/mcol5480.txt
--exec echo "1000|" >> $DATADIR/mcol5480.txt
--exec echo "8388607|" >> $DATADIR/mcol5480.txt
--exec echo "\N|" >> $DATADIR/mcol5480.txt
--exec echo "16777215|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for timestamp and time data types
CREATE TABLE t1 (ctimestamp TIMESTAMP, ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:07|11:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-14 03:14:07|11:58:38|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-15 03:14:07|11:58:48|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-16 03:14:07|11:58:58|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:08|12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:09|13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:10|14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:11|15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for only timestamp data type
CREATE TABLE t1 (ctimestamp TIMESTAMP) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:07|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-14 03:14:07|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-15 03:14:07|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-16 03:14:07|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:08|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:09|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:10|" >> $DATADIR/mcol5480.txt
--exec echo "2020-08-13 03:14:11|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

# Test for only time data type
CREATE TABLE t1 (ctime TIME) engine=columnstore;
--exec rm -f $DATADIR/mcol5480.txt
--exec echo "11:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "11:58:38|" >> $DATADIR/mcol5480.txt
--exec echo "11:58:48|" >> $DATADIR/mcol5480.txt
--exec echo "11:58:58|" >> $DATADIR/mcol5480.txt
--exec echo "12:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "13:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "14:58:28|" >> $DATADIR/mcol5480.txt
--exec echo "15:58:28|" >> $DATADIR/mcol5480.txt
--replace_result $DATADIR DATADIR
--eval LOAD DATA INFILE "$DATADIR/mcol5480.txt" INTO TABLE t1 FIELDS TERMINATED BY "|";
SELECT * FROM t1;
DROP TABLE t1;

--exec rm -f $DATADIR/mcol5480.txt
--disable_warnings
DROP DATABASE mcol_5480;
--enable_warnings