[MCOL-1382] Incorrect results returned when comparing incompatible columns in the where clause Created: 2018-05-03  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.1.4
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Daniel Lee (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: None

Epic Link: ColumnStore Compatibility Improvements

 Description   

Build tested: 1.1.4-1

For the following query, MariaDB server returned:

MariaDB [mytest]> select dtt1.cidx, dtt1.CVCHAR8, dtt2.CDATETIME from datatypetestm1 dtt1, datatypetestm2 dtt2 where dtt1.cidx = dtt2.cidx and dtt1.CVCHAR8 < dtt2.CDATETIME
-> ;
---------------------------------

cidx CVCHAR8 CDATETIME

---------------------------------

1 aaaaaaaa 1997-01-01 00:00:00
2 h 1997-01-01 00:00:01
3 hh 1997-01-02 00:00:01
4 hhh 1997-01-03 00:00:02
5 hhhh 1997-01-04 00:00:03
6 sssssss 2009-12-31 23:59:56
7 ssssssss 2009-12-31 23:59:57
8 ssssssss 2009-12-31 23:59:58
9 ssssssss 2009-12-31 23:59:59
10 zzzzzzzz 2009-12-31 23:59:59

---------------------------------
10 rows in set, 10 warnings (0.00 sec)

MariaDB [mytest]> show warnings;
-------------------------------------------------

Level Code Message

-------------------------------------------------

Warning 1292 Incorrect datetime value: 'aaaaaaaa'
Warning 1292 Incorrect datetime value: 'h'
Warning 1292 Incorrect datetime value: 'hh'
Warning 1292 Incorrect datetime value: 'hhh'
Warning 1292 Incorrect datetime value: 'hhhh'
Warning 1292 Incorrect datetime value: 'sssssss'
Warning 1292 Incorrect datetime value: 'ssssssss'
Warning 1292 Incorrect datetime value: 'ssssssss'
Warning 1292 Incorrect datetime value: 'ssssssss'
Warning 1292 Incorrect datetime value: 'zzzzzzzz'

-------------------------------------------------
10 rows in set (0.00 sec)

it seems that it tried to cast the vchar8 column values to date time. In this case it, actually returned results. ColumnStore did not return any rows.

if the > operator is used, server returned no results while ColumnStore did.

This is reported by the Autopilot/datatypecasting test suite. 52 test cases did not pass and they all have DATE/DATETIME columns involved in the comparison.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-05-04 ]

It will get the datetime values from dtt2, cast those as CHAR and then compare. Although it doesn't look like it is even doing that correctly as inverting the WHERE condition (dtt1.CVCHAR8 < dtt2.CDATETIME) also produces no results.

Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

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