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

view with ifnull around window function error

    XMLWordPrintable

Details

    Description

      A view that has an ifnull around a window function results in error 1356 on querying. A view without the ifnull works and the select with ifnull works.

      Test case:

      create table t1(dt datetime);
      insert into t1 values ('2017-05-17'), ('2017-05-18');
      create view v1 as select dt, lead(dt) over (order by dt) from t1;
      create view v2 as select dt, ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') from t1;
       
      select * from v1;
      +---------------------+-----------------------------+
      | dt                  | lead(dt) over (order by dt) |
      +---------------------+-----------------------------+
      | 2017-05-17 00:00:00 | 2017-05-18 00:00:00         |
      | 2017-05-18 00:00:00 | NULL                        |
      +---------------------+-----------------------------+
      2 rows in set (0.00 sec)
       
      select * from v2;
      ERROR 1356 (HY000): View 'genesis.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
       
      select dt, ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') from t1;
      +---------------------+------------------------------------------------------------+
      | dt                  | ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') |
      +---------------------+------------------------------------------------------------+
      | 2017-05-17 00:00:00 | 2017-05-18 00:00:00                                        |
      | 2017-05-18 00:00:00 | 9999-12-31 12:00:00                                        |
      +---------------------+------------------------------------------------------------+
      2 rows in set (0.00 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              dthompson David Thompson (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.