Home » Database » DB » Bug #10211

[2007-02-27 19:31 UTC] mbuchmann at shaw dot ca

Description:
------------
MySQL introduced true 'bit' fields in the 5.x series, superceding the tinyint(1) fields they'd been using to fake them in previous versions.

DB 1.7.9 (haven't tested previous versions, but they're very likely to be also affected) report these bit fields as type "unknown" and default them to a 'true' value for boolean true/false, no matter what's actually stored in the field.
NULL values come through as blank, as expected.

Workaround is the modify the query to CAST() the bit fields to an unsigned integer.

Test script:
---------------
MySQL:

CREATE TABLE bugsample (
bitfield BIT default FALSE
);
INSERT INTO bugsample (bitfield) VALUES (true), (false), (NULL); // create 3 records with the extended insert syntax

PHP:

$query = <<<EOF
SELECT bitfield AS buggy,
CAST(bitfield AS unsigned integer) AS fixed
FROM bugsample;
EOF;

$sth = $dbh->query($query);
print_r($dbh->tableInfo($sth));
while(list($buggy, $fixed) = $sth->fetchRow()) {
$x = ($buggy) ? 'true' : 'false';
print("buggy: $buggy/$x fixed: $fixed\n");
}

Expected result:
----------------
Array
(
[0] => Array
(
[table] => bugsample
[name] => buggy
[type] => bit <----!!!!!!
[len] => 1
[flags] =>
)

[1] => Array
(
[table] =>
[name] => fixed
[type] => int
[len] => 1
[flags] => unsigned binary
)

)

buggy: /true fixed: 1
buggy: ?/false fixed: 0 <---!!!!!!!
buggy: /false fixed:

Actual result:
--------------
Array
(
[0] => Array
(
[table] => bugsample
[name] => buggy
[type] => unknown
[len] => 1
[flags] =>
)

[1] => Array
(
[table] =>
[name] => fixed
[type] => int
[len] => 1
[flags] => unsigned binary
)

)

buggy: /true fixed: 1
buggy: ?/true fixed: 0 <---!!!!!!
buggy: /false fixed: