HIVE-19166: TestMiniLlapLocalCliDriver sysdb failure (Daniel Dai, reviewed by Vaibhav Gumashta)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/6a3d42b3 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/6a3d42b3 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/6a3d42b3 Branch: refs/heads/branch-3 Commit: 6a3d42b3b9d032b771aa0ebcb9f0cb1b4f1af309 Parents: c0a9d3b Author: Daniel Dai <[email protected]> Authored: Thu Sep 20 14:32:06 2018 -0700 Committer: Daniel Dai <[email protected]> Committed: Thu Sep 20 14:32:36 2018 -0700 ---------------------------------------------------------------------- .../TestInformationSchemaWithPrivilege.java | 8 +- .../hadoop/hive/cli/control/CliConfigs.java | 1 - .../upgrade/hive/hive-schema-3.1.0.hive.sql | 78 +- ql/src/test/queries/clientpositive/sysdb.q | 12 +- .../clientpositive/llap/resourceplan.q.out | 156 +-- .../llap/strict_managed_tables_sysdb.q.out | 156 +-- .../results/clientpositive/llap/sysdb.q.out | 1250 +++++++++++++----- 7 files changed, 1075 insertions(+), 586 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/itests/hive-unit/src/test/java/org/apache/hive/service/server/TestInformationSchemaWithPrivilege.java ---------------------------------------------------------------------- diff --git a/itests/hive-unit/src/test/java/org/apache/hive/service/server/TestInformationSchemaWithPrivilege.java b/itests/hive-unit/src/test/java/org/apache/hive/service/server/TestInformationSchemaWithPrivilege.java index 610e646..18b0b13 100644 --- a/itests/hive-unit/src/test/java/org/apache/hive/service/server/TestInformationSchemaWithPrivilege.java +++ b/itests/hive-unit/src/test/java/org/apache/hive/service/server/TestInformationSchemaWithPrivilege.java @@ -581,20 +581,20 @@ public class TestInformationSchemaWithPrivilege { opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.SCHEMATA", confOverlay); rowSet = serviceClient.fetchResults(opHandle); - Assert.assertTrue(rowSet.numRows() > 2); + Assert.assertEquals(rowSet.numRows(), 5); opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLES", confOverlay); rowSet = serviceClient.fetchResults(opHandle); - Assert.assertTrue(rowSet.numRows() > 10); + Assert.assertTrue(rowSet.numRows() > 50); opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES", confOverlay); rowSet = serviceClient.fetchResults(opHandle); - Assert.assertEquals(rowSet.numRows(), 7); + Assert.assertTrue(rowSet.numRows() > 200); opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMNS", confOverlay); rowSet = serviceClient.fetchResults(opHandle); - Assert.assertTrue(rowSet.numRows() > 10); + Assert.assertTrue(rowSet.numRows() > 350); opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES", confOverlay); http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java ---------------------------------------------------------------------- diff --git a/itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java b/itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java index c9ae676..fb7062f 100644 --- a/itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java +++ b/itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java @@ -223,7 +223,6 @@ public class CliConfigs { includesFrom(testConfigProps, "minillaplocal.shared.query.files"); excludeQuery("bucket_map_join_tez1.q"); // Disabled in HIVE-19509 excludeQuery("special_character_in_tabnames_1.q"); // Disabled in HIVE-19509 - excludeQuery("sysdb.q"); // Disabled in HIVE-19509 excludeQuery("tez_smb_1.q"); // Disabled in HIVE-19509 excludeQuery("union_fast_stats.q"); // Disabled in HIVE-19509 excludeQuery("schema_evol_orc_acidvec_part.q"); // Disabled in HIVE-19509 http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/metastore/scripts/upgrade/hive/hive-schema-3.1.0.hive.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/hive/hive-schema-3.1.0.hive.sql b/metastore/scripts/upgrade/hive/hive-schema-3.1.0.hive.sql index f98894f..2d67242 100644 --- a/metastore/scripts/upgrade/hive/hive-schema-3.1.0.hive.sql +++ b/metastore/scripts/upgrade/hive/hive-schema-3.1.0.hive.sql @@ -1080,11 +1080,10 @@ SELECT DISTINCT cast(null as string), `DB_LOCATION_URI` FROM - `sys`.`DBS` D, `sys`.`TBLS` T, `sys`.`TBL_PRIVS` P + `sys`.`DBS` D LEFT JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - NOT restrict_information_schema() OR - D.`DB_ID` = T.`DB_ID` - AND T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND current_authorizer() = P.`AUTHORIZER`; @@ -1118,12 +1117,12 @@ SELECT DISTINCT 'NO', cast(null as string) FROM - `sys`.`TBLS` T, `sys`.`DBS` D, `sys`.`TBL_PRIVS` P + `sys`.`TBLS` T JOIN `sys`.`DBS` D ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND (NOT restrict_information_schema() OR T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))) + OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer(); CREATE OR REPLACE VIEW `TABLE_PRIVILEGES` @@ -1147,18 +1146,15 @@ SELECT DISTINCT IF (P.`GRANT_OPTION` == 0, 'NO', 'YES'), 'NO' FROM - `sys`.`TBL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - P.`TBL_ID` = T.`TBL_ID` - AND T.`DB_ID` = D.`DB_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR + (P2.`TBL_ID` IS NOT NULL AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP'))) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer(); + OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer()); CREATE OR REPLACE VIEW `COLUMNS` ( @@ -1299,21 +1295,16 @@ SELECT DISTINCT WHEN lower(C.TYPE_NAME) like 'numeric%' THEN 10 ELSE null END FROM - `sys`.`COLUMNS_V2` C, - `sys`.`SDS` S, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_COL_PRIVS` P + `sys`.`COLUMNS_V2` C JOIN `sys`.`SDS` S ON (C.`CD_ID` = S.`CD_ID`) + JOIN `sys`.`TBLS` T ON (S.`SD_ID` = T.`SD_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_COL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND C.`CD_ID` = S.`CD_ID` - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND C.`COLUMN_NAME` = P.`COLUMN_NAME` AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) - AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()); + AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer(); CREATE OR REPLACE VIEW `COLUMN_PRIVILEGES` ( @@ -1336,20 +1327,16 @@ SELECT DISTINCT P.`TBL_COL_PRIV`, IF (P.`GRANT_OPTION` == 0, 'NO', 'YES') FROM - `sys`.`TBL_COL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`SDS` S, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_COL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + JOIN `sys`.`SDS` S ON (S.`SD_ID` = T.`SD_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND P.`TBL_ID` = T.`TBL_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR P2.`TBL_ID` IS NOT NULL + AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer()); + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer(); CREATE OR REPLACE VIEW `VIEWS` ( @@ -1376,14 +1363,11 @@ SELECT DISTINCT false, false FROM - `sys`.`DBS` D, - `sys`.`TBLS` T, - `sys`.`TBL_PRIVS` P + `sys`.`DBS` D JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND length(T.VIEW_ORIGINAL_TEXT) > 0 - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + length(T.VIEW_ORIGINAL_TEXT) > 0 + AND (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()); http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/ql/src/test/queries/clientpositive/sysdb.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/sysdb.q b/ql/src/test/queries/clientpositive/sysdb.q index f47a941..6a03dd9 100644 --- a/ql/src/test/queries/clientpositive/sysdb.q +++ b/ql/src/test/queries/clientpositive/sysdb.q @@ -1,3 +1,5 @@ +--! qt:dataset:alltypesorc,alltypesparquet,part,src,src1,srcbucket,srcbucket2,src_cbo,src_json,src_sequencefile,src_thrift,srcpart,cbo_t1,cbo_t2,cbo_t3,lineitem + set hive.strict.checks.cartesian.product=false; set hive.compute.query.using.stats=false; @@ -5,8 +7,6 @@ set hive.compute.query.using.stats=false; set hive.support.concurrency=true; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -set hive.cbo.enable=false; - -- SORT_QUERY_RESULTS create table src_buck (key int, value string) clustered by(value) into 2 buckets; @@ -33,7 +33,7 @@ use sys; select bucket_col_name, integer_idx from bucketing_cols order by bucket_col_name, integer_idx limit 5; -select count(*) from cds; +select t.tbl_name, c.column_name from tbls t join sds s on t.sd_id=s.sd_id join columns_v2 c on s.cd_id=c.cd_id order by t.tbl_name, c.column_name; select column_name, type_name, integer_idx from columns_v2 order by column_name, integer_idx limit 5; @@ -68,8 +68,6 @@ select count(*) from sds; select param_key, param_value from sd_params order by param_key, param_value limit 5; -select sequence_name from sequence_table order by sequence_name limit 5; - select name, slib from serdes order by name, slib limit 5; select param_key, param_value from serde_params order by param_key, param_value limit 5; @@ -104,7 +102,7 @@ select func_name, func_type from funcs order by func_name, func_type limit 5; select constraint_name from key_constraints order by constraint_name limit 5; -select COLUMN_STATS_ACCURATE, NUM_FILES, NUM_ROWS, RAW_DATA_SIZE, TOTAL_SIZE FROM TABLE_STATS_VIEW where COLUMN_STATS_ACCURATE is not null order by NUM_FILES, NUM_ROWS, RAW_DATA_SIZE limit 5; +select COLUMN_STATS_ACCURATE, NUM_FILES, NUM_ROWS, RAW_DATA_SIZE, TOTAL_SIZE FROM TABLE_STATS_VIEW where COLUMN_STATS_ACCURATE is not null order by COLUMN_STATS_ACCURATE limit 5; select COLUMN_STATS_ACCURATE, NUM_FILES, NUM_ROWS, RAW_DATA_SIZE, TOTAL_SIZE FROM PARTITION_STATS_VIEW where COLUMN_STATS_ACCURATE is not null order by NUM_FILES, NUM_ROWS, RAW_DATA_SIZE limit 5; @@ -122,7 +120,7 @@ select * from TABLES order by TABLE_SCHEMA, TABLE_NAME; select * from TABLE_PRIVILEGES order by GRANTOR, GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE limit 10; -select * from COLUMNS where TABLE_NAME = 'alltypesorc' or TABLE_NAME = 'moretypes' order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION ; +select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_precision_radix,numeric_scale,datetime_precision,interval_type,interval_precision,character_set_catalog,character_set_schema,character_set_name,collation_catalog,collation_schema,collation_name,udt_catalog,udt_schema,udt_name,scope_catalog,scope_schema,scope_name,maximum_cardinality,is_self_referencing,is_identity,identity_generation,identity_start,identity_increment,identity_maximum,identity_minimum,identity_cycle,is_generated,generation_expression,is_system_time_period_start,is_system_time_period_end,system_time_period_timestamp_generation,is_updatable,declared_data_type,declared_numeric_precision,declared_numeric_scale from COLUMNS where TABLE_NAME = 'alltypesorc' or TABLE_NAME = 'moretypes' order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION ; select * from COLUMN_PRIVILEGES order by GRANTOR, GRANTEE, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME limit 10; http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/ql/src/test/results/clientpositive/llap/resourceplan.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/resourceplan.q.out b/ql/src/test/results/clientpositive/llap/resourceplan.q.out index 3933273..5132ac9 100644 --- a/ql/src/test/results/clientpositive/llap/resourceplan.q.out +++ b/ql/src/test/results/clientpositive/llap/resourceplan.q.out @@ -2392,11 +2392,10 @@ SELECT DISTINCT cast(null as string), `DB_LOCATION_URI` FROM - `sys`.`DBS` D, `sys`.`TBLS` T, `sys`.`TBL_PRIVS` P + `sys`.`DBS` D LEFT JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - NOT restrict_information_schema() OR - D.`DB_ID` = T.`DB_ID` - AND T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND current_authorizer() = P.`AUTHORIZER` @@ -2425,11 +2424,10 @@ SELECT DISTINCT cast(null as string), `DB_LOCATION_URI` FROM - `sys`.`DBS` D, `sys`.`TBLS` T, `sys`.`TBL_PRIVS` P + `sys`.`DBS` D LEFT JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - NOT restrict_information_schema() OR - D.`DB_ID` = T.`DB_ID` - AND T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND current_authorizer() = P.`AUTHORIZER` @@ -2475,12 +2473,12 @@ SELECT DISTINCT 'NO', cast(null as string) FROM - `sys`.`TBLS` T, `sys`.`DBS` D, `sys`.`TBL_PRIVS` P + `sys`.`TBLS` T JOIN `sys`.`DBS` D ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND (NOT restrict_information_schema() OR T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))) + OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs @@ -2517,12 +2515,12 @@ SELECT DISTINCT 'NO', cast(null as string) FROM - `sys`.`TBLS` T, `sys`.`DBS` D, `sys`.`TBL_PRIVS` P + `sys`.`TBLS` T JOIN `sys`.`DBS` D ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND (NOT restrict_information_schema() OR T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))) + OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs @@ -2563,18 +2561,15 @@ SELECT DISTINCT IF (P.`GRANT_OPTION` == 0, 'NO', 'YES'), 'NO' FROM - `sys`.`TBL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - P.`TBL_ID` = T.`TBL_ID` - AND T.`DB_ID` = D.`DB_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR + (P2.`TBL_ID` IS NOT NULL AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP'))) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer() + OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer()) PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs PREHOOK: Input: sys@tbl_privs @@ -2602,18 +2597,15 @@ SELECT DISTINCT IF (P.`GRANT_OPTION` == 0, 'NO', 'YES'), 'NO' FROM - `sys`.`TBL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - P.`TBL_ID` = T.`TBL_ID` - AND T.`DB_ID` = D.`DB_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR + (P2.`TBL_ID` IS NOT NULL AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP'))) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer() + OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer()) POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs POSTHOOK: Input: sys@tbl_privs @@ -2767,21 +2759,16 @@ SELECT DISTINCT WHEN lower(C.TYPE_NAME) like 'numeric%' THEN 10 ELSE null END FROM - `sys`.`COLUMNS_V2` C, - `sys`.`SDS` S, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_COL_PRIVS` P + `sys`.`COLUMNS_V2` C JOIN `sys`.`SDS` S ON (C.`CD_ID` = S.`CD_ID`) + JOIN `sys`.`TBLS` T ON (S.`SD_ID` = T.`SD_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_COL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND C.`CD_ID` = S.`CD_ID` - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND C.`COLUMN_NAME` = P.`COLUMN_NAME` AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) - AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) + AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@columns_v2 PREHOOK: Input: sys@dbs @@ -2929,21 +2916,16 @@ SELECT DISTINCT WHEN lower(C.TYPE_NAME) like 'numeric%' THEN 10 ELSE null END FROM - `sys`.`COLUMNS_V2` C, - `sys`.`SDS` S, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_COL_PRIVS` P + `sys`.`COLUMNS_V2` C JOIN `sys`.`SDS` S ON (C.`CD_ID` = S.`CD_ID`) + JOIN `sys`.`TBLS` T ON (S.`SD_ID` = T.`SD_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_COL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND C.`CD_ID` = S.`CD_ID` - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND C.`COLUMN_NAME` = P.`COLUMN_NAME` AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) - AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) + AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@columns_v2 POSTHOOK: Input: sys@dbs @@ -3020,20 +3002,16 @@ SELECT DISTINCT P.`TBL_COL_PRIV`, IF (P.`GRANT_OPTION` == 0, 'NO', 'YES') FROM - `sys`.`TBL_COL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`SDS` S, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_COL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + JOIN `sys`.`SDS` S ON (S.`SD_ID` = T.`SD_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND P.`TBL_ID` = T.`TBL_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR P2.`TBL_ID` IS NOT NULL + AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer()) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs PREHOOK: Input: sys@sds @@ -3063,20 +3041,16 @@ SELECT DISTINCT P.`TBL_COL_PRIV`, IF (P.`GRANT_OPTION` == 0, 'NO', 'YES') FROM - `sys`.`TBL_COL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`SDS` S, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_COL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + JOIN `sys`.`SDS` S ON (S.`SD_ID` = T.`SD_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND P.`TBL_ID` = T.`TBL_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR P2.`TBL_ID` IS NOT NULL + AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer()) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs POSTHOOK: Input: sys@sds @@ -3118,14 +3092,11 @@ SELECT DISTINCT false, false FROM - `sys`.`DBS` D, - `sys`.`TBLS` T, - `sys`.`TBL_PRIVS` P + `sys`.`DBS` D JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND length(T.VIEW_ORIGINAL_TEXT) > 0 - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + length(T.VIEW_ORIGINAL_TEXT) > 0 + AND (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) @@ -3160,14 +3131,11 @@ SELECT DISTINCT false, false FROM - `sys`.`DBS` D, - `sys`.`TBLS` T, - `sys`.`TBL_PRIVS` P + `sys`.`DBS` D JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND length(T.VIEW_ORIGINAL_TEXT) > 0 - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + length(T.VIEW_ORIGINAL_TEXT) > 0 + AND (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) http://git-wip-us.apache.org/repos/asf/hive/blob/6a3d42b3/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out b/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out index 7637f0e..d4aad39 100644 --- a/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out +++ b/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out @@ -2482,11 +2482,10 @@ SELECT DISTINCT cast(null as string), `DB_LOCATION_URI` FROM - `sys`.`DBS` D, `sys`.`TBLS` T, `sys`.`TBL_PRIVS` P + `sys`.`DBS` D LEFT JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - NOT restrict_information_schema() OR - D.`DB_ID` = T.`DB_ID` - AND T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND current_authorizer() = P.`AUTHORIZER` @@ -2515,11 +2514,10 @@ SELECT DISTINCT cast(null as string), `DB_LOCATION_URI` FROM - `sys`.`DBS` D, `sys`.`TBLS` T, `sys`.`TBL_PRIVS` P + `sys`.`DBS` D LEFT JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - NOT restrict_information_schema() OR - D.`DB_ID` = T.`DB_ID` - AND T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND current_authorizer() = P.`AUTHORIZER` @@ -2565,12 +2563,12 @@ SELECT DISTINCT 'NO', cast(null as string) FROM - `sys`.`TBLS` T, `sys`.`DBS` D, `sys`.`TBL_PRIVS` P + `sys`.`TBLS` T JOIN `sys`.`DBS` D ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND (NOT restrict_information_schema() OR T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))) + OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs @@ -2607,12 +2605,12 @@ SELECT DISTINCT 'NO', cast(null as string) FROM - `sys`.`TBLS` T, `sys`.`DBS` D, `sys`.`TBL_PRIVS` P + `sys`.`TBLS` T JOIN `sys`.`DBS` D ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND (NOT restrict_information_schema() OR T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))) + OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs @@ -2653,18 +2651,15 @@ SELECT DISTINCT IF (P.`GRANT_OPTION` == 0, 'NO', 'YES'), 'NO' FROM - `sys`.`TBL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - P.`TBL_ID` = T.`TBL_ID` - AND T.`DB_ID` = D.`DB_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR + (P2.`TBL_ID` IS NOT NULL AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP'))) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer() + OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer()) PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs PREHOOK: Input: sys@tbl_privs @@ -2692,18 +2687,15 @@ SELECT DISTINCT IF (P.`GRANT_OPTION` == 0, 'NO', 'YES'), 'NO' FROM - `sys`.`TBL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - P.`TBL_ID` = T.`TBL_ID` - AND T.`DB_ID` = D.`DB_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR + (P2.`TBL_ID` IS NOT NULL AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' - OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP'))) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer() + OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER` = current_authorizer() AND P2.`AUTHORIZER` = current_authorizer()) POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs POSTHOOK: Input: sys@tbl_privs @@ -2857,21 +2849,16 @@ SELECT DISTINCT WHEN lower(C.TYPE_NAME) like 'numeric%' THEN 10 ELSE null END FROM - `sys`.`COLUMNS_V2` C, - `sys`.`SDS` S, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_COL_PRIVS` P + `sys`.`COLUMNS_V2` C JOIN `sys`.`SDS` S ON (C.`CD_ID` = S.`CD_ID`) + JOIN `sys`.`TBLS` T ON (S.`SD_ID` = T.`SD_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_COL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND C.`CD_ID` = S.`CD_ID` - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND C.`COLUMN_NAME` = P.`COLUMN_NAME` AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) - AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) + AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@columns_v2 PREHOOK: Input: sys@dbs @@ -3019,21 +3006,16 @@ SELECT DISTINCT WHEN lower(C.TYPE_NAME) like 'numeric%' THEN 10 ELSE null END FROM - `sys`.`COLUMNS_V2` C, - `sys`.`SDS` S, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`TBL_COL_PRIVS` P + `sys`.`COLUMNS_V2` C JOIN `sys`.`SDS` S ON (C.`CD_ID` = S.`CD_ID`) + JOIN `sys`.`TBLS` T ON (S.`SD_ID` = T.`SD_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + LEFT JOIN `sys`.`TBL_COL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND C.`CD_ID` = S.`CD_ID` - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND C.`COLUMN_NAME` = P.`COLUMN_NAME` AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) - AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) + AND P.`TBL_COL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@columns_v2 POSTHOOK: Input: sys@dbs @@ -3110,20 +3092,16 @@ SELECT DISTINCT P.`TBL_COL_PRIV`, IF (P.`GRANT_OPTION` == 0, 'NO', 'YES') FROM - `sys`.`TBL_COL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`SDS` S, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_COL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + JOIN `sys`.`SDS` S ON (S.`SD_ID` = T.`SD_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND P.`TBL_ID` = T.`TBL_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR P2.`TBL_ID` IS NOT NULL + AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer()) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer() PREHOOK: type: CREATEVIEW PREHOOK: Input: sys@dbs PREHOOK: Input: sys@sds @@ -3153,20 +3131,16 @@ SELECT DISTINCT P.`TBL_COL_PRIV`, IF (P.`GRANT_OPTION` == 0, 'NO', 'YES') FROM - `sys`.`TBL_COL_PRIVS` P, - `sys`.`TBLS` T, - `sys`.`DBS` D, - `sys`.`SDS` S, - `sys`.`TBL_PRIVS` P2 + `sys`.`TBL_COL_PRIVS` P JOIN `sys`.`TBLS` T ON (P.`TBL_ID` = T.`TBL_ID`) + JOIN `sys`.`DBS` D ON (T.`DB_ID` = D.`DB_ID`) + JOIN `sys`.`SDS` S ON (S.`SD_ID` = T.`SD_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P2 ON (P.`TBL_ID` = P2.`TBL_ID`) WHERE - S.`SD_ID` = T.`SD_ID` - AND T.`DB_ID` = D.`DB_ID` - AND P.`TBL_ID` = T.`TBL_ID` - AND (NOT restrict_information_schema() OR - P.`TBL_ID` = P2.`TBL_ID` AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` + NOT restrict_information_schema() OR P2.`TBL_ID` IS NOT NULL + AND P.`PRINCIPAL_NAME` = P2.`PRINCIPAL_NAME` AND P.`PRINCIPAL_TYPE` = P2.`PRINCIPAL_TYPE` AND (P2.`PRINCIPAL_NAME`=current_user() AND P2.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P2.`PRINCIPAL_NAME`) OR P2.`PRINCIPAL_NAME` = 'public') AND P2.`PRINCIPAL_TYPE`='GROUP')) - AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer()) + AND P2.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer() AND P2.`AUTHORIZER`=current_authorizer() POSTHOOK: type: CREATEVIEW POSTHOOK: Input: sys@dbs POSTHOOK: Input: sys@sds @@ -3208,14 +3182,11 @@ SELECT DISTINCT false, false FROM - `sys`.`DBS` D, - `sys`.`TBLS` T, - `sys`.`TBL_PRIVS` P + `sys`.`DBS` D JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND length(T.VIEW_ORIGINAL_TEXT) > 0 - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + length(T.VIEW_ORIGINAL_TEXT) > 0 + AND (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer()) @@ -3250,14 +3221,11 @@ SELECT DISTINCT false, false FROM - `sys`.`DBS` D, - `sys`.`TBLS` T, - `sys`.`TBL_PRIVS` P + `sys`.`DBS` D JOIN `sys`.`TBLS` T ON (D.`DB_ID` = T.`DB_ID`) + LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` = P.`TBL_ID`) WHERE - D.`DB_ID` = T.`DB_ID` - AND length(T.VIEW_ORIGINAL_TEXT) > 0 - AND (NOT restrict_information_schema() OR - T.`TBL_ID` = P.`TBL_ID` + length(T.VIEW_ORIGINAL_TEXT) > 0 + AND (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER' OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP')) AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer())
