[wp-trac] [WordPress Trac] #43589: WPDB Update change NULL values to acceptable values

[WordPress Trac]

#43589: WPDB Update change NULL values to acceptable values
--------------------------+------------------------------
 Reporter:  dorianrd      |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Changes (by soulseekah):

 * keywords:  needs-patch needs-unit-tests =>


Comment:

 The `wpdb::prepare` warnings are thrown when inserting a single NULL
 value.
 This happens inside `_insert_replace_helper` where in this part of the
 code `$this->query( $this->prepare( $sql, $values ) );` the `$values` is
 an empty array. For a single NULL value there are no placeholders and no
 values.

 Regarding the update issue. My mistake, this is indeed a MySQL issue. I
 thought strict mode was assumed by default in WordPress (alas #21212).
 MySQL does not generate an error, rather generates a warning. I have
 included tests for this.

 {{{
 UPDATE `test2` SET `title` = NULL WHERE `title` = '2';
 Rows matched: 1  Changed: 1  Warnings: 1
 SHOW WARNINGS;
 +---------+------+-------------------------------+
 | Level   | Code | Message                       |
 +---------+------+-------------------------------+
 | Warning | 1048 | Column 'title' cannot be null |
 +---------+------+-------------------------------+
 }}}

 It is, of course, possible to set MySQL into STRICT MODE by issuing a `SET
 sql_mode = 'STRICT_ALL_TABLES';` query
 (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict) in
 order to force it to produce errors where warnings are produced by
 default. But be careful with this. A lot in core is supposed to break
 under strict mode, and definitely third-party plugins and themes will
 break.

 Tests for this are also present in 43989.diff. While this report is
 invalid, it did help uncover two warnings from `$wpdb->prepare` and added
 some new tests for redundancy.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/43589#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform