Problem with MySQL boolean and Phalcon 4.0

I see this as a bug.

Nope, you just entered to the world where MySQL doesn't have BOOL type.
Because in MySQL BOOL = TINYINT(1).

Which means, that you need to specify 1 or 0 or write as you (int)false

I disagree, Anton.

In the CREATE TABLE statement, I used the 'boolean' datatype. That MySQL (or MariaDb) implements that as tinyint(1) shouldn't have to bother me. When a field is of type 'tinyint' length 1, it should be considered a boolean. Assigning a boolean to it, should not give an error.

And note that the error message doesn't describe the problem at all, which is a clear sign that this is a bug.

But anyway, how come that when assigning a boolean true, there is no error message?

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

test boolean NOT NULL

By creating column like that, you can't insert empty '' value inside row.

In PHP false == '', but false !== ''

So if want to not have that error, and do not change it inside PHP, just create DEFAULT '0' for test column:

`test` boolean NOT NULL DEFAULT '0'

You're telling me nothing new, Anton. I've been working with PHP and MySQL for over 17 years.

Anyway, in my tests I did not insert empty strings. I inserted booleans. Because that is what the issue is about: There is a problem that seems to be with booleans, and particularly boolean false.

Without having looked at the actual Zephir code, my guess is that there is a check on input for a field not being filled. That check probably confuses a boolean false value with the input for the field not being filled. The error message suggests that an empty string is inserted when there is a boolean false. Perhaps the type is changed to the default string in that case, and the boolean false is cast to that, giving an empty string.

None of checks are made with empty values, only if value is null.

https://github.com/phalcon/cphalcon/blob/b28b0a2c8a39fdb505d0529c8994aec60462ae57/phalcon/Db/Adapter/AbstractAdapter.zep#L795

It just aggregate values and pass to PDO execute() method

for over 17 years.

Most of this years PHP wasn't strict with values.

Anyway, if you are really think that it is an issue, please create an issue in phalcon repository - https://github.com/phalcon/cphalcon/issues
And I'll pass it to team to think about that issue more..

Most of this years PHP wasn't strict with values.

But the years before that with other languages, like Delphi, Turbo Pascal for Windows, and others, was.

I decided to implement a DRY solution to prevent the problem, and not create an issue for you. I presented code to reproduce the problem in my opening post, with which you can easily create an issue yourself. The only thing missing for that is the PHP version, which is 7.4.2, and the Phalcon version, 4.0.4.

I'm going to watch whether the Phalcon Team cares whether developers get incomprehensible error messages and consequently need to put lots of time into solving problems, just because they want to persist booleans into a database.

Anyway, what I saw in that .zep code that resembles my guess of what the problem is, worries me.

If you saw .zep code, then you could notice 3d param of insertAsDist() method, which is $dataTypes.

So, if you want to cast some of values into specific type, you can do it like that:

$data = ['id' => null, 'test' => true];
$this->db->insertAsDict('test_bool', $data, [
    'test' => Column::BIND_PARAM_BOOL,
]);

I did check that out in the documentation, before posting here. After I started writing that, I realized how much work that would be, without a guaranteed chance of success, and didn't continue.

After all, everything just worked fine with Phalcon 3.4.

Please note that the forums are not the best place to report bugs. They're great for discussion and determining if something really is a bug, but not reporting them.

If you truly think this is a bug or a regression, please help by posting a bug report on Github. The developers surely care, but they can only care about problems they know about.

Thanks for clarifying, Dylan.

When I studied at a technical university, many years ago, I learned that there are 2 basic ways to write booleans: using 0 and 1, and false and true. What I learned is that they are equivalent. 0 is equivalent to false, and 1 is equivalent to true. There is no inherent difference between implementing for example NAND using an IC like SN 7400N where the specifications use 0 and 1 for booleans, and implementing it in a programming language like PHP as !($a && $b) where booleans are usually indicated with false and true. (At the technical university, I was taught Pascal for programming, that I later used a lot in Delphi).

So, to me the implementation of booleans by MySQL as 0 and 1, and the accompanying MySQL constants FALSE and TRUE, is nothing different from the PHP implentation with false and true, that can be cast to int 0 and int 1. I expect classes that interface between MySQL and PHP to handle that equivalence for me.

If the Phalcon team would implement the not very pragmatic option of throwing a Phalcon exception when someone tries to persist a PHP boolean into a MySQL database table, I would need to continue using my DRY solution that I have in place now. I'm not going to repeatedly use (int) $someBoolean, that's bad programming practice, or WET, so to speak.

I would keep using this, or adapt it to handle boolean true as well:

namespace MyNamespace\Library\Phalcon4\Db\Adapter\Pdo;

class Mysql extends \Phalcon\Db\Adapter\Pdo\Mysql
{

    public function insertAsDict($table, $data, $dataTypes = null ): bool
    {
        foreach ($data as $field => &$value) {
            if ($value === false) {
                $value = 0;
            }
        }

        return parent::insertAsDict($table, $data, $dataTypes);
    }
}

(It would probably be better to overrride the insert() function, but I'm not using that one anywhere.)