Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12613

UNION creates excessive integer column types for functions

    Details

      Description

      CREATE OR REPLACE TABLE t1 AS SELECT 1=1;
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT 1=1 UNION SELECT 1=1;
      SHOW CREATE TABLE t1;
      

      Notice:

      • The first CREATE statement made a column of the int(1) type.
      • The second CREATE statement with UNION made a column the bigint(11) type. Looks excessive. An int column would be enough.

      More examples:

      CREATE OR REPLACE TABLE t1 AS SELECT strcmp('a','b');
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT strcmp('a','b') UNION SELECT strcmp('c','d'); 
      SHOW CREATE TABLE t1;
      

      CREATE OR REPLACE TABLE t1 AS SELECT 1+1;
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT 1+1 UNION SELECT 1+1;
      SHOW CREATE TABLE t1;
      

      CREATE OR REPLACE TABLE t1 AS SELECT EXISTS (SELECT 1);
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT EXISTS (SELECT 1) UNION SELECT EXISTS (SELECT 1);
      SHOW CREATE TABLE t1;
      

      CREATE OR REPLACE TABLE t1 AS SELECT MONTH(NOW());
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT MONTH(NOW()) UNION SELECT MONTH(NOW());
      SHOW CREATE TABLE t1;
      

      CREATE OR REPLACE TABLE t1 AS SELECT WEEKDAY(NOW());
      SHOW CREATE TABLE t1;
      CREATE OR REPLACE TABLE t1 AS SELECT WEEKDAY(NOW()) UNION SELECT WEEKDAY(NOW());
      SHOW CREATE TABLE t1;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: