Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-1516

DateTime ingestion corner case - wrong handling - "0001-01-01 00:00:01"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Do
    • 1.1.5
    • Icebox
    • MariaDB Server
    • None
    • CentOS 7

    Description

      If I try to insert the DateTime "0001-01-01 00:00:01.0" into a ColumnStore table via mcsmysql it shows a truncation warning and inserts the date "0000-00-00 00:00:00" instead. On InnoDB tables the insert works as expected.

      If mcsapi is used to injected the DateTime "0001-01-01 00:00:01.0" it is shown as injected in mcsmysql. But if afterwards the DateTime is tried to be deleted via:

      DELETE FROM test_cs WHERE dt = '0001-01-01 00:00:01';

      only the via mcsmysql inserted DateTime "0000-00-00 00:00:00" is deleted.

      [jens@centos-dev tmp]$ mcsmysql informatica
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 124
      Server version: 10.2.15-MariaDB-log Columnstore 1.1.5-1
       
      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 [informatica]> create table test_inno (dt datetime) engine=innodb;
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [informatica]> create table test_cs (dt datetime) engine=columnstore;
      Query OK, 0 rows affected (0.13 sec)
       
      MariaDB [informatica]> insert into test_inno values ('0001-01-01 00:00:01.0');
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [informatica]> insert into test_cs values ('0001-01-01 00:00:01.0');
      Query OK, 1 row affected, 1 warning (0.20 sec)
       
      MariaDB [informatica]> show warnings;
      +---------+------+------------------------------------------------------+
      | Level   | Code | Message                                              |
      +---------+------+------------------------------------------------------+
      | Warning | 1264 | CAL0001: IDB-2025: Data truncated for column 'dt'    |
      +---------+------+------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [informatica]> select * FROM test_inno;
      +---------------------+
      | dt                  |
      +---------------------+
      | 0001-01-01 00:00:01 |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [informatica]> select * FROM test_cs;
      +---------------------+
      | dt                  |
      +---------------------+
      | 0000-00-00 00:00:00 |
      +---------------------+
      1 row in set (0.07 sec)
       
      MariaDB [informatica]> Ctrl-C -- exit!
      Aborted
      [jens@centos-dev tmp]$ python3
      Python 3.4.8 (default, Mar 23 2018, 10:04:27)
      [GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux
      Type "help", "copyright", "credits" or "license" for more information.
      >>> import pymcsapi
      >>> d = pymcsapi.ColumnStoreDriver()
      >>> b = d.createBulkInsert("informatica","test_cs",0,0)
      >>> b.setColumn(0,"0001-01-01 00:00:01")
      [<pymcsapi.ColumnStoreBulkInsert; proxy of <Swig Object of type 'mcsapi::ColumnStoreBulkInsert *' at 0x7f3621612810> >, 0]
      >>> b.writeRow()
      <pymcsapi.ColumnStoreBulkInsert; proxy of <Swig Object of type 'mcsapi::ColumnStoreBulkInsert *' at 0x7f36216128a0> >
      >>> b.commit()
      >>>
      [jens@centos-dev tmp]$ mcsmysql informatica
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 143
      Server version: 10.2.15-MariaDB-log Columnstore 1.1.5-1
       
      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 [informatica]> select * FROM test_cs;
      +---------------------+
      | dt                  |
      +---------------------+
      | 0000-00-00 00:00:00 |
      | 0001-01-01 00:00:01 |
      +---------------------+
      2 rows in set (0.07 sec)
       
      MariaDB [informatica]> DELETE FROM test_inno WHERE dt = '0001-01-01 00:00:01';
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [informatica]> DELETE FROM test_cs WHERE dt = '0001-01-01 00:00:01';
      Query OK, 1 row affected (0.22 sec)
       
      MariaDB [informatica]> SELECT * FROM test_inno;
      Empty set (0.00 sec)
       
      MariaDB [informatica]> SELECT * FROM test_cs;
      +---------------------+
      | dt                  |
      +---------------------+
      | 0001-01-01 00:00:01 |
      +---------------------+
      1 row in set (0.02 sec)
       
      MariaDB [informatica]> Bye
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              jens.rowekamp Jens Röwekamp (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.