Bit(1) values being fetch as empty string

[odahcam]

For some reason all the columns I have in my database that are of type Bit(1) (I didn't check for other Bit sizes) are being fetch as empty strings, I don't know exactly what to look for in order to further investigate this, sorry about the lack of information but I'm open to collaborate to get more data if you give me some light.

In a script as simple as this I can reproduce the issue I'm facing:

<?php

require_once __DIR__ . '/vendor/autoload.php';

use Amp\Mysql\MysqlConfig;
use Amp\Mysql\MysqlConnectionPool;

$config = MysqlConfig::fromString(
    "host=localhost user=oneticket password=oneticket db=oneticket"
);

$pool = new MysqlConnectionPool($config);

$statement = $pool->prepare("SELECT * FROM <my_table>");

$result = $statement->execute();
foreach ($result as $row) {
    // $row is an associative-array of column values, e.g.: $row['column_name']
    print_r($row);
}

The columns of type smallint works just as expected. Before, with PDO, all BIT columns would return as integers so this is currently a big problem for me.

---

Just to be clear I'm trying to use AMP MySQL as a PDO replacement for Eloquent (based on https://github.com/xpader/amphp-eloquent-mysql) and its been super positive so far, the only issue I have now is with this BIT column.

[odahcam]

Okay, after some thinking I figured the strings weren't empty and I was missing an empty space that could mean only one thing, they were byte strings. For an instant I forgot about that wonderful PHP thing (I don't like that). Well, so I looked for where the byte values are converted to PHP values:

Then I made some tests and my thinking was right, I could successfully convert the BIT values to int from the byte string:

Now I know this is not a bug, it is by design. But I don't understand why, since PDO would simply convert the BIT to an integer I was expecting the same behavior. I think BIT values in MySQL are closer to integers. Although they’re stored as binary data, they represent numeric bit-fields and are used in bitwise operations, which aligns them more with integers than with strings. Is there a specific reason why you've implemented this, this way? Would you be willing to accept a change on that behavior?

[bwoebi]

As to why it's that way: https://dev.mysql.com/doc/dev/mysql-server/8.4.3/page_protocol_binary_resultset.html#sect_protocol_binary_resultset_row_value_string

According to the protocol it's sent as a length encoded string.

I see that php-src has special handling for BIT. https://github.com/php/php-src/blob/master/ext/mysqlnd/mysqlnd_wireprotocol.c#L1632

We probably should replicate it for amphp/mysql. But simply parsing as tinyint is not correct.

[bwoebi]

I.e. what PHP does is taking the binary string and converting it to int, if the number fits into int, otherwise into string.

This is not really trivial, like a straightforward test shows:

php -r 'include "vendor/autoload.php"; $pdo = (new Amp\Mysql\SocketMysqlConnector)->connect(Amp\Mysql\MysqlConfig::fromString("host=/var/run/mysqld/mysqld.sock user=root db=test")); $pdo->query("CREATE TEMPORARY TABLE tmp (a BIT(64))"); $pdo->query("insert into tmp values (18446744073709551615)"); $q = $pdo->query("select a from tmp"); var_dump(hexdec(bin2hex($q->fetchRow()["a"])));'
float(1.8446744073709552E+19)

hexdec converts to float. base_convert does also (and then back to string, wtf).

Using gmp obviously works, but it's rather imperfect to make this depend on gmp.

[bwoebi]

Actually we can probably combine str_pad with unpack().

[odahcam]

@bwoebi should that be something to consider updating in the PR I have open?

[odahcam]

Also, in case this doesn't go forward, is there a way of extending the driver so I can customize the BIT value parsing for my project?

[odahcam]

Hello! I hope you're doing well. Any news around this topic?