DATETIME and DATE Types Not Comparable is not compatible with MySQL

[Padraig O'Sullivan]

server revision: 1.1.0

DATETIME and DATE types are not comparable in the Akiban Server. This is not compatible with MySQL behavior.

An easy test case to demonstrate this:

mysql> create table t1 (id int primary key, a_date datetime not null) engine=akibandb;
Query OK, 0 rows affected (0.28 sec)

mysql> mysql> select * from t1 where a_date > current_date - interval 1 day;
Empty set (0.09 sec)

mysql>

Executing the same query in akiban:

test=> select * from t1 where a_date > current_date - interval 1 day;
ERROR: Types not comparable: DATETIME and DATE
test=>

This in particular affects one of our design partners.

[Padraig O'Sullivan (posulliv)]

The workaround we have been doing at the moment is to use the now() function:

test=> select * from t1 where a_date > now() - interval 1 day;
 id | a_date
----+--------
(0 rows)

test=>

As part of resolving this bug, it would be nice to know which functions are not MySQL compatible if possible

[Timothy Wegner (timmwegner)]

I have added a separate story to properly document compatibility issues
that we know of.

On Tue, Apr 3, 2012 at 10:30 AM, Mike McMahon <email address hidden>wrote:

> ** Project changed: akiban-server => akiban-sql-parser
>
> --
> You received this bug notification because you are a member of Akiban
> Technologies, which is subscribed to the bug report.
> https://bugs.launchpad.net/bugs/972447
>
> Title:
> DATETIME and DATE Types Not Comparable is not compatible with MySQL
>
> Status in Akiban SQL Parser Project:
> New
>
> Bug description:
> server revision: 1.1.0
>
> DATETIME and DATE types are not comparable in the Akiban Server. This
> is not compatible with MySQL behavior.
>
> An easy test case to demonstrate this:
>
> mysql> create table t1 (id int primary key, a_date datetime not null)
> engine=akibandb;
> Query OK, 0 rows affected (0.28 sec)
>
> mysql> mysql> select * from t1 where a_date > current_date - interval 1
> day;
> Empty set (0.09 sec)
>
> mysql>
>
> Executing the same query in akiban:
>
> test=> select * from t1 where a_date > current_date - interval 1 day;
> ERROR: Types not comparable: DATETIME and DATE
> test=>
>
> This in particular affects one of our design partners.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/akiban-sql-parser/+bug/972447/+subscriptions
>