title: MySQL UUID function produces the same value when used as a function parameter
body: <p>The <code>UUID()</code> function by itself produces a different value each time it is called, as I would expect it to do:</p>
  
  <pre><code>SELECT UUID() from INFORMATION_SCHEMA.TABLES LIMIT 3;
  
  3bb7d468-f9c5-11e9-8349-d05099466715
  3bb7d482-f9c5-11e9-8349-d05099466715
  3bb7d492-f9c5-11e9-8349-d05099466715
  </code></pre>
  
  <p>However, as soon as we use it within the <code>REPLACE()</code> function, it begins producing the same value:</p>
  
  <pre><code>SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.TABLES LIMIT 3;
  e0f2d47a-f9c5-11e9-8349-d05099466715
  e0f2d47a-f9c5-11e9-8349-d05099466715
  e0f2d47a-f9c5-11e9-8349-d05099466715
  </code></pre>
  
  <p>This 'breaks' Insert From Select statements like this where we expect each inserted row to have a unique value:</p>
  
  <pre><code>INSERT INTO MyTable (uid, tableName) -- uid is binary(16)
  SELECT UNHEX(REPLACE(UUID(),'-','')), TABLE_NAME from INFORMATION_SCHEMA.TABLES;
  </code></pre>
  
  <p><em>Note, I am using the information schema's list of tables for convenience.  It shouldn't matter, but for those that are curious, our PK's are UUIDs in binary(16) form.  I can't change that; please don't focus on that.</em></p>
  
  <p>The <code>UUID()</code> function is non-deterministic, while the <code>REPLACE()</code> function is deterministic.  I would have expected the non-deterministic characteristic of the <code>UUID()</code> function to result in the <code>REPLACE()</code> function behaving as if it had a different argument for each row, but it seems as though the DB engine is over optimizing by assuming the <code>UUID()</code> to be constant.</p>
  
  <p>I also tested this behavior with another non-deterministic function, <code>RAND()</code>, and in this case the <code>REPLACE()</code> function worked as we'd expect!</p>
  
  <pre><code>SELECT REPLACE(RAND(),' ',' ') from INFORMATION_SCHEMA.TABLES LIMIT 3;
  0.911571646026868
  0.626416072832808
  0.6977608461843439
  </code></pre>
  
  <p><strong>Questions</strong>:</p>
  
  <p>Is there a way to perform an "Insert From Select" and generate a unique UUID in binary 16 form per row in the select?</p>
  
  <p>Why is this happening?  Is this a bug?</p>
  
  <hr>
  
  <p><strong>Updates</strong></p>
  
  <p>I am using 5.7.27 locally:</p>
  
  <p><code>mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64)</code></p>
  
  <p>But this will end up deploying to an AWS RDS instance.  lol... The Terraform (scripted deploy) spins up an AWS RDS instance with engine version 5.7.16.</p>
  
  <p>Looking in the AWS console, I see support up to version 5.7.26 (in the 5.7 vein) and 8.0.16 (in the 8.0 vein).  I'll discuss upgrading the deployed engine version.  I'd love to change the PK column definitions to default the values as @Schwern has suggested.</p>
  
  <hr>
  
  <p><strong>Work Around</strong></p>
  
  <p>Until I can get others to agree to a version change, I'm moving forward by using a temporary table as intermediate storage for generated id values.</p>
  
  <pre><code>CREATE TEMPORARY TABLE GeneratedIds (
      generatedId varchar(36) NOT NULL,
      tableName text NOT NULL
  );
  INSERT INTO GeneratedIds (generatedId, tableName)
  SELECT UUID(), TABLE_NAME from INFORMATION_SCHEMA.TABLES;
  
  INSERT INTO MyTable (uid, tableName) -- uid is binary(16)
  SELECT UNHEX(REPLACE(generatedId,'-','')), tableName FROM GeneratedIds;
  
  DROP TABLE GeneratedIds;
  </code></pre>
  
  <p>This is not very elegant, but it does work.  In my case I am working within a sql migration file where I can string together this kind of sequence of sql in a cohesive manner.  I wouldn't recommend doing this in code; it smells.</p>
  
  <hr>
  
  <p><strong>Conclusion</strong></p>
  
  <p><strong>This does appear to be a bug in MySQL.</strong>  I did a quick search of their bug DB but I did not find a mention of it.  Regardless, the SQL statements above illustrate the defect, and @Schwern and I have shown that this bug has been fixed in version 5.7.27 (exactly) and version 8.0.16 (possibly all 8.<em>.</em>, only tested 8.0.16 and 8.0.18).</p>
  
  <p>Version 8.0.16 test:</p>
  
  <pre><code>Server version: 8.0.16 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql&gt; SELECT REPLACE(UUID(),'-','-') from INFORMATION_SCHEMA.TABLES LIMIT 3;
  +--------------------------------------+
  | REPLACE(UUID(),'-','-')              |
  +--------------------------------------+
  | 96f9205a-fdc6-11e9-87de-d05099466715 |
  | 96f920f9-fdc6-11e9-87de-d05099466715 |
  | 96f9213e-fdc6-11e9-87de-d05099466715 |
  +--------------------------------------+
  3 rows in set (0.00 sec)
  </code></pre>