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

• I'd like to know what is causing this, and if there is a workaround to the problem either with or without changing the data type of the column assuming that's the issue when it's returned as a variable rather than just a straight query result.

  • I'll be happy to disclose more technical specs of the environment if needed but I've included what I think it important for the question. Perhaps this is a version bug or there's something obvious that causes this that I cannot see easily so I'm hoping someone can help me with this or explain why this is or is not possible with MySQL.