Mariadb started giving erroneous query results for 1 view 10.1.44-MariaDB-0ubuntu0.18.04.1

Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its datetime column is included in the where clause. This started happening in more than one database with this particular view on the same instance each with their own data in the tables (details on that part at bottom).

Data that's there:

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020;

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22


With action_date_created doing a comparison in where clause like this gets no rows returned:

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created &gt; '2020-01-01'


Change ">" to "&lt;" and ask for records before 2020 which clearly there are none, and it will return exactly 1 record which is not before 2020.

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created &lt; '2020-01-01'

scene_id    action_date_created
11020     2020-02-08 07:14:28


Deleting that particular record will cause the next record to be retrieved instead.

Adding "order by 1" on the end causes a query to give correct results

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created &gt; '2020-01-01'
order by 1

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22


A query where "action_date_created is not null" without other clauses on that column gives correct results, but not if there is one of the ">" clauses that causes failure.

Querying the underlying table with action_date_created works fine. Spot-checking other views that have similar datetime columns does not reproduce the problem. Analyzed all tables in the database.  Have recreated the view, problem persists. 

Later found it is not just this database on the instance with the problem for this specific view.

There is a separate dev database on the same Mariadb instance with an identical data model but different data in it. Checked and it has started showing the same exact problem with its data as well.

The queries had worked for years. I know it worked successfully at 11pm last night, but was broken when someone ran one around 2am.

We've done a service mysql restart, but same problem persists. I'm perplexed. Any help would be appreciated.

Edit to give link to schema code:
https://raw.githubusercontent.com/volundmush/mushcode/master/Schema.sql
You get one wrong row and when that row is removed, you get the next wrong row in your result - means there are mismatches between table data and index data. You had checked through two clients and getting the same wrong result which means...

Option 1

If at all something happened to your databases around your mentioned 2 a.m. try running (for all databases)

mysqlcheck -o --all-databases


in commandline and

analyze table tablename;


Finally

optimize [no_write_to_binlog | local] table tablename;


The analyze / optimize are used again to ensure if everything is fine for your specific tables.

Option 2

Check your log files past previous night 11 p.m. If you can not find anything odd, post it here, we shall help.