Search index process broken on mysql >= 5.0.13

I wanted to change this... mmmm

I just tried to review this patch using MySQL 5.0.24 (PHP 5.1.6 on WinXPsp2). But actually indexing works fine even without this patch! Are there any special settings/modules/... required to reproduce this bug?

I think you need comment module disabled and a node added in this state. That node won't be indexed.

Thanks, I didnt realize that comment.module is enabled by default ;)

To review I added 95 random nodes, then disabled the comment.module and added another 5 with known (searchable) content. On the search settings page AFTER the cron run the 'Indexing status' was 95%, what (I think) means the 95 random nodes were indexed but the other 5 were not. But actually performing a search (for a word in the known nodes) I got results even from the 5 (not indexed?) nodes.
With this patch the index status AFTER cron is 100% and all content seems indexed.

BUT the patch doesnt work with PostgreSQL!

Warning: pg_query() [function.pg-query]: Query failed: ERROR: function if(boolean, integer, integer) does not exist
in C:\Programme\xampp\htdocs\drupalcvs\includes\database.pgsql.inc on line 120

Warning: ERROR: function if(boolean, integer, integer) does not exist
query: SELECT GREATEST(IF(c.last_comment_timestamp IS NULL, 0, c.last_comment_timestamp), n.changed) as last_change, n.nid FROM node n LEFT JOIN node_comment_statistics c ON n.nid = c.nid WHERE n.status = 1 AND ((GREATEST(n.changed, c.last_comment_timestamp) = 0 AND n.nid > 0) OR (n.changed > 0 OR c.last_comment_timestamp > 0)) ORDER BY GREATEST(n.changed, c.last_comment_timestamp) ASC, n.nid ASC LIMIT 20 OFFSET 0 in C:\Programme\xampp\htdocs\drupalcvs\includes\database.pgsql.inc on line 139

I'm not a PostgreSQL/SQL expert, so I dont know there is a solution that works with every db. Maybe we need a switch() {} as in hook_install to get a db-specific solution!?

Your pgsql install is wrong, there was a time when a bug in system.install caused the functions not install.. Try a fresh setup.

      db_query('CREATE OR REPLACE FUNCTION "if"(boolean, integer, integer) RETURNS integer AS
        \'SELECT CASE WHEN $1 THEN $2 ELSE $3 END;\'
        LANGUAGE \'sql\''
      );

@chx: You are right, I tried with latest code but using an existing db. The FUNCTION "if" stuff is only added in hook_install, but not in a hook_update_x, what means it is not available after an upgrade to Drupal 5, right?

However the patch works nicely with a clean install on both MySQL and PostgreSQL. RTBC?

the if function was already added to a 4.7 database in database.pgsql and will still be available after an upgrade.

Another solution might be to make last_comment_timestamp default to '0' instead of NULL. Not sure if that has any implications, but from a code's point of view, it might be a tad more elegant.

With that in mind, I decided to look at the database definition and it reads like this:

modules/system/system.install:      last_comment_timestamp int NOT NULL default '0',
modules/system/system.install:      last_comment_timestamp integer NOT NULL default '0',

Wouldn't that mean that GREATEST() can never return NULL? Both changed and last_comment_timestamp should be non-NULL ...

Stupid me. The NULL comes from the LEFT JOIN, of course.

Looks like this change actually improves compatibility with other databases: see http://bugs.mysql.com/bug.php?id=15610.

Reviewed the code and it looks good to me.

Committed to DRUPAL-4-7 and CVS HEAD. Thanks.