CI4 Generate Empty String On Query Binding Value On "FALSE" value

So, I was updating the version from 4.0.4 to 4.1.0 (and after it to 4.1.1 to see the same bug). But, I have this code:

PHP Code:

$this->model->update(intval($id), ['ispartial' => false]); 


But then it throw error

Code:

CRITICAL - 2021-02-02 18:37:15 --> Incorrect integer value: '' for column `dbname`.`thetable`.`ispartial` at row 1

Here's the log if it's useful:

Code:

CRITICAL - 2021-02-02 18:37:15 --> Incorrect integer value: '' for column `dbname`.`thetable`.`ispartial` at row 1
#0 my-directory-project\server\vendor\codeigniter4\framework\system\Database\MySQLi\Connection.php(314): mysqli->query('UPDATE `incomin...')
#1 my-directory-project\server\vendor\codeigniter4\framework\system\Database\BaseConnection.php(713): CodeIgniter\Database\MySQLi\Connection->execute('UPDATE `incomin...')
#2 my-directory-project\server\vendor\codeigniter4\framework\system\Database\BaseConnection.php(641): CodeIgniter\Database\BaseConnection->simpleQuery('UPDATE `incomin...')
#3 my-directory-project\server\vendor\codeigniter4\framework\system\Database\BaseBuilder.php(2468): CodeIgniter\Database\BaseConnection->query('UPDATE `incomin...', Array, false)
#4 my-directory-project\server\vendor\codeigniter4\framework\system\Model.php(353): CodeIgniter\Database\BaseBuilder->update()
#5 my-directory-project\server\vendor\codeigniter4\framework\system\BaseModel.php(901): CodeIgniter\Model->doUpdate(Array, Array)
#6 my-directory-project\server\vendor\codeigniter4\framework\system\Model.php(737): CodeIgniter\BaseModel->update(Array, Array)
#7 my-directory-project\server\app\Controllers\v1\IncomingData.php(556): CodeIgniter\Model->update(1420140, Array)
#8 my-directory-project\server\vendor\codeigniter4\framework\system\CodeIgniter.php(936): App\Controllers\v1\IncomingData->status()
#9 my-directory-project\server\vendor\codeigniter4\framework\system\CodeIgniter.php(432): CodeIgniter\CodeIgniter->runController(Object(App\Controllers\v1\IncomingData))
#10 my-directory-project\server\vendor\codeigniter4\framework\system\CodeIgniter.php(333): CodeIgniter\CodeIgniter->handleRequest(NULL, Object(Config\Cache), false)
#11 my-directory-project\server\public\index.php(45): CodeIgniter\CodeIgniter->run()
#12 {main}

I thought it was the $id is string, so I add intval. But then when I debug, it's the data. Here's the video of me debugging all of it. Please note: I'm a newbie and not professional or pros or master or anything above, I'm just learning, that's why I add all of the breakpoint even the I-don't-have-to-breakpoint-that line of code.

[Video: https://vimeo.com/507464819]

Because of this, I revert to version 4.0.4, and now it's working very well again. I don't know which part that make the "FALSE" boolean value to empty string. It's working with the "TRUE" tho, but not with the "FALSE". Thank you.

It seems the column `ispartial` type is integer.
So you can't set false to it.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

IF you need t to be true or false use 0 for false and 1 for true.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

It seems the column `ispartial` type is integer.
So you can't set false to it.

It's TINYINT which mysql usually used for boolean type. But, in version 4.0.4 below is just working fine tho. So, seems the v4.1.x not working anymore when using direct bool type, isn't?

---

IF you need t to be true or false use 0 for false and 1 for true.

But it would be for me to replace the whole code that use this method, and there's three project currently use CI4. Also, it's working fine in version 4.0.4, but upgrade it to version 4.1.x is throwing errors, including this one. Why would the version 4.0.4 is just fine with direct bool type, but not with the 4.1.x? that's what I try to find out 

What value was inserted in 4.0.4?
true --> 1?
false --> 0?

Find out where converts bool to int in 4.0.4.
And investigate what's changed in 4.1.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

What value was inserted in 4.0.4?
true --> 1?
false --> 0?

Find out where converts bool to int in 4.0.4.
And investigate what's changed in 4.1.

In that video, is the version of 4.1.x. But, here's a temporary guess I could make.

v4.0.4:
true --> true
false --> false

Then it will be generate the sql like `ispartial` = true, which mysql is so far understand that.

v4.1.x
true --> true
false --> '' <== this, mysql will throw error "empty string", because it's illegal. this version is convert false to empty string.

MySQL does not contain built-in Boolean or Bool data type. They provide a TINYINT data type instead of Boolean or Bool data types. MySQL considered value zero as false and non-zero value as true. If you want to use Boolean literals, use true or false that always evaluates to 0 and 1 value. - javatpoint.com

Tested on query in pma, it's just working fine with "true" or "false". My assumption is, v4.0.4 did not sanitize it or filter it or whatever it is, with the boolean type. 


I couldn't get deep investigation on this since last days was figure it out what cause this issue, and now I'm on deadline. But I will try, thank you.

Now it's not just on false, but with `NULL` value. Idk how to handle this I'm so frustrated.

If your datatype is an integer, you need to use an integer in your code. Not true, not false, not null.

If your datatype is an integer, you need to use an integer in your code. Not true, not false, not null.

I also have a varchar (and other things), which allow NULL on the database (meaning that column is not required to be filled). The 4.0.4/4.0.5 just fine with this, after the 4.1.x is not. And also, I want to save a boolean datatype which mysql did not have, so I use TINYINT which suggested by them. Even in SQL query itself TRUE mean 1 and FALSE mean 0 automatically. I don't know why CI4 convert it to empty string (I turn on the mysql log and the value is just '' instead of, just FALSE) on query binding. The only way I could think of is provide the custom function in model to convert any data that have FALSE value to integer 0 (using intval() function), but I don't know how to handle the NULL which I'm still find a way to trick it.

Also, $this->db->query() in migration didn't work either.

READ:

An Introduction to MySQL BOOLEAN Data Type

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )