CODEWITHSUNDEEP
mysqlsqlmariadbsql-viewsqldatetime
Craig
Craig

Reputation: 2143

MariaDB View not working but statement does

My new hosting provider is running MySQL Version 10.0.31-MariaDB-cll-lve

I have a view that was working fine in MySQL 5.6 but does not work in MariaDB.

I have created a simple cut down version just to show what is causing an error.

I can create the view but I can not use it:

CREATE VIEW `test_date` 
AS select (case 
   when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') 
     then '2018-06-30' 
   else NULL end) - interval 4 month

The error I get when trying to open:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use 
near '21:05:05 between 2018-01-01 00:00:00 and 2018-06-30 23:59:59) 
then '2018-06-30' ' at line 1

I can't see anything wrong and it works fine on normal MySQL servers.

I have tried removing the '- interval 4 month' and it works fine:

CREATE VIEW `test_date` 
AS select case 
  when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') 
    then '2018-06-30' 
  else NULL end

I have tried replacing the dates with simple numbers and it works fine:

CREATE VIEW `test_date` 
AS select (case 
   when (3 between 1 and 5) 
     then '2018-06-30' 
   else NULL end) - interval 4 month

So what is the real problem here? I am stumped.

Upvotes: 0

Views: 1212

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

'2018-06-30' is not implicitly converting to a date (I guess this is one of the things that has been tightened up between mysql versions or the fork that is mariadb) try explicitly converting it.

drop view if exists test_date;
CREATE VIEW `test_date` AS 
select (case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then str_to_date('2018-06-30','%Y-%m-%d') else NULL end) 
- interval 4 month;

select * from test_date;

+------------+
| Name_exp_1 |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

Oddly the select alone works fine it's only when used in a view (possibly in conjunction with a between statement) that it doesn't

MariaDB [sandbox]> select (case
    ->    when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59')
    ->      then '2018-06-30'
    ->    else NULL end) - interval 4 month rd;
+------------+
| rd         |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

When used in a view an error is thrown

MariaDB [sandbox]> create view test_date as
    -> select
    -> (
    -> case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then '2018-06-30'
    -> else NULL
    -> end
    -> ) - interval 4 month as rd
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [sandbox]> select rd from test_date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '08:16:57 between 2018-01-01 00:00:00 and 2018-06-30 23:59:59) then '2018-06-30' ' at line 1

If the between statement is replaced by >=,<= 

MariaDB [sandbox]> create view test_date as
    -> select
    -> (
    -> case when (now() >= '2018-01-01 00:00:00' and now() <= '2018-06-30 23:59:59') then '2018-06-30'
    -> else NULL
    -> end
    -> ) - interval 4 month as rd
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [sandbox]> select rd from test_date;
+------------+
| rd         |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions