alotacents commented 6 years ago

What steps will reproduce the problem?

In 64bit PHP create a Bit(34) column set a value for example to 75 when you query the result via ActiveRecord result is 0;

What is the expected result?

Expect result is either a binary string value \0\0\0\0K like from PDO sql query, or after typecasting a integer value 75.

What do you get instead?

integer value 0.

Seems that the yii/mysql/schema set bit to integer if the size is equal 32 or less and bigint greater than 32. Then when the getColumnPhpType determines string or integer based on PHP_INT_SIZE it is looking at unsigned values which bit don't have.

How I fixed the issue was in the \yii\db\ColumnSchema file where it does typecasting I modified the two functions phpType and dbType

public function phpTypecast($value)
    {

        if ($value === '' && !in_array($this->type, self::$textTypes, true)) {
            //if ($value === '' && $this->type !== Schema::TYPE_TEXT && $this->type !== Schema::TYPE_STRING && $this->type !== Schema::TYPE_BINARY && $this->type !== Schema::TYPE_CHAR) {
            return null;
        }
        if ($value === null || $value instanceof Expression || $value instanceof Query) {
            return $value;
        }
if gmp library recommended
        if (strncasecmp($this->dbType, 'bit', 3) === 0) {
            if (($number = gmp_import($value)) !== false) {
                $value = gmp_strval($number, 10);
            }
if no library 
             return (string) hexdec(bin2hex($value));
        }

        // needs to be after bit binary to string conversion otherwise binary string doesn't get converted
        if(gettype($value) === $this->phpType){
            return $value;
        }

        return $this->typecast($value);
    }

    /**
     * Converts the input value according to [[type]] and [[dbType]] for use in a db query.
     * If the value is null or an [[Expression]], it will not be converted.
     * @param mixed $value input value
     * @return mixed converted value. This may also be an array containing the value as the first element
     * and the PDO type as the second element.
     */
    public function dbTypecast($value)
    {

        if ($value === '' && !in_array($this->type, self::$textTypes, true)) {
            return null;
        }
        if ($value === null || $value instanceof Expression || $value instanceof Query) {
            return $value;
        }

        if (strncasecmp($this->dbType, 'bit', 3) === 0) {
if gmp library recommended
            if (($number = gmp_init($value, 10)) !== false) {
                $value = gmp_export($number);
                return $value;
            }
if no library 
             return pack('H*', dechex($value));
        }

        if(gettype($value) === $this->phpType){
            return $value;
        }

        // the default implementation does the same as casting for PHP, but it should be possible
        // to override this with annotation of explicit PDO type.
        return $this->typecast($value);
    }

also took the first 2 if statements in typecast function

protected function typecast($value)
    {
        /* remove used in the phpTypecast and dbTypecast
        if ($value === '' && $this->type !== Schema::TYPE_TEXT && $this->type !== Schema::TYPE_STRING && $this->type !== Schema::TYPE_BINARY && $this->type !== Schema::TYPE_CHAR) {
            return null;
        }
        if ($value === null || gettype($value) === $this->phpType || $value instanceof Expression || $value instanceof Query) {
            return $value;
        }
        */

and modified \yii\db\Schema getColumnPhpType for handling bit

protected function getColumnPhpType($column)
    {
        static $typeMap = [
            // abstract type => php type
            'smallint' => 'integer',
            'integer' => 'integer',
            'bigint' => 'integer',
            'boolean' => 'boolean',
            'float' => 'double',
            'double' => 'double',
            'binary' => 'resource',
        ];
        if (isset($typeMap[$column->type])) {
            // bit is unsigned but if you want to fit the bit into integer check the max php integer byte size
            if (strncasecmp($column->dbType,  'bit', 3) === 0) {
                return $column->size < (PHP_INT_SIZE * 8) ? 'integer' : 'string';
            } elseif ($column->type === 'bigint') {
                return PHP_INT_SIZE === 8 && !$column->unsigned ? 'integer' : 'string';
            } elseif ($column->type === 'integer') {
                return PHP_INT_SIZE === 4 && $column->unsigned ? 'string' : 'integer';
            }

            return $typeMap[$column->type];
        }

        return 'string';
    }

Also saw some code on setting bit(1) to boolean I would like to recommend determining boolean if the column comment contains the word boolean. I know that is a extra step to make sure someone writes boolean in the column comment but I rather make the choice on what I want to be returned in a active record then for the code to always change a column of certain setup. Also having a comment saying boolean helps someone determine hey this field is being used for a boolean value. So say it was used on tinyint(1) I can know only values expect are NULL, 0, and 1.

Additional info

Q	A
Yii version	2.0.13.1
PHP version	5.6.32
MySQL	5.6.32-78.1
Operating system	GNU/Linux 3.10.0-614.10.2.lve1.4.50.el6h.x86_64

njasm commented 6 years ago

seems related or in scope of #15455 but on MSSQL.

alotacents commented 6 years ago

That one seem related to MSSQL loadColumnSchema is getting the SQL raw default value. Which in the issue states it has parentheses around it. Maybe for MSSQL loadColumnSchema needs to detect bit and do bindec(trim($info['default'], '()')); to the default value like in MySQL it does bindec(trim($info['default'], 'b\'')); This issue is more related that the yii/db/Schema typecasting it's not even trying to convert the raw database bit binary string to a integer string.

cebe commented 6 years ago

yiisoft / yii2

MySQL handling of bit values incorrectly #15471

What steps will reproduce the problem?

What is the expected result?

What do you get instead?

Additional info