WDK cache mystery in postgres (null vs empty string)

[ryanrdoherty]

Summary of "solved" issue:

In Oracle, empty varchar2 column values are treated as NULL by Oracle, creating a class of bugs like:

  insert into table1 (varcharrow) values ('');  // creates a row with a null value in varcharrow
  select * from table1 where varcharrow = ''; // does NOT return our new row because '' != NULL

This is a little crazy!

Postgres fixes this issue but any code that depends on the insertion of empty strings resulting in null values will newly break in PG. Recently this change was made to purposefully insert nulls into PG varchar col if a WSF plugin returns empty strings (the only way they can return empty/missing values).
9949af8

The issue was the text search plugin (calling out to SOLR), which is set to uncacheable. The org filter (column filter) reporter was unable to select any partition keys for the cache table resulting from the search because the search fills in only gene_source_id and depends on post-insert-sql to fill in the source_id (transcript ID). However, that SQL only filled rows where source_id is null, which was true in Oracle (empty string) but not in postgres. Thus the column was not being filled in, so no IDs matched partition keys, so the attribute query could not run.

A remaining mystery is that the primary result page panel (rows of results) DID work ok. Somehow we were inserting nulls for source_id in that case (paged reporter) but not for the org filter (single attribute query reporter). So, maybe undo the fix above and discover why this was working in the results pane case but not the org filter reporter, as it may have other consequences. Would like to understand this.