Error Code: 1292. Truncated incorrect DOUBLE value: '' when updating field value-CodePudding

The following query gives me the expected result as in a single col of decimals and blanks:

SELECT IFNULL(CAST((volume/SharesIssued)*100 AS decimal(2,2)) , '') FROM stock_watch.eod

RESULT

0.33
0.03
0.00
0.02
0.55
0.39
0.00
0.05
0.09
0.06
0.08
0.00
0.00

however when I try to add it to to a field like this

UPDATE stock_watch.eod SET VolumePercentage = IFNULL(CAST((volume/SharesIssued)*100 AS decimal(2,2)) , '')

I get the following error: Error Code: 1292. Truncated incorrect DOUBLE value: ''

All fields are varchar(45)

The table looks like this

CREATE TABLE eod (
  id int(11) NOT NULL AUTO_INCREMENT,
  code varchar(6) CHARACTER SET utf8mb4 DEFAULT NULL,
  date date DEFAULT NULL,
  open varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  high varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  low varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  close varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  volume varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  SharesIssued varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  VolumePercentage varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  checksum varchar(45) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fb_groupbyorder_date_INDEX (date)
) ENGINE=InnoDB AUTO_INCREMENT=270331 DEFAULT CHARSET=utf8;

SAMPLE DATA: dbfiddle

CodePudding user response：

In the end, I changed sql_mode='' in the my.cnf file. Casting was also creating issues by giving wrong results. Issue was resolved by not casting, which works with sql-mode=''. I accept this is not actually a solution to the error message, but rather a way to get the update to happen and reduce error to warning. However, in my experience, strict mode just tends to make mysql unusable.

CodePudding user response：

One of the SharesIssued field is empty and your division of volume/SharesIssued there become 0/' '. This is the cause of the error.

If you update the data like so:

UPDATE eod SET SharesIssued=0 WHERE SharesIssued='';

And run the UPDATE query again, you'll receive Error 1365: Division by 0 instead but that's now caused by the SharesIssued='' being updated to 0. Instead, since your sql_mode is on with STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION by default, this will happen. One quick solution to this is by turning off sql_mode on the session:

SET sql_mode = "";

UPDATE eod 
SET VolumePercentage = CAST(IFNULL(volume/IFNULL(SharesIssued,0),0)*100 AS DECIMAL(2,2));

Depending on what tool you use and whether you set it to show messages (error, warning etc.), you'll probably get these warning messages:

Warning 1292: Truncated incorrect DOUBLE value: ''
Warning 1264: Out of range value for column 'CAST((volume/SharesIssued)*100 AS decimal(2,2))' at row 1

But the column VolumePercentage will get updated anyhow. Note that sql_mode will return to default setting when the MySQL service is restarted.

Demo fiddle