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
          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;
          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.

          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.