[MCOL-350] can't select date value of 0000-00-00 Created: 2016-10-07  Updated: 2016-11-11  Resolved: 2016-11-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 1.0.3
Fix Version/s: 1.0.5

Type: Bug Priority: Major
Reporter: David Thompson (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: relnote

Issue Links:
Blocks
blocks MCOL-172 & operator returns wrong results in 4... Closed
blocks MCOL-179 DIV operator returns wrong result on ... Closed
Relates
relates to MCOL-171 0000-00-00 dates are not supported an... Closed
relates to MCOL-174 | operator returns wrong results in 7... Closed
Sprint: 2016-20, 2016-21, 2016-22

 Description   

found this while reviewing justin's testsuite result bugs and it looks like we missed a verification in MCOL-171. It looks like datetime correctly supports where clauses on 0000-00-00 but not date values. 

MariaDB [test]> create table t2(d date, dt datetime) engine=columnstore;
 
Query OK, 0 rows affected (0.54 sec)
 
 
 
MariaDB [test]> insert into t2 values ('0000-00-00','0000-00-00');
 
Query OK, 1 row affected (0.17 sec)
 
 
 
MariaDB [test]>  select * from t2 where dt='0000-00-00';
 
+------------+---------------------+
 
| d          | dt                  |
 
+------------+---------------------+
 
| 0000-00-00 | 0000-00-00 00:00:00 |
 
+------------+---------------------+
 
1 row in set (0.04 sec)
 
 
 
MariaDB [test]>  select * from t2 where d='0000-00-00';
 
Empty set (0.01 sec)

the last query should also return a result. In justin's test suite, it means that the data setup code doesn't correctly set the d column to null causing some of the failures.

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

Damn, I must have missed an if (data == 0) then NULL thing somewhere in PrimProc...

Comment by Andrew Hutchings (Inactive) [ 2016-10-19 ]

My notes for when I come to patch this later:
After spending far too long debugging this I found that the execplan sets a special magic 0 for '0000-00-00' during compare. This is in jlf_execplantojoblist.cpp in two places (look for an if with '0000-00-00')

This will fail as the storage level date format is padded with 3F for the unused 6 bits. It either needs to be processed in the same data conversion function or hard set to an empty Date struct.

Comment by Andrew Hutchings (Inactive) [ 2016-10-19 ]

Fix ready for when code freeze is listed.

Comment by Daniel Lee (Inactive) [ 2016-11-11 ]

Build verified: 1.0.5-1

mcsadmin> getsoft
getsoftwareinfo Fri Nov 11 16:39:51 2016

Name : mariadb-columnstore-platform
Version : 1.0.5
Release : 1
Architecture: x86_64
Install Date: Fri 11 Nov 2016 03:09:12 PM UTC
Group : Applications/Databases
Size : 9987818
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-platform-1.0.5-1.src.rpm
Build Date : Thu 10 Nov 2016 04:31:29 PM UTC
Build Host : centos7

MariaDB [tpch10c]> use mytest
Database changed
MariaDB [mytest]> create table t2(d date, dt datetime) engine=columnstore;
Query OK, 0 rows affected (0.19 sec)

MariaDB [mytest]> insert into t2 values ('0000-00-00','0000-00-00');
Query OK, 1 row affected (0.20 sec)

MariaDB [mytest]> select * from t2 where dt='0000-00-00';
-------------------------------+

d dt

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

0000-00-00 0000-00-00 00:00:00

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

MariaDB [mytest]>
MariaDB [mytest]>
MariaDB [mytest]> select * from t2 where d='0000-00-00';
-------------------------------+

d dt

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

0000-00-00 0000-00-00 00:00:00

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

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