Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5654

P_S showing same digest for all select statements using views

Details

    Description

      The table performance_schema.events_statements_history showing the same digest value for the SELECTS on views. 

      mysql> create table percona.test_digest_table (cola int, colb int);
      Query OK, 0 rows affected (0.22 sec)mysql> create view percona.test_digest_view 
          -> as
          -> select cola, colb
          -> from percona.test_digest_table;
      Query OK, 0 rows affected (0.01 sec)mysql> select cola from percona.test_digest_view;
      Empty set (0.00 sec)mysql> select * from percona.test_digest_view where colb = 2;
      Empty set (0.00 sec)mysql> show create table percona.test_digest_view;
      +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      | View             | Create View                                                                                                                                                                                                     | character_set_client | collation_connection |
      +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      | test_digest_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_digest_view` AS select `test_digest_table`.`cola` AS `cola`,`test_digest_table`.`colb` AS `colb` from `test_digest_table` | latin1               | latin1_swedish_ci    |
      +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
      1 row in set (0.00 sec)mysql> select sql_text,digest,digest_text
          -> from performance_schema.events_statements_history 
          -> where sql_text like '%percona.test_digest_view%';
      +-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_text                                                                                  | digest                                                           | digest_text                                                                                                                                         |
      +-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | show create table percona.test_digest_view                                                | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
      | create view percona.test_digest_view 
      as
      select cola, colb
      from percona.test_digest_table | 3cd56fc1f7db95795f33109a503176924293c6fe118b626dffa933c684e8bc9b | CREATE VIEW `percona` . `test_digest_view` AS SELECT `cola` , `colb` FROM `percona` . `test_digest_table`                                           |
      | select cola from percona.test_digest_view                                                 | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
      | select * from percona.test_digest_view where colb = 2                                     | 0ae8e625e79b6ed0ef4f9bfe638092edfbaf40c094f32709ffd4abe5679830ce | SELECT `percona` . `test_digest_table` . `cola` AS `cola` , `percona` . `test_digest_table` . `colb` AS `colb` FROM `percona` . `test_digest_table` |
      +-------------------------------------------------------------------------------------------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      4 rows in set (0.13 sec)
      

      The same happens with CREATE VIEW ALGORITHM=TEMPTABLE|MERGE as well. This seems to happen in all version and the above was tested in 8.0.15 Percona server. 

      This is related with upstream bug - https://bugs.mysql.com/bug.php?id=89559

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              vinodh.krishnaswamy Vinodh Krishnaswamy
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Smart Checklist