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

Wrong warning with MAX(datetime_field) OVER (...)

Details

    Description

      This script:

      create or replace table t1(dt datetime);
      		 
      insert into t1 values ('2017-05-17');
      		 
      select max(dt)  over (order by dt rows between 1 following and 1 following)  from t1;
      		 
      show warnings;

      returns a correct result:

      +----------------------------------------------------------------------+
      		 
      | max(dt)  over (order by dt rows between 1 following and 1 following) |
      		 
      +----------------------------------------------------------------------+
      		 
      | NULL                                                                 |
      		 
      +----------------------------------------------------------------------+

      but causes a redundant warning in bb-10.2-ext and 10.3:

      +---------+------+------------------------------+
      		 
      | Level   | Code | Message                      |
      		 
      +---------+------+------------------------------+
      		 
      | Warning | 1292 | Incorrect datetime value: '' |
      		 
      +---------+------+------------------------------+

      The warning looks wrong. There should not be any warnings.

      The problem is not repeatable in 10.2.

      The problem happens because Item_window_func::save_in_field() works differently in bb-10.2-ext comparing to 10.2:

      • 10.2 goes through val_str()
      • bb-10.2-ext goes through get_date(), due to Type_handler related changes

      After a discussion with Vicentiu, it was decided to fix Item_window_func::val_xxx() to return NULL (instead of an "empty" value, such as 0 for numbers and '' for strings) when force_return_blank is set.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          causes a warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}


          The warning looks wrong.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler changes

          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          causes a warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}


          The warning looks wrong. There should not be warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler changes

          bar Alexander Barkov made changes -
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          causes a warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}


          The warning looks wrong. There should not be warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler changes

          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler changes

          bar Alexander Barkov made changes -
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler changes

          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes
          bar Alexander Barkov made changes -
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because Item_window_func::save_in_field works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes
          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because {{Item_window_func::save_in_field()}} works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes
          bar Alexander Barkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Assignee Alexander Barkov [ bar ] Vicentiu Ciorbaru [ cvicentiu ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          cvicentiu Vicențiu Ciorbaru added a comment -

          Ok to push

          cvicentiu Vicențiu Ciorbaru added a comment - Ok to push
          cvicentiu Vicențiu Ciorbaru made changes -
          Status In Review [ 10002 ] Stalled [ 10000 ]
          cvicentiu Vicențiu Ciorbaru made changes -
          Assignee Vicentiu Ciorbaru [ cvicentiu ] Alexander Barkov [ bar ]
          bar Alexander Barkov made changes -
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because {{Item_window_func::save_in_field()}} works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes
          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because {{Item_window_func::save_in_field()}} works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes

          After a discussion with Vicentiu, it was decided to fix Item_window_func::val_xxx() to return NULL (instead of an "empty" value, such as 0 for numbers and '' for strings) when force_return_blank is set.
          bar Alexander Barkov made changes -
          Description This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because {{Item_window_func::save_in_field()}} works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes

          After a discussion with Vicentiu, it was decided to fix Item_window_func::val_xxx() to return NULL (instead of an "empty" value, such as 0 for numbers and '' for strings) when force_return_blank is set.
          		This script:
          {code:sql}
          create or replace table t1(dt datetime);
          insert into t1 values ('2017-05-17');
          select max(dt) over (order by dt rows between 1 following and 1 following) from t1;
          show warnings;
          {code}
          returns a correct result:
          {noformat}
          +----------------------------------------------------------------------+
          | max(dt) over (order by dt rows between 1 following and 1 following) |
          +----------------------------------------------------------------------+
          | NULL |
          +----------------------------------------------------------------------+
          {noformat}
          but causes a redundant warning in bb-10.2-ext and 10.3:
          {noformat}
          +---------+------+------------------------------+
          | Level | Code | Message |
          +---------+------+------------------------------+
          | Warning | 1292 | Incorrect datetime value: '' |
          +---------+------+------------------------------+
          {noformat}

          The warning looks wrong. There should not be any warnings.

          The problem is not repeatable in 10.2.

          The problem happens because {{Item_window_func::save_in_field()}} works differently in bb-10.2-ext comparing to 10.2:
          - 10.2 goes through val_str()
          - bb-10.2-ext goes through get_date(), due to Type_handler related changes

          After a discussion with Vicentiu, it was decided to fix {{Item_window_func::val_xxx()}} to return {{NULL}} (instead of an "empty" value, such as 0 for numbers and '' for strings) when force_return_blank is set.
          bar Alexander Barkov added a comment -

          Pushed to bb-10.2-ext.

          bar Alexander Barkov added a comment - Pushed to bb-10.2-ext.
          bar Alexander Barkov made changes -
          issue.field.resolutiondate 2017-07-04 10:17:09.0 2017-07-04 10:17:09.228
          bar Alexander Barkov made changes -
          Fix Version/s 10.3.1 [ 22532 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 81542 ] MariaDB v4 [ 152433 ]

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.