title: MySQL Variable Returning Incorrect Value
body: <h2>The Issue</h2>
  
  <p>I have a stored proc in a DB server that's bringing back a value of <code>5064803</code> when that record does not exist and the value should be <code>5064800</code> as per the query that builds the value of the variable.</p>
  
  <p>I'm not sure if this is an issue with the value being of the <code>FLOAT</code> 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.</p>
  
  <p>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 <code>INT</code> with no decimal but I'd like to keep the data types the same as the correlated sensor just in case.</p>
  
  <p>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.</p>
  
  <hr>
  
  <h2>The SQL Data</h2>
  
  <p><strong>Create Table</strong></p>
  
  <pre><code>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$$  
  </code></pre>
  
  <p><strong>Insert Data</strong></p>
  
  <pre><code>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);
  </code></pre>
  
  <p><strong>The SQL Query Breakdown</strong> </p>
  
  <pre><code>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;
  </code></pre>
  
  <hr>
  
  <h2>Recreating the Issue</h2>
  
  <p>So based on <strong>The SQL Query Breakdown</strong> above, once all the data is in the table and then I run the queries within the <code>SELECT</code> queries alone within the <code>@NowNumber</code> and/or <code>@LastNumber</code> variables, I get the correct result of <code>5064800</code>. However, if I run the entire <code>SET</code> statements for both of those to have it set the query and then just do a <code>SELECT</code> of those variable, it brings back the wrong result of <code>5064803</code>.</p>
  
  <p>So for example if I run <code>SELECT Value FROM Number ORDER BY TimeStr DESC LIMIT 1</code> then the correct value is returned. If I run <code>SET @LastNumber = (SELECT Value FROM Number ORDER BY TimeStr DESC LIMIT 1);</code> and then run <code>SELECT @LastNumber;</code> I get the incorrect value returned.</p>
  
  <hr>
  
  <h2>Server System Specs</h2>
  
  <p>This particular <strong>MySQL Server</strong> is running the x86 version of <code>5.5.50</code> on <strong>Windows Server 2008</strong> with 144 GB of RAM for some quick specs. </p>
  
  <hr>
  
  <h2>Question</h2>
  
  <ul>
  <li><p>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. </p>
  
  <ul>
  <li>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. </li>
  </ul></li>
  </ul>