Daily build fails on catman1/TEST138 Hortonworks

This is the analysis for the failure of catman test 138 on HortonWorks in the daily build of January 6, 2015.

The first difference is at line 1194
1192-1196
>>create table user1_t1 (c1 int not null primary key, c2 int);

*** ERROR[1017] You are not authorized to perform this operation.

--- SQL operation failed with errors.

The “not authorized” error is from a CREATE TABLE statement being performed by user SQL_USER1. The schema is a private schema (owned by DB__ROOT), so a user cannot create a table in the schema unless they have the CREATE_TABLE or CREATE component privilege.

The test expects the CREATE TABLE command to succeed. The rest of the test fails because the expected table is not present.

A user has a DDL privilege if they have been granted the privilege directly, or it has been granted to a role granted to the user, or the privilege has been granted to PUBLIC. In this section of the test, the user (SQL_USER1) does not have the CREATE or CREATE_TABLE privilege, but the CREATE privilege has been granted to PUBLIC, therefore, the CREATE TABLE should succeed.

Since the CREATE TABLE command fails with a “not authorized” error, there are two possibilities.
1) The write (INSERT) of the privilege failed.
2) The read (SELECT) of the privilege failed.

After the privilege is granted to PUBLIC, the test issues a GET command to verify the privilege was granted. Here is the output from the GET commands just prior to the CREATE TABLE command:

1154-1166
>>get privileges on component sql_operations for "PUBLIC";

Privilege information on Component SQL_OPERATIONS for PUBLIC
============================================================

CREATE
CREATE_SCHEMA
SHOW

--- SQL operation complete.
>>get privileges on component sql_operations for sql_user1;

--- SQL operation complete.
Clearly the INSERT succeeded, therefore, the SELECT must have failed.

Here is the SELECT statement that is not returning expected values:

SELECT COUNT(*) FROM TRAFODION.PRIVMGR_MD.COMPONENT_PRIVILEGES
       WHERE COMPONENT_UID = 1 AND
             (OPERATION_CODE = 'CT’ OR OPERATION_CODE = 'C0’) AND
             (GRANTEE_ID = -1 OR GRANTEE_ID = 33334)

The user ID for PUBLIC is -1, and for SQL_USER1 it is 33334. CT is the operation code for CREATE_TABLE and C0 is the operation code for CREATE. The query is looking for one or more rows where either the CREATE or CREATE_TABLE privilege has been granted to either the user or PUBLIC. Most of the time the result is one, but sometimes the query either fails or returns zero.