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

FIRST_V throw SQL Fehler (1292): Incorrect datetime value: ''

Details

    Description

      To reproduce:

      CREATE TABLE IF NOT EXISTS `fv_test` (
      		 
        `SOME_DATE` datetime NOT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT INTO `aa`.`fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');


      create table fv_result
      		 
      SELECT 
      FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
      		 
      FROM fv_test;


      SQL Fehler (1292): Incorrect datetime value: '' for column 'somedate'

      The select (without create table) throw only a warning.

      It will caused by FIRST_VALUE, the error persists if the datatype in the over clause will be changed to non-datetime, but vanished if the datatype/column in the first_value will be changed to non-datetime.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Richard Richard Stracke created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          Thanks for the report, reproducible as described. Same test case as above:

          CREATE TABLE IF NOT EXISTS `fv_test` (
          		 
            `SOME_DATE` datetime NOT NULL
          		 
          );
          		 
           
          INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');
          		 
           
          		 
          create table fv_result
          		 
          SELECT 
          FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
          		 
          FROM fv_test;
          		 
           
          		 
          drop table fv_test, fv_result;

          elenst Elena Stepanova added a comment - Thanks for the report, reproducible as described. Same test case as above: CREATE TABLE IF NOT EXISTS `fv_test` ( `SOME_DATE` datetime NOT NULL ); INSERT INTO `fv_test` (`SOME_DATE`) VALUES ( '2017-07-20 12:47:56' );   create table fv_result SELECT FIRST_VALUE(SOME_DATE) OVER( ORDER BY SOME_DATE DESC ) AS somedate FROM fv_test;   drop table fv_test, fv_result;
          elenst Elena Stepanova made changes -
          Component/s Optimizer - Window functions [ 13502 ]
          Fix Version/s 10.2 [ 14601 ]
          Affects Version/s 10.2 [ 14601 ]
          Assignee Vicentiu Ciorbaru [ cvicentiu ]
          cvicentiu Vicențiu Ciorbaru added a comment -

          The root cause is the same as the one for MDEV-13240. Backporting the patch to 10.2 fixes the issue.

          cvicentiu Vicențiu Ciorbaru added a comment - The root cause is the same as the one for MDEV-13240 . Backporting the patch to 10.2 fixes the issue.
          cvicentiu Vicențiu Ciorbaru made changes -
          Fix Version/s 10.2.9 [ 22611 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 81723 ] MariaDB v4 [ 152511 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 121077

          People

            cvicentiu Vicențiu Ciorbaru
            Richard Richard Stracke
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.