vlaudizio wrote:

SD_Filter.php file patched

Attached:

vlaudizio wrote:

SD_AppliedFilter.php file patched

Attached:

vlaudizio wrote:

Possible fixes in the attached files. Below a short description:

1. To find a solution, I tried to replicate the error by using the postgres client (psql).

Since the semantic_drilldown_values and the semantic_drilldown_filter_values are temporary tables, i created them on the fly before performing the offending query.

CREATE TEMPORARY TABLE semantic_drilldown_values ( id INT NOT NULL );

CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT
s_id AS id, o_ids.smw_title AS value FROM "smw_di_wikipage" JOIN
"smw_object_ids" p_ids ON "smw_di_wikipage".p_id = p_ids.smw_id JOIN
"smw_object_ids" o_ids ON "smw_di_wikipage".o_id = o_ids.smw_id
WHERE p_ids.smw_title = 'Simple_title';

I even changed the NOT operator in the offending query by using the one
allowed in postgres:

SELECT COUNT(DISTINCT sdv.id) FROM semantic_drilldown_values sdv
LEFT OUTER JOIN semantic_drilldown_filter_values sdfv ON sdv.id =
sdfv.id WHERE ((not ((sdfv.value IS NULL) OR (sdfv.value = '') OR
(sdfv.value = '0') OR (sdfv.value = '1'))));

If I perform the query into psql, I get no error:

  count
-------
      0
(1 row)

Going over many possible solutions, the one working was to modify the offending query in the SemanticDrillDown extension code (SD_AppliedFilter.php file) in order to skip the blank string check.
But in this way, I'm not sure if the patch fits the expected SemanticDrillDown behaviour. Is there a way for keeping the blank string check?

2. Postgres does not allow the slash escape by default. As a workaround the postgres configuration can be modified by changing the following property in the postgresql.conf file:
   
   backslash_quote = on standard_conforming_strings = off

Unfortunately, the mediawiki overwrites the standard_conforming_strings value (as stated in the includes/db/DatabasePostgres.php file):

$this->query( "SET standard_conforming_strings = on", __METHOD__ );

To solve this problem without changing the mediawiki core file, I modified the SD code (SD_Filter.php file).
My changes are simple: the special character quote is replaced with a double quote sequence when postgres is the database.

Since the mediawiki code has several utility methods for managing databases differences, I suggest to start using them within the SD code (if possible).

Change 114763 had a related patch set uploaded by Vincenzo Laudizio:
Fixed SemanticDrillDown generated sql queries for working with Postgres

https://gerrit.wikimedia.org/r/114763

Change 114763 merged by jenkins-bot:
Fixed SemanticDrillDown generated sql queries for working with Postgres

https://gerrit.wikimedia.org/r/114763

Thanks for all your work on this! Hopefully this is now fixed, thanks to your patch.