"SQLSTATE[42883]: Undefined function: 7 ERROR: function lower(date) does not exist" error searching

[Okipa]

Discussed in https://github.com/Okipa/laravel-table/discussions/125

^{Originally posted by **devgiu** March 17, 2023} Hi, I don't know why or since when but suddenly my tables stopped working trying to search. I have a laravel 7 project with laravel-table v3 I created several pages with a simple laravel-table with date column and worked fine, and I didn't came back to this pages again. This week I deployed a new version with some asked features in the project several months later last update, and users started to complain about errors on pages I didn't changed in more than a year. Error is trying to search. Table has a date column called due_date with sortable and searchable. If I remove sortable and searchable, it works. Need help to know the root of the problem and how to solve it, because there is another page with a similar error but an int column. `Illuminate\Database\QueryException SQLSTATE[42883]: Undefined function: 7 ERROR: function lower(date) does not exist LINE 1: ... LOWER(vp_project.project_name)::text ILIKE $3 or LOWER(vp_p... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select count(*) as aggregate from "vp_po_data" left join "vp_project" on "vp_project"."project_key" = "vp_po_data"."project_key" left join "vp_vendor" on "vp_vendor"."vendor_key" = "vp_po_data"."vendor_key" where (LOWER(vp_po_data.client_order_number)::text ILIKE %fefe% or LOWER(vp_vendor.vendor_name)::text ILIKE %fefe% or LOWER(vp_project.project_name)::text ILIKE %fefe% or LOWER(vp_po_data.due_date)::text ILIKE %fefe%)) (View: /home/xxxx/project/resources/views/podata/index.blade.php) ` Looking in the log I see this where: `where (LOWER(vp_po_data.client_order_number)::text ilike '%fefe%' or LOWER(vp_vendor.vendor_name)::text ilike '%fefe%' or LOWER(vp_project.project_name)::text ilike '%fefe%' or LOWER(vp_po_data.due_date)::text ilike '%fefe%')` Trying to run the query in dbeaver it complains and looks like it wants date field to be casted this way `LOWER(vp_po_data.due_date::text)` I'm not an expert in laravel, nor laraveltable, is the unique project I work in laravel and have no idea how to get ride of this.

[Okipa]

@devgiu

I tried to reproduce to issue on the V5 (the SQL treatment is bearly the same than V3) and I couldn't write a test that is putting the problem in evidence.

You can see it here : https://github.com/Okipa/laravel-table/pull/130

Could please try to write a test that would highlight the problem ?

I see that you are using Postgres, the tests are running under MySQL and the behavior is fine: could it be possible that the issue is triggered only with postgres when we try to lower date or integer fields ?

I don't use much Postgres, so I would appreciate any help for this.

[devgiu]

Sorry, no idea how to do testing, I have only one project with Laravel. Yes, probably issue is only with postgres and not mysql because Postgres forces the need of casting. I solved temporary with a previous version. Issue comes from changes on this commit https://github.com/Okipa/laravel-table/commit/705267ecdc2544ec0e2896f47167fe807efed7b8

[Okipa]

@devgiu Did you bumped your version of Postgres recently ?

I found an issue on another package that explains that functions only accept strings since PG 8.4 => https://github.com/thoughtbot/administrate/issues/620

That may be related to your case. I think that I can manage to implement a fix on V5.

Using the previous version will work for you but the search will be an exact search (the case will have to be exactly the same to return results).

[devgiu]

No, I chanched nothing, in the server, only on my project. I understand the issue about the case, but I can live with that, but not with the problem, and upgrade ight now to latest versions is probably a pain. It's a project I'm being hired to just add some features or fix some issues and nothing more

[Okipa]

@devgiu I created a PR with a potential fix : https://github.com/Okipa/laravel-table/pull/131/files

Could please try to install the package with the 3.1.4 branch ?

To do that, executing composer require "okipa/laravel-table:dev-3.1.4" should work.

[Okipa]

@devgiu I have edited my post above with the correct composer instruction.

I also made a fix: there was a missing opening parenthesis after CAST.

Please tell me if this is working for you.

[Okipa]

@devgiu I have implemented a fix for v5.

I let you tell me if you need this PR to be implemented as well and tagged 3.1.4: https://github.com/Okipa/laravel-table/pull/131

It would be an exceptional implementation on a previous version as I usually don't maintain versions other than the current one (I still may accept contributions).

[devgiu]

Thanks, will try ASAP in next days