IF() always returns the else branch #517

IF() returns its third argument whatever the condition is, so any plugin query using IF() gets the wrong result on SQLite.

Reproduced on a vanilla WordPress Playground (Playground CLI 3.1.56, which bundles this plugin at 3.0.2, PHP 8.3, latest WordPress, no other plugins), through $wpdb->get_var():

SELECT IF(1 = 1, 'yes', 'no')                                     => 'no'
SELECT IF(1, 'yes', 'no')                                         => 'no'
SELECT IF(0, 'yes', 'no')                                         => 'no'
SELECT IF('a' = 'a', 'yes', 'no')                                 => 'no'
SELECT IF(ID > 0, 'yes', 'no') FROM wp_posts ORDER BY ID LIMIT 1  => 'no'
SELECT CASE WHEN 1 = 1 THEN 'yes' ELSE 'no' END                   => 'yes'

Cause: WP_SQLite_PDO_User_Defined_Functions::_if() (class-wp-sqlite-pdo-user-defined-functions.php, line 579 on the default branch) does

return ( true === $expression ) ? $truthy : $falsy;

SQLite passes a comparison result to the user-defined function as the integer 1 or 0, never PHP true, so the strict comparison is always false.

MySQL treats the condition as true when it's neither NULL nor zero as a number, converting strings to numbers first (IF('abc', ...) is false, IF('1', ...) and IF('2abc', ...) are true). This matches it, and I've checked it against MariaDB as the reference for 16 conditions:

public function _if( $expression, $truthy, $falsy ) {
	if ( null === $expression ) {
		return $falsy;
	}
	if ( is_string( $expression ) ) {
		$expression = is_numeric( trim( $expression ) ) ? (float) trim( $expression ) : (float) $expression;
	}
	return ( 0.0 != (float) $expression ) ? $truthy : $falsy;
}

(A plain 0 != $expression isn't enough on PHP 8, where 0 != 'abc' is true.)

How I hit it: an INSERT ... SELECT IF(<mapped>, it.post_type, 'post') ... in my import plugin created every product as a post on Playground. I've worked round it by registering my own IF on the PDO connection when running on SQLite, but a fix here would help anyone else using IF().

Happy to send a PR if that's useful.

Cheers, Dave