Posted on

WordPress wpdb Update/Insert Null Problems

WP Core Ticket 15158 Banner

While doing additional testing for upcoming Tagalong features I discovered some odd behavior with the featured and rank elements of Enhanced Results which I had left activated on my development system.    It turns out that when you edit a location some odd things start happening in the data.    Further research has uncovered another WordPress wpdb bug (ticket #15158) that impacts Store Locator Plus.

The bug , or “feature request” (depending whom you ask), has Store Locator Plus working differently when adding a location versus editing a location.    For the sake of discussion I will focus on the featured and rank extended data fields provided in Enhanced Results.

The wpdb insert command will accept a blank field and store it in the database with a NULL value.
Note: After further research this is not always the value.  MOST of the time the data goes in as a zero.  In some cases, I have yet to discover when/why, data goes in as a NULL value.   

The wpdb update command will accept a blank field and convert it to either a blank string or a ZERO if it is updating an integer field such as featured or rank.

In MySQL a NULL value and a 0 (or blank) are VERY different things.

What this means for Store Locator Plus is that adding a location and not putting anything into the rank field or leaving the featured box unchecked writes a record to the database where featured = NULL and rank = NULL.  When updating that same location and leaving those two elements untouched, the save location changes the featured = 0 and rank = 0.

This becomes a BIG problem when sorting results on output, especially with Enhanced Results.     One of the common settings that people are using is order by “Featured, Then Rank, Then Distance”.     In MYSQL this *WAS* being written as a ORDER BY featured DESC, rank ASC, distance ASC sort command on the returned data.

Why is this an issue?

Consider the case where two locations have been created.  Neither location has been setup to use either the featured or rank field.   You start with data like this:

Locatien A  : Featured NULL : Rank NULL : Distance 3 miles
Location B : Featured NULL : Rank NULL : Distance 1 mile

 

Before editing location A the locations were shown on the map search with Location B first then Location A.  That makes sense.   B is closer and since neither was set as featured or ranked it should take precedence.

However location A was edited to fix a typo and location B was not.  You end up with something like this:

Location A  Edited : Featured 0 : Rank 0 : Distance 3 miles
Location B New : Featured NULL : Rank NULL : Distance 1 mile

SLP Featured Rank Null Sort
A real-world example of editing a location changing featured, rank, distance sort order.

Why did featured and rank get set to 0?  Because of the wpdb update bug and how it handles null fields.   This has a drastic change on the search results.    Now when you perform the same exact search you get location A coming up before location B.

Why did the sort order change?

For MySQL 0 has more weight than null.     As such the ORDER BY featured DESC statement will put Location A before Location B because featured = 0 is higher than featured = NULL on the sorting algorithm used by MySQL.

Patches In Progress : Featured

Enhanced Results has a patch in progress that will address the FEATURED field.   That is fairly easy by changing the ORDER BY featured DESC clause to a slightly more complex variant that forces NULL and 0 settings to be equivalently weighted:

ORDER BY case featured is null return 0 case featured is not null return featured end DESC

In other words for sorting purposes turn null into a 0, then sort by that.

This patch works great for featured which is a checkbox.  The only values allowed are 0 (or null) and 1.   Since and unchecked box is always going to be 0 or null we can force it to a specific value.

Patches For Rank

I’m not sure yet how to deal with rank.  While I can force a null rank to be 0 that is not a great idea.    That means all locations will start with a 0 rank.  When you order by rank ASC the zero rank will ALWAYS come first.   That means leaving rank empty and setting other locations to “1”, “2”, and “3” rank values will put them AFTER all the empty/blank ranked locations.

That is not how people will expect that to work.

I’m working through various iterations outside of submitting another patch to WordPress core.  In my experience core patches take far too long to be implemented.  My last patch for dbDelta was submitted nearly a year ago and has yet to be incorporated into core.  The core team has too many other fixes to implement that affect far more people.      Since waiting a year or more for a fix is not acceptable to my user base I need to work around WordPress core shortcomings.

For now the featured entry patch is coming today.   The rank issue needs further investigation.

Update : LEFT JOIN

Turns out a bigger issue with this problem is the default values inserted into a LEFT JOIN if a record does not exist in the extended data table.    I need to either add a record to the extended data table when there is not extended data for a location or find a way to get LEFT JOIN to return ‘0’ instead of NULL on joined fields.