Convert int to string when execute insert query if the type of mysql column is 'bigint unsigned'

[bittenApple]

What steps will reproduce the problem?

1. Create a MySQL table and mapped ActiveRecord.

create table foo(id bigint unsigned);

Save a model

    $f = new Foo;
    $f->id = 1;
    $f->save();

What is the expected result?

The query should be

INSERT INTO `foo` (`id`) VALUES (1)

What do you get instead?

The executed query is

INSERT INTO `foo` (`id`) VALUES ('1')

The range of MySQL unsigned bigint is larger than PHP_INT_SIZE, but it's not reasonable to convert int attribute manually set to string when execute sql, which may slow the query performance.

Additional info

Q	A
Yii version	2.0.12
PHP version	7.1.3
Operating system	CentOS 7
MySQL	5.7.16

[fcaldarelli]

Have you checked that it is not related to PDO ?

[yyxx9988]

This is not a bug.

The maximum int value of php depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647. So for example on such a system, intval('1000000000000') will return 2147483647.

see http://php.net/manual/en/language.types.integer.php for more details

[bittenApple]

This is not a bug.

The maximum int value of php depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647. So for example on such a system, intval('1000000000000') will return 2147483647.

see http://php.net/manual/en/language.types.integer.php for more details

I think it's the programmer's responsibility to take care of the risk of int overflow.
If the framework user set the int value of an attribute explicitly, I don't think it's appropriate to cast int to string implicitly by framework

[cebe]

The executed query is

how did you verify the type of the value bound on query execution time? These are bound as parameter values and are converted inside of PDO, Yii has no logic to convert int to string by default in that case. I can not reproduce the issue. Please provide the CREATE TABLE statement of your table.

[bittenApple]

how did you verify the type of the value bound on query execution time

I find some slow query in my production MySQL slow log which the type of value is not as expected. Maybe the performance of type casting is not the key reason for mysql, but I still think it's better not to cast the type of value if user set it explicitly.

These are bound as parameter values and are converted inside of PDO, Yii has no logic to convert int to string by default in that case

I think the following code do the casting.

yii2/framework/db/mysql/QueryBuilder.php

Line 261 in 1501c65

$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;

Please provide the CREATE TABLE statement of your table.

create table foo(id bigint unsigned);

Insert any table which has bigint unsigned type would case the casting.

[cebe]

yeah, the reason for this is that bigint unsigned is bigger than integer in PHP so the string representation is safe to use, while integer may overflow.

[cebe]

I find some slow query in my production MySQL slow log which the type of value is not as expected.

can you provide more information about this? What exactly is logged in MySQL?

[bittenApple]

can you provide more information about this? What exactly is logged in MySQL?

The table has a union index with two unsigned bigint columns and has ten million of rows. There are some inserted sql logged at MySQL slow query log. Although I think it's not a heavy work doing type casting for mysql, I still migrated the unsigned bigint to signed.

yeah, the reason for this is that bigint unsigned is bigger than integer in PHP so the string representation is safe to use, while integer may overflow.

The overflow only happened when string casted to int, but if programmer set the type as int explicitly, my point is that maybe it's not necessary to cast int back to string.

[cebe]

The overflow only happened when string casted to int, but if programmer set the type as int explicitly, my point is that maybe it's not necessary to cast int back to string.

it is probably not necessary, but we need to find the condition to find that case.

[darkdef]

I'm check query for this code

        $model = new Order();
        $model->customer_id = 1000000000000;
        $model->save(false);

Result query is
``sql
INSERT INTO order (`customer_id`, `created_at`) VALUES (1000000000000, 1612863588)

[darkdef]

for the

$model->customer_id = 10000000000000000000000;

sql

INSERT INTO `order` (`customer_id`, `created_at`) VALUES (1864712049423024128, 1612863706)

[bizley]

PHP_INT_MAX check required?

[darkdef]

PHP_INT_MAX check required?

You may add rules to model. But generated sql query is correct.

[rob006]

@darkdef What is the type of customer_id column? Try convert it to unsigned bigint. The inserted value is irrelevant here, incorrect typecast comes from column schema, you will get the same result even for $model->customer_id = 1 (as explained in first comment).

[darkdef]

You right. Reproduced the problem

[egorrishe]

phpunit --filter testInsertInteger tests/framework/db/mysql/QueryBuilderTest.php
Failed on PHP 5.6:

Failed asserting that two strings are equal.
--- Expected
+++ Actual
@@ @@
-'INSERT INTO customer (profile_id) VALUES (1000000000000)'
+'INSERT INTO customer (profile_id) VALUES (2147483647)'

The value 2147483647 is correct - because type of customer.profile_id is INT (mysql.sql). And it's maximum in MySql is 2147483647.
So behaviour is ok. Test should be changed.

[bizley]

Closed via 17742cb