Boolean MySQL migration vs. auto-model-attribute-typecast inconsistency

[karelvasicek]

• TINYINT(1) column is created using $this->boolean() in migration
• BIT(1) column is type-casted to bool during model population (TINYINT(1) column is type-casted as integer)

[terabytesoftw]

@rob006 @bizley What would be the best way to solve this problem.

[bizley]

I would check typecasting process.

[rob006]

Maybe we should have the same condition in MySQL Schema as for SQLite:

yii2/framework/db/sqlite/Schema.php

Line 333 in 5f3d36e

if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) {

[terabytesoftw]

Maybe we should have the same condition in MySQL Schema as for SQLite:

yii2/framework/db/sqlite/Schema.php

Line 333 in 5f3d36e

if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) {

The change seems correct to me, but it will be BC, since it currently returns integer.

yii2/tests/framework/db/SchemaTest.php

Line 431 in 5f3d36e

'type' => 'tinyint',

[VladimirRakovich]

If you look at the mysql documentation, you can see that this data type can be -128...127 signed and 0...255 unsigned. tinyint(1) doesn't equal the boolean type and this change will potentially break a lot of projects

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

[rob006]

Why would you use tinyint(1) if you want just tinyint? Defining length does not create any additional constrains and does not make data more compact.

[terabytesoftw]

Document

yii2/framework/db/QueryBuilder.php

Line 1183 in 6804fbe

* - `boolean`: boolean type, will be converted into "tinyint(1)"

[schmunk42]

I also stumbled upon this ... and I asked myself the following question:

Why did $this->boolean() in migrations not simply create a BIT(1) column? Which was always typecasted to boolean.

[schmunk42]

Why would you use tinyint(1) if you want just tinyint? Defining length does not create any additional constrains and does not make data more compact.

For new projects this would be fine, but I think @VladimirRakovich comment was about existing projects, where model values are now boolean instead of integer - this should be a concern, especially with PHP 8.1 and above.

[schmunk42]

@samdark

As for doing it at schema level, it may break things.

yiisoft/yii2-gii#440 (comment)

Why did you change your mind :) ?

[bizley]

I think this is because of MySQL docs where it was mentioned as a preferred implementation of boolean column (I hope I remember it correctly and not making it up).

[terabytesoftw]

This is an old bug, which was fixed according to the Yii2 documentation.

[rob006]

For new projects this would be fine, but I think @VladimirRakovich comment was about existing projects

My questions stands also for existing projects - why would you specify length as 1 for tinyint? It does not have any practical effect, and for me it was always used to indicate that this is a boolean column.

[schmunk42]

My questions stands also for existing projects - why would you specify length as 1 for tinyint? It does not have any practical effect, and for me it was always used to indicate that this is a boolean column.

Yeah, but this also works the other way :)
Since it has no practical effect why bother; or maybe you work against an existing database where this is actually used to store values from 0, 255. Such an application would be completely broken.

[schmunk42]

I think this is because of MySQL docs where it was mentioned as a preferred implementation of boolean column (I hope I remember it correctly and not making it up).

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

But the alias does not really apply vice-versa. Sorry, according to the docs it's:

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

[rob006]

Do we have a way to overwrite this detected type in somewhat convenient manner? I still think that tinyint(1) should be treated as bool by default, but it would be worth to add a way to overwrite detected type and handle edge cases when tinyint(1) actually represents a number.

[handcode]

IMHO it is simply wrong to assume in the schema that EVERY tinyint col (no matter if with or without length) is a boolean.
https://github.com/terabytesoftw/yii2/blob/b40de6a5380819472f5bde6c5d120e9c83146c87/framework/db/mysql/Schema.php#L282

this is valid (My)SQL and the state column is clearly NOT a boolean.

CREATE TABLE `dev1` (
       `id` int(11) NOT NULL,
       `state` tinyint(1) unsigned DEFAULT 0,
       PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

INSERT INTO `dev1` (`id`, `state`) VALUES (1, 255);

select * from `dev1`;
+----+-------+
| id | state |
+----+-------+
|  1 |   255 |
+----+-------+

And for MySQL the length param for tinyint has no effect. The possible range of values is only dependent on signed or unsigned.

[terabytesoftw]

If you are right in this case, we can fix it, we just have to define whether the type tinyint(1) will be boolean or not, this is a very old case that we are simply trying to solve.

[rob006]

If you are right in this case, we can fix it

How? Column schema does not check what values are stored in this column.

[terabytesoftw]

If you are right in this case, we can fix it

How? Column schema does not check what values are stored in this column.

yii2/framework/db/mysql/Schema.php

Line 282 in 6804fbe

if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) {

[handcode]

If you are right in this case, we can fix it

How? Column schema does not check what values are stored in this column.

And even if you look at what is stored so far, how is schema supposed to know what i want to store tomorrow?

[rob006]

That is why I propose a way to define types of columns in config and keep current type detection as a fallback. For me this guessing PHP type from DB schema was always a bit too magic, I would prefer to keep these definitions as part of the code with explicitly defined defaults or types.

[schmunk42]

Example and discussion from a non-PHP project: metabase/metabase#3506

tldr: They implemented a driver flag tinyInt1IsBit=false where TINYINT(1) columns treated as numbers instead of booleans.

We have the manual saying TINYINT(1) and BOOLEAN are synonyms, although it is not enforced on db-level.
So you could argue, that using TINYINT(1) for values other than 0 and 1 is "wrong" - but yeah, this is more a made-up argument.

[terabytesoftw]

I just did a test for the boolean column, it is a Yii2 mapping, which phpTypeCast will always return a boolean value, be it tinyint(1) or bit(1).

[schmunk42]

Just for the record ... I did some tests on the db-level.

column       length
-------------------------
tiny1        1
tinynolen    4
tiny5        5
tiny0        4

tinyint and tinyint(0) is tinyint(4)

[handcode]

@terabytesoftw
sorry, but if it is boolean or not has nothing to do with signed or unsigned. the signed/unsigned flag "just" define the possible range for tinyint.
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Still valid SQL but still NO boolean col:

CREATE TABLE `dev1` (
       `id` int(11) NOT NULL,
       `state` tinyint(1) DEFAULT 0,
       PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

INSERT INTO `dev1` (`id`, `state`) VALUES (1, 127);

select * from dev1;
+----+-------+
| id | state |
+----+-------+
|  1 |   127 |
+----+-------+

To be clear: i understand the problem, that MySQL has no real boolean column type, and that the docs state that boolean "is" a tinyint(1), but the assumption the other way around, that every tinyint(1) is a boolean is simply wrong.

[terabytesoftw]

related:

#9339
#9324
#15471
#9324 (comment)

[schmunk42]

That is why I propose a way to define types of columns in config and keep current type detection as a fallback. For me this guessing PHP type from DB schema was always a bit too magic, I would prefer to keep these definitions as part of the code with explicitly defined defaults or types.

@rob006 How would that look like?

Also related in general:

• Disable TINYINT(1) interpretation as BOOLEAN? propelorm/Propel2#1490

[VladimirRakovich]

related:

#9339 #9324 #15471 #9324 (comment)

#9339 (comment)
🙂

[terabytesoftw]

After investigating all the yii2 issues, i also saw that yii2 supports legacy versions of Mysql in its versions 5, if it is correct that tinyint(1) would be synonymous with boolean, but not tinyint(1) unsigned which would be integer, then for me two solutions.

1.- Check the case when tinyint is unsigned, and it will work fine.

Example:

https://mysqlconnector.net/api/mysqlconnector/mysqlconnectionstringbuilder/treattinyasboolean/

2.- Let's simply reverse the commit, and this problem should never have existed.

It's my two cents.

[rob006]

@rob006 How would that look like?

I will look into it and try to propose a solution.

[terabytesoftw]

btw in Yii3, we use bit(1), but that here would be BC.

[handcode]

if it is correct that tinyint(1) would be synonymous with boolean, but not tinyint(1) unsigned which would be integer, then for me two solutions.

• boolean is "stored" as integers 0|1 in a tinyint col because there is no real boolean type
• tinyint signed or unsigned are integers not booleans

see: #19808 (comment)

Why here, because of this one word synonyms from the doc, it is pretended that there is a defined column data type Boolean, and that this Boolean type is tinyint(1), I honestly don't understand. MySQL has no defined storage type for boolean. If there would be one, it would surely be called boolean and stored as boolean and it would not be allowed to store an integer 42 in this col.

Just open a MySQL and try it yourself what you can write into a tiniyint field and what you get out of it.

If you still think it's a good idea to always cast the value from a tinyint(1) column to the php type boolean, go ahead.

This would be obviously a bug and will potentially break perfectly valid code, but hey, we would have a boolean or at least we could pretend that it is so.

I'm out.

[terabytesoftw]

if it is correct that tinyint(1) would be synonymous with boolean, but not tinyint(1) unsigned which would be integer, then for me two solutions.

• boolean is "stored" as integers 0|1 in a tinyint col because there is no real boolean type
• tinyint signed or unsigned are integers not booleans

see: #19808 (comment)

Why here, because of this one word synonyms from the doc, it is pretended that there is a defined column data type Boolean, and that this Boolean type is tinyint(1), I honestly don't understand. MySQL has no defined storage type for boolean. If there would be one, it would surely be called boolean and stored as boolean and it would not be allowed to store an integer 42 in this col.

Just open a MySQL and try it yourself what you can write into a tiniyint field and what you get out of it.

If you still think it's a good idea to always cast the value from a tinyint(1) column to the php type boolean, go ahead.

This would be obviously a bug and will potentially break perfectly valid code, but hey, we would have a boolean or at least we could pretend that it is so.

I'm out.

The first thing i must clarify is that, i have not said that there is a boolean type in MYSQL, simply by not having a specific type a mapping is made, and this is the Framework specification, you can consult QueryBuilder in getColumnType, it simply refers me to the existing documentation, keep in mind that this also handles legacy versions of mysql, where in the previous documentation it said that tinyint(1) was synonymous with boolean, i'm not making it up.

yii2/framework/db/QueryBuilder.php

Line 1183 in 6804fbe

* - `boolean`: boolean type, will be converted into "tinyint(1)"

If you do the test, using the Framework, you will see that it only saves 0 and 1, when you use $this->boolean().

[schmunk42]

@terabytesoftw

If you do the test, using the Framework, you will see that it only saves 0 and 1, when you use $this->boolean().

How would the framework do that? Aren't you able to set $model->is_something = 5 and save that.

In a project I have columns defined in migration with $this->boolean() and my expectation was that I can easily work with true and false - but that is not the case, firstly gii generates an integer validator, which prevents me from setting true/false, secondly values from the db are also integersin the model.

[terabytesoftw]

@terabytesoftw

If you do the test, using the Framework, you will see that it only saves 0 and 1, when you use $this->boolean().

How would the framework do that? Aren't you able to set $model->is_something = 5 and save that.

In a project I have columns defined in migration with $this->boolean() and my expectation was that I can easily work with true and false - but that is not the case, firstly gii generates an integer validator, which prevents me from setting true/false, secondly values from the db are also integersin the model.

The framework works this way in this case, you can read the code and documentation, where it clearly says that tinyint(1) works with 0 for false, 1 for true, the framework does the typeCast, this is how it works for this case, in Yii3 to eliminate ambiguity we use bit(1), and that is why i asked here how the best way to solve it would be, since here applying bit(1) would be BC, but it eliminates the ambiguity.

[terabytesoftw]

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

"Display width specification for integer data types was deprecated in MySQL 8.0.17,
and now statements that include data type definitions in their output no longer show the display width for
integer types, with these exceptions:

The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN
columns; this exception enables them to continue to make that assumption."

[schmunk42]

@samdark @bizley IMHO, the changes in #20045 should be reverted, there's just too much what could break.

Ie. all models generated with gii which have a property for a tinyint(1) column have IntegerValidators, but the value would now be a boolean. And this is just one example.

[terabytesoftw]

Yes, i agree, this commit should be reverted.