MDEV-24363 (followup refactor) avoid listing mysql.user

view definition in tests as it wastes lots of time when they fail
for minor fixes like adding/changing a new column in the view, also
it's less code

1 files changed, 2 insertions, 50 deletions

diff --git a/mysql-test/main/upgrade_MDEV-23102-2.test b/mysql-test/main/upgrade_MDEV-23102-2.test
index 1491f7d7bd2..27ee75ea58c 100644
--- a/mysql-test/main/upgrade_MDEV-23102-2.test
+++ b/mysql-test/main/upgrade_MDEV-23102-2.test
@@ -23,57 +23,9 @@ use mysql;
 create user superuser@localhost;
 grant all privileges on mysql.* to superuser@localhost;
 
-drop view user;
-
-CREATE DEFINER='superuser'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT
-  Host,
-  User,
-  IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
-  IF(JSON_VALUE(Priv, '$.access') &         1, 'Y', 'N') AS Select_priv,
-  IF(JSON_VALUE(Priv, '$.access') &         2, 'Y', 'N') AS Insert_priv,
-  IF(JSON_VALUE(Priv, '$.access') &         4, 'Y', 'N') AS Update_priv,
-  IF(JSON_VALUE(Priv, '$.access') &         8, 'Y', 'N') AS Delete_priv,
-  IF(JSON_VALUE(Priv, '$.access') &        16, 'Y', 'N') AS Create_priv,
-  IF(JSON_VALUE(Priv, '$.access') &        32, 'Y', 'N') AS Drop_priv,
-  IF(JSON_VALUE(Priv, '$.access') &        64, 'Y', 'N') AS Reload_priv,
-  IF(JSON_VALUE(Priv, '$.access') &       128, 'Y', 'N') AS Shutdown_priv,
-  IF(JSON_VALUE(Priv, '$.access') &       256, 'Y', 'N') AS Process_priv,
-  IF(JSON_VALUE(Priv, '$.access') &       512, 'Y', 'N') AS File_priv,
-  IF(JSON_VALUE(Priv, '$.access') &      1024, 'Y', 'N') AS Grant_priv,
-  IF(JSON_VALUE(Priv, '$.access') &      2048, 'Y', 'N') AS References_priv,
-  IF(JSON_VALUE(Priv, '$.access') &      4096, 'Y', 'N') AS Index_priv,
-  IF(JSON_VALUE(Priv, '$.access') &      8192, 'Y', 'N') AS Alter_priv,
-  IF(JSON_VALUE(Priv, '$.access') &     16384, 'Y', 'N') AS Show_db_priv,
-  IF(JSON_VALUE(Priv, '$.access') &     32768, 'Y', 'N') AS Super_priv,
-  IF(JSON_VALUE(Priv, '$.access') &     65536, 'Y', 'N') AS Create_tmp_table_priv,
-  IF(JSON_VALUE(Priv, '$.access') &    131072, 'Y', 'N') AS Lock_tables_priv,
-  IF(JSON_VALUE(Priv, '$.access') &    262144, 'Y', 'N') AS Execute_priv,
-  IF(JSON_VALUE(Priv, '$.access') &    524288, 'Y', 'N') AS Repl_slave_priv,
-  IF(JSON_VALUE(Priv, '$.access') &   1048576, 'Y', 'N') AS Repl_client_priv,
-  IF(JSON_VALUE(Priv, '$.access') &   2097152, 'Y', 'N') AS Create_view_priv,
-  IF(JSON_VALUE(Priv, '$.access') &   4194304, 'Y', 'N') AS Show_view_priv,
-  IF(JSON_VALUE(Priv, '$.access') &   8388608, 'Y', 'N') AS Create_routine_priv,
-  IF(JSON_VALUE(Priv, '$.access') &  16777216, 'Y', 'N') AS Alter_routine_priv,
-  IF(JSON_VALUE(Priv, '$.access') &  33554432, 'Y', 'N') AS Create_user_priv,
-  IF(JSON_VALUE(Priv, '$.access') &  67108864, 'Y', 'N') AS Event_priv,
-  IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
-  IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv,
-  IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
-  ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type,
-  IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
-  IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
-  IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
-  CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions,
-  CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
-  CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections,
-  CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections,
-  IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
-  IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string,
-  ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
-  IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
-  CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time
-  FROM global_priv;
+let $viewdef = `select view_definition from information_schema.views where table_name='user' and table_schema='mysql'`;
 
+--eval create or replace definer=superuser@localhost sql security definer view user as $viewdef;
 
 DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn;
 DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn;