In the example below, we have found that the query doesn't always return the correct count of rows when NULL is passed in as the prepared statement parameters. If NULL is used for the first call, then the results are always correct. (This is the second case). If a non NULL parameter is used, then a future call with NULL will return the wrong result set.

This form of SQL is used in our software for reports, in which a parameter could be set to NULL. The 2 parameters are always the same value for a particular execution.

We have additionally found that using the null safe comparator <=> on the string comparison, stops the problem from occurring.

This problem was originally noted using the Maria JDBC connector. With 1.3.6 the code works, but was broken in 1.3.7. I am currently trying to determine the difference here, as to my knowledge both Connector/J versions should use server Prepared Statements.

CREATE TABLE a (a_id INT AUTO_INCREMENT PRIMARY KEY, a_text VARCHAR(20));

CREATE TABLE b (b_id INT AUTO_INCREMENT PRIMARY KEY, b_a_id INT);

INSERT INTO a VALUES (NULL, 'word1');

INSERT INTO b VALUES (NULL, 1);

INSERT INTO b VALUES (NULL, NULL);

PREPARE q FROM 'SELECT * FROM b

 LEFT JOIN a

  ON (a.a_id = b.b_a_id)

WHERE ((? IS NULL) OR (a.a_text = ?))';

SET @var = 'word1';

EXECUTE q USING @var, @var;

/* expect row count 1, actual row count 1 */

EXECUTE q USING @nul, @nul;

/* expect row count = 2, actual row count 1 */

PREPARE q2 FROM 'SELECT * FROM b

 LEFT JOIN a

  ON (a.a_id = b.b_a_id)

WHERE ((? IS NULL) OR (a.a_text = ?))';

SET @var = 'word1';

EXECUTE q2 USING @nul, @nul;

/* expect row count 2, actual row count 2 */

EXECUTE q2 USING @var, @var;

/* expect row count 1, actual row count 1 */

DROP TABLE b, a;