CODEWITHSUNDEEP
mysql
Bitcoin Murderous Maniac
Bitcoin Murderous Maniac

Reputation: 1478

MySQL Variable Returning Incorrect Value

The Issue

I have a stored proc in a DB server that's bringing back a value of 5064803 when that record does not exist and the value should be 5064800 as per the query that builds the value of the variable.

I'm not sure if this is an issue with the value being of the FLOAT data type and the value in the record of the table ending in a double-zero or what but I cannot figure it out easily.

The table data types match those from the sensors that are set but this particular value from this sensor never actually gets set to a data type and it's usually always either a 1-8 digit INT with no decimal but I'd like to keep the data types the same as the correlated sensor just in case.

I've broke down the proc and I'm able to recreate the problem easily so I will post the detail below for those that may be able to help me figure out the issue and any workaround, etc.

The SQL Data

Create Table

delimiter $$

CREATE TABLE `number` (
  `TimeInt` varchar(10) NOT NULL,
  `TimeStr` datetime NOT NULL,
  `IsInitValue` int(11) NOT NULL,
  `Value` float NOT NULL,
  `IQuality` int(11) NOT NULL,
  UNIQUE KEY `uk_Times` (`TimeInt`,`TimeStr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Insert Data

INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502618950','2017-08-13 10:09:10',1,5064800,0);
INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502618796','2017-08-13 10:06:36',0,5064800,3);
INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502617167','2017-08-13 09:39:27',1,5063310,0);
INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502613355','2017-08-13 08:35:55',0,5063310,3);
INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502612814','2017-08-13 08:26:54',1,0,0);
INSERT INTO `Number` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1502609015','2017-08-13 07:23:35',0,0,3);

The SQL Query Breakdown 

SET @bStartTime = '2017-08-13 09:24:16';
SET @bEndTime = '2017-08-13 10:06:31';
SET @LastNumber = (SELECT Value FROM Number ORDER BY TimeStr DESC LIMIT 1); 
SET @NowNumber  = (SELECT Value FROM Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1);

SELECT @NowNumber;
SELECT @LastNumber;

Recreating the Issue

So based on The SQL Query Breakdown above, once all the data is in the table and then I run the queries within the SELECT queries alone within the @NowNumber and/or @LastNumber variables, I get the correct result of 5064800. However, if I run the entire SET statements for both of those to have it set the query and then just do a SELECT of those variable, it brings back the wrong result of 5064803.

So for example if I run SELECT Value FROM Number ORDER BY TimeStr DESC LIMIT 1 then the correct value is returned. If I run SET @LastNumber = (SELECT Value FROM Number ORDER BY TimeStr DESC LIMIT 1); and then run SELECT @LastNumber; I get the incorrect value returned.

Server System Specs

This particular MySQL Server is running the x86 version of 5.5.50 on Windows Server 2008 with 144 GB of RAM for some quick specs.

Question

Upvotes: 0

Views: 275

Answers (1)

LAS
LAS

Reputation: 819

Sorry, declares can only be used in stored procedures in MySQL. I found this article which may help. It explains how MySQL rounds when storing digits and recommends using doubles. Try changing your floats to doubles.

MySql FLOAT datatype and problems with more then 7 digit scale

Upvotes: 1

Related Questions