Framework: json where on sqlite throws error: This database engine does not support JSON operations

[gdpa]

• Laravel Version: 5.6.26
• PHP Version: 7.2
• Database Driver & Version: sqlite 3

Description:

After upgrading from 5.6.23 to 5.6.26 json where queries for sqlite throw error:

This database engine does not support JSON operations

Steps To Reproduce:

Add a notification class for user to notify another user want to be friend and use sqlite database driver for saving notification with friend object.
Try to markAsRead notification on accept friend request:
auth()->user->notifications()->where('type', 'App\Notifications\FriendshipRequestNotification')->where('data->user->id', $friend->id)->get();

[staudenmeir]

I don't see how this query could have worked before. Laravel has never supported JSON queries on SQLite.

[MannikJ]

We just updated from v5.6.14 to v5.6.34 and although it seems a little strange, I can only confirm that some tests where json columns are involved could be executed successfully before, but now fail in the same way as described above.
You might be correct that it should'nt have worked before either, but I don't think it's coincidence that the upgrade makes it behave differently than before.

[staudenmeir]

Can you post an example?

[MannikJ]

Mh, it's hard to give proper information. I use the package spatie/laravel-tags and it internally stores the tag name with translations in a json column. It uses a json deep where query which seems to be the problem. I have no time to dive deeper into this. Will have another look on monday. If you have any ideas, please share ;)

[staudenmeir]

The behavior has changed in 5.6.25. Before that, the query used the unmodified column selector:

DB::table('test')->where('foo->bar', 'baz')->get();
// select * from "test" where "foo->bar" = 'baz'

This couldn't have worked, it looks for a column named foo->bar.

But it also didn't throw an exception because SQLite silently ignores non-existing columns.
Does this explain your issues?

[MannikJ]

That makes perfect sense. I already guessed it's something like that. That's why I wrote 'where json columns are involved'. The tests are not very extensive so it might looked like it was working as long as it didn't fail.

[MannikJ]

@staudenmeir Thank you very much for your PR. We just installed v5.6.35 and the problems are solved. I'm also glad that is properly implemented so we can use those json queries with sqlite in the future.