SQL bug for 'IS NULL' pre-filter on dropdown elements

Status
Not open for further replies.
VOI

VOI

Member
Hi,

since a recent github update I am getting a SQL error when trying to open a list with the following pre-filter:

Field: any dropdown element with an entry where the label is empty, but not the value
Condition: IS NULL
Value: (empty)
Type: Text

The corresponding part in the SQL Statement for a dropdown element called 'dp1' and the label of the empty dropdown entry been "Please Select" is:

Code: 
WHERE table.dp1 is null Please Select

i.e. the value of the dropdown entry is incorrectly added after the 'is null' operator.

Could someone please have a look at the code which generates the SQL statement for the list view?

Thanks

Chris
 
Hi Chris

I'm not able to replicate this one. Can you post a screen shot of your element settings and the prefilter please?
 
Hi Rob,

thanks for the quick response. Find attached a screenshot of the list pre-filter and the dropdown element with the "IS NULL" Condition.

dropdown.jpg

pre-filter.jpg

Chris
 
Friendly bump

To me it seems that when building the SQL query Fabrik is detecting that the element to filter for (dp1) is a dropdown element and attaches the value (Please Select) corresponding to the label enter in the filter field 'Value' to the WHERE clause.

This is fine for filter condition like "equel" or "not equal", but creating an incorrect SQL statement if the condition "IS NULL" is used.
 
Confirmed.
But the error occurs only if you have an empty label in your dropdown.
In this case it's creating the wrong "WHERE table.dp1 is null Please Select" by appending the value (Please select in your case).

Workaround: put anything in your label (may be . or just a space).
 
I already thought about that workaround but hestitated to apply it since this would involve changing values of all records in the database for the affected dropdowns (I got about 10 of those).

Until the fabrik update about 2-3 weeks ago the filter worked fine - so something must have been changed in the code which creates the SQL statement.
 
No, the problem is your empty LABEL, you only have to change this.
This doesn't affect the database at all, it's storing the value.
 
Applied the workaround - however, would it be possible to correct the root cause of this problem? Otherwise, I always have to remember to use this workaround when filtering for such dropdown elements.
 
Updated from Github today. I can verify that IS NULL Filter is now working also if dropdown label is empty.

Closing this thread
 
On my new J3.2 site - using Fabrik 3.1rc2 - the problem described above re-occured: any list using a prefilter with is_null on a dropdown element containing an empty label is creating an SQL error.

Could you maybe apply the fix to Fabrik 3.1 as well?

Thanks.

Chris
 
Update from the 3.1 Github branch today. Issue is fixed in 3.1 now as well.

Closing this thread.
 
Issue wasnt really fixed since the condition looks for "is null", however the variable "condition" contains this expression in UPPERCASE, i.e. "IS NULL".

Therefore the variable $value is not set to empty.

Can someone change this in github?
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 143 (members: 1, guests: 142)
    Back
    Top