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

CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition

    XMLWordPrintable

    Details

    • Sprint:
      10.1.13

      Description

      Test case

      CREATE TABLE B ( i INT );
      INSERT INTO B VALUES (1),(2);
       
      CREATE VIEW v1 AS 
      SELECT 3 AS three, COUNT(*) FROM B GROUP BY three;
       
      SELECT * FROM v1;
      

      Result

      MariaDB [test]> SELECT * FROM v1;
      ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      

      Here is the reason (note the constant in GROUP BY):

      MariaDB [test]> SHOW CREATE VIEW v1 \G
      *************************** 1. row ***************************
                      View: v1
               Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 AS `three`,count(0) AS `COUNT(*)` from `B` group by 3
      character_set_client: utf8
      collation_connection: utf8_general_ci
      

      Reproducible on current 5.5.48, 10.0.24, 10.1.12.
      Also reproducible on MySQL 5.5, but fixed in MySQL 5.6:

      MySQL [test]> SELECT * FROM v1;
      +-------+----------+
      | three | COUNT(*) |
      +-------+----------+
      |     3 |        2 |
      +-------+----------+
      1 row in set (0.00 sec)
       
      MySQL [test]> SHOW CREATE VIEW v1 \G
      *************************** 1. row ***************************
                      View: v1
               Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 AS `three`,count(0) AS `COUNT(*)` from `B` group by `three`
      character_set_client: utf8
      collation_connection: utf8_general_ci
      1 row in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration