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

Datetime value dropped in "INSERT ... SELECT ... ON DUPLICATE KEY"

Details

    • 5.5.47-1

    Description

      INSERT INTO ...
      		 
       SELECT ...
      		 
        GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0)
      		 
       ) AS `changed_at`
      		 
       ON DUPLICATE KEY UPDATE ...
      		 
       `changed_at` = VALUES(`changed_at`)

      In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'

      Attachments

        1. test.query.sql
          2 kB
        2. test.sql.gz
          3.14 MB
        3. test.variables.txt
          61 kB

        Activity

          Ascending order - Click to sort in descending order
          raynix Raymond Xu created issue -
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Description INSERT INTO ...
           SELECT ...
            GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0)
           ) AS `changed_at`
           ON DUPLICATE KEY UPDATE ...
           `changed_at` = VALUES(`changed_at`)

          In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'           		{code:sql}
          INSERT INTO ...
           SELECT ...
            GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0)
           ) AS `changed_at`
           ON DUPLICATE KEY UPDATE ...
           `changed_at` = VALUES(`changed_at`)
          {code}
          In MySQL the changed_at will be the expected value from the greatest function, but in MariaDB it will be '0000-00-00 00:00:00'
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 5.5 [ 15800 ]
          serg Sergei Golubchik made changes -
          Priority Minor [ 4 ] Major [ 3 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 5.5 [ 15800 ]
          elenst Elena Stepanova made changes -
          Labels need_feedback
          raynix Raymond Xu made changes -
          Attachment test.sql.gz [ 39206 ]
          raynix Raymond Xu made changes -
          Attachment test.variables.txt [ 39207 ]
          raynix Raymond Xu made changes -
          Attachment test.query.sql [ 39208 ]
          elenst Elena Stepanova made changes -
          Labels need_feedback
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 5.5 [ 15800 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 5.5 [ 15800 ]
          Assignee Alexander Barkov [ bar ]
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          bar Alexander Barkov made changes -
          Rank Ranked higher
          ratzpo Rasmus Johansson (Inactive) made changes -
          Sprint 5.5.47-1 [ 22 ]
          bar Alexander Barkov made changes -
          Component/s Character Sets [ 10801 ]
          Fix Version/s 5.5.47 [ 20300 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 71055 ] MariaDB v4 [ 149481 ]

          People

            bar Alexander Barkov
            raynix Raymond Xu
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.