MariaDB View not working but statement does

Question

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.

You are using string literals here. Is this the same in your real view? If you want date literals, use the ANSI syntax, where the string is preceded by the `DATE` or `TIMESTAMP` keyword. E.g. `timestamp '2018-01-01 00:00:00'` or `date '2018-01-01'`. — Thorsten Kettner, Jan 17 '18 at 11:48
With time ranges `BETWEEN` is not recommended, because of time precision. Use `case when now() >= date '2018-01-01' and now() < date '2018-07-01' then` instead. Or use dates without time parts only: `case when current_date between date '2018-01-01' and date '2018-06-30' then`. — Thorsten Kettner, Jan 17 '18 at 11:54

P.Salmon · Accepted Answer · 2018-01-18T08:24:39.667

'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)

Thanks that seems to have fixed it. Any idea why in my last example it works? Seems odd it would convert it to a date in that example. — Craig, Jan 17 '18 at 22:52
After a bit more testing this begins to look like a bug to me. I have added some detail to the answer. — P.Salmon, Jan 18 '18 at 08:14
Thanks for adding more detail, yeah the logic just doesn't make sense to me. I can't understand why as a view it would execute differently then the statement itself. — Craig, Jan 19 '18 at 09:38

MariaDB View not working but statement does

1 Answers1