Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Insert float type into database table but fractional part loses #30435

Closed
liugj opened this issue Oct 27, 2019 · 1 comment
Closed

Insert float type into database table but fractional part loses #30435

liugj opened this issue Oct 27, 2019 · 1 comment

Comments

@liugj
Copy link

liugj commented Oct 27, 2019 •
edited
Loading

  • Laravel Version: 5.4+
  • PHP Version: 7.2+
  • Database Driver & Version: mysql

Description:

Insert float type into database table but fractional part loses

Steps To Reproduce:

CREATE TABLE order (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
cast decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '订单总金额',
PRIMARY KEY (id)
) ENGINE=InnoDB
DB::table('order')->insert(['cast' => 10.11]);

 select * from order where id  =1
+--------+----------+ 
| id     | cast     | 
+--------+----------+ 
| 1      | 10.00    | 
+--------+----------+
@liugj liugj mentioned this issue Oct 27, 2019
Closed
@driesvints
Copy link
Member

Hey there,

Can you first please try one of the support channels below? If you can actually identify this as a bug, feel free to report back and I'll gladly help you out and re-open this issue.

Thanks!

Chekote added a commit to Chekote/laravel-framework that referenced this issue Jan 10, 2020
@Chekote
Fix can't insert float into MySQL with PHP 7.3
95a8141 
## Problem
When inserting a float value into MySQL via the Illuminate\Database package when using at least PHP 7.3, the value is converted to an int.

The bug has been reported a few times before:

* laravel#30435 
* illuminate/database#246

## Cause
PR laravel#16069 introduced logic to the MySQLConnection that cast floats to ints to address a comparison problem with JSON columns, which was reported in issue laravel#16069. This does not seem to cause a problem with PHP 7.1 and below but causes the float to lose it's decimal places when using PHP 7.3. I have not tested with PHP 7.2.

## Bug Reproduction
Given we have the following MySQL table:

```
CREATE TABLE `zip_codes` (
  `zip_code` varchar(255) NOT NULL,
  `latitude` decimal(15,10) NOT NULL,
  `longitude` decimal(15,10) NOT NULL
)
```

When we execute the following using Artisan Tinker:

```php
DB::table('zip_codes')->delete();

// Using the same values
$lat = -0.2;
$lon = 0;
$sql = 'INSERT INTO zip_codes (latitude, longitude, zip_code) VALUES (:lat, :long, :zip)';

// We insert the first by using PDO directly
$values = [':lat' => $lat, ':long' => $lon, ':zip' => 1];
DB::connection()->getPdo()->prepare($sql)->execute($values);

// Then the second by using Eloquent
DB::table('zip_codes')->insert(['latitude' => $lat, 'longitude' => $lon, 'zip_code' => 2]);

// Then pull them both.
DB::table('zip_codes')->get();
```

Then we should see both records with -0.2 as the latitude. But only the first has the value, and the second row is zero.
@Chekote Chekote mentioned this issue Jan 10, 2020
Merged
Chekote added a commit to Chekote/laravel-framework that referenced this issue Jan 10, 2020
@Chekote
Fix can't insert float into MySQL with PHP 7.3
92737be 
When inserting a float value into MySQL via the Illuminate\Database package when using at least PHP 7.3, the value is converted to an int.

The bug has been reported a few times before:

* laravel#30435
* illuminate/database#246

PR laravel#16069 introduced logic to the MySQLConnection that cast floats to ints to address a comparison problem with JSON columns, which was reported in issue laravel#16069. This does not seem to cause a problem with PHP 7.1 and below but causes the float to lose it's decimal places when using PHP 7.3. I have not tested with PHP 7.2.

Given we have the following MySQL table:

```
CREATE TABLE `zip_codes` (
  `zip_code` varchar(255) NOT NULL,
  `latitude` decimal(15,10) NOT NULL,
  `longitude` decimal(15,10) NOT NULL
)
```

When we execute the following using Artisan Tinker:

```php
DB::table('zip_codes')->delete();

// Using the same values
$lat = -0.2;
$lon = 0;
$sql = 'INSERT INTO zip_codes (latitude, longitude, zip_code) VALUES (:lat, :long, :zip)';

// We insert the first by using PDO directly
$values = [':lat' => $lat, ':long' => $lon, ':zip' => 1];
DB::connection()->getPdo()->prepare($sql)->execute($values);

// Then the second by using Eloquent
DB::table('zip_codes')->insert(['latitude' => $lat, 'longitude' => $lon, 'zip_code' => 2]);

// Then pull them both.
DB::table('zip_codes')->get();
```

Then we should see both records with -0.2 as the latitude. But only the first has the value, and the second row is zero.
Chekote added a commit to Chekote/laravel-framework that referenced this issue Jan 10, 2020
@Chekote
Fix can't insert float into MySQL with PHP 7.3
f6530aa 
When inserting a float value into MySQL via the Illuminate\Database package when using at least PHP 7.3, the value is converted to an int.

The bug has been reported a few times before:

* laravel#30435
* illuminate/database#246

PR laravel#16069 introduced logic to the MySQLConnection that cast floats to ints to address a comparison problem with JSON columns, which was reported in issue laravel#16063. This does not seem to cause a problem with PHP 7.1 and below but causes the float to lose it's decimal places when using PHP 7.3. I have not tested with PHP 7.2.

Given we have the following MySQL table:

```
CREATE TABLE `zip_codes` (
  `zip_code` varchar(255) NOT NULL,
  `latitude` decimal(15,10) NOT NULL,
  `longitude` decimal(15,10) NOT NULL
)
```

When we execute the following using Artisan Tinker:

```php
DB::table('zip_codes')->delete();

// Using the same values
$lat = -0.2;
$lon = 0;
$sql = 'INSERT INTO zip_codes (latitude, longitude, zip_code) VALUES (:lat, :long, :zip)';

// We insert the first by using PDO directly
$values = [':lat' => $lat, ':long' => $lon, ':zip' => 1];
DB::connection()->getPdo()->prepare($sql)->execute($values);

// Then the second by using Eloquent
DB::table('zip_codes')->insert(['latitude' => $lat, 'longitude' => $lon, 'zip_code' => 2]);

// Then pull them both.
DB::table('zip_codes')->get();
```

Then we should see both records with -0.2 as the latitude. But only the first has the value, and the second row is zero.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[6.x] Fix inserting float type into database liugj/framework
2 participants
@driesvints @liugj