title: Understanding recursive cte with floats
body: <p>I would like to create histograms of some data, but the tool I'm using doesn't have a histogram tool but just a bar chart tool. Therefor I need to fill the gaps and create the bins myself. I'm trying to do this with a recursive cte and a left join (quite new to SQL, so apologies if terminology not on point). I seem to be able to do what I want with integers, but get problems when I move to real numbers.</p>
  <p>I have four examples below explained here.</p>
  <ol>
  <li><p>Generating a table with with n from 0 to 500 with step size 1 no problem, basically what I want except a table that goes from 0 to 50 with a step size of 0.1.</p>
  </li>
  <li><p>Another table with step size 0.1, from 0 to 9. Works great but not the range I want.</p>
  </li>
  <li><p>As soon as I use n&gt;=10 it doesn't work anymore.</p>
  </li>
  <li><p>In the SELECT statement I use 0, instead of 0.0. Doesn't work.</p>
  </li>
  </ol>
  <p>Questions:</p>
  <ol>
  <li>How do I generate a table where n has rows from 0 to 50 with step size 0.1?</li>
  <li>Why do I have to use as many decimals in SELECT x as I want in the steps? For step size of 0.1 I need to enter 0.0 and for 0.01 I need to enter 0.00.</li>
  </ol>
  <p>Notes:</p>
  <ul>
  <li><p>I know a histogram shouldn't really have 501 bins, but the data has
  several interesting parts so it's used to &quot;zoom in&quot; to partical areas.</p>
  </li>
  <li><p>I've seen that recursive has a limit of 1 000 rows, but this is below
  that and thus don't understand the errors.</p>
  </li>
  </ul>
  <p>1.Works (returns 501 rows):</p>
  <pre><code>WITH RECURSIVE cte1 (n) AS
  (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM cte1 WHERE n &lt; 500
  )
  SELECT * FROM cte1;
  </code></pre>
  <p>2.Works (returns 91 rows:</p>
  <pre><code>WITH RECURSIVE cte1 (n) AS
  (
    SELECT 0.0
    UNION ALL
    SELECT n + 0.1 FROM cte1 WHERE n &lt; 9
  )
  SELECT * FROM cte1;
  </code></pre>
  <p>3.Does not work (reponse <code>Error Code: 1264. Out of range value for column 'n' at row 1</code>):</p>
  <pre><code>WITH RECURSIVE cte1 (n) AS
  (
    SELECT 0.0
    UNION ALL
    SELECT n + 0.1 FROM cte1 WHERE n &lt; 10
  )
  SELECT * FROM cte1;
  </code></pre>
  <p>4.Does not work (response <code>Error Code: 3636. Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.</code>):</p>
  <pre><code>WITH RECURSIVE cte1 (n) AS
  (
    SELECT 0
    UNION ALL
    SELECT n + 0.1 FROM cte1 WHERE n &lt; 9
  )
  SELECT * FROM cte1;
  </code></pre>