[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:  reporter-feedback  |     Focuses:
-------------------------------+------------------------------

Comment (by dorianrd):

 Hi @dd32,

 Here are some set of commands to reproduce it :


 > Table creation

 {{{

 DROP TABLE IF EXISTS `wp_2_wsc_test`;
 CREATE TABLE IF NOT EXISTS `wp_2_wsc_test` (
   `title` varchar(250) NOT NULL,
   `address` json NOT NULL,
   `lat` float(10,6) NOT NULL,
   `count` float(10,6) NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
 )

 }}}


 > PHP commandes


 {{{#!php
 <?php

 global $wpdb;
 //=>return false. Line not created (> expected behaviour OK)
 $ret = $wpdb->insert("wp_2_wsc_test", array('count'=>1,
 'title'=>null,'address'=>null,'lat'=>null), array('%d', '%s', '%s',
 '%f'));

 //=>return 1. Created (expected behaviour OK)
 $ret = $wpdb->insert("wp_2_wsc_test", array('count'=>1,
 'title'=>"etet",'address'=>'{"value":"1"}','lat'=>5.5), array('%d', '%s',
 '%s', '%f'));

 //=>return 1. Updated with some values (expected behaviour KO)
 $ret = $wpdb->update("wp_2_wsc_test", array('count'=>1,
 'title'=>null,'address'=>null,'lat'=>null), array('id'=>1),  array('%d',
 '%s', '%s', '%f'));

 }}}

 These are the values updated:
 title => ""
 address => "null"
 lat => "0.000000"
 count => "1"
 id => "1"

 This is the SQL command generated with $wpdb->last_query :
 UPDATE `wp_2_wsc_test` SET `count` = '1', `title` = NULL, `address` =
 NULL, `lat` = NULL WHERE `id` = '1'



 Thus :
 - Insert behaviour is OK
 - Update behaviour is not OK
 I don't know if it's a Wordpress or SQL issue, but when I don't use the
 commands $wpdb->update but the php commands:

 {{{#!php
 <?php
 $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
 $result = $conn->query($req);
 $conn->close();
 }}}

 The line is not updated. It seems that the update is caused by the
 wpdb->update function.

 Let me know if you need more information

 Have a good day

 Dorian

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