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

INSERT INTO ... SELECT ERROR

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 10.2
    • Fix Version/s: N/A
    • Labels:
      None
    • Environment:
      Testing on some MariaDB Version and also MySQL

      Description

      If i have a SELECT query that returns 1 from 2 ROWS everything is OK. But when i add it afer a INSERT it reports a ERROR on the second ROW

      Sample CODE

      CREATE TABLE `table1` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `dt_int` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

      INSERT INTO `table1` (`id`, `dt_int`)
      VALUES
      (1, 20200202),
      (2, 20202020);

      CREATE TABLE `table2` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `dt` date DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

      – OK it returns only 1 correct ROW
      SELECT DATE(str_to_date( dt_int , '%Y%m%d'))
      FROM table1
      WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null;

      – reports ERROR – Incorrect datetime value: '20202020' for function str_to_date
      INSERT INTO table2(dt)
      SELECT DATE(str_to_date( dt_int , '%Y%m%d'))
      FROM table1
      WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null
      ;

      How can i fix this in the query

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              Bernd Buffen Bernd Buffen
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: