Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5443

Using RAND() function in a column mask errors on temp table creation

    Details

    • Steps to Reproduce:
      Hide

      Using below VDB with column masking attempting to use RAND() to generate a random number to mask the column

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <vdb name="columnmask" version="2">
      <description/>
      <property name="validationDateTime" value="Mon Aug 13 07:19:31 CDT 2018"/>
      <property name="validationVersion" value="8.12.13"/>
      <model name="debbie_pg" visible="true">
      <source connection-jndi-name="java:/debbie_pg_DS" name="debbie_pg" translator-name="postgresql"/>
      <metadata type="DDL"><![CDATA[

      CREATE FOREIGN TABLE emp (
      id integer NOT NULL OPTIONS(NAMEINSOURCE '"id"', NATIVE_TYPE 'int4', FIXED_LENGTH 'TRUE'),
      name string(40) OPTIONS(NAMEINSOURCE '"name"', NATIVE_TYPE 'bpchar', FIXED_LENGTH 'TRUE'),
      CONSTRAINT pk_emp PRIMARY KEY(id) OPTIONS(NAMEINSOURCE '"pk_emp"')
      ) OPTIONS(NAMEINSOURCE '"public"."emp"', UPDATABLE 'TRUE', CARDINALITY '10');

      CREATE FOREIGN FUNCTION RANDOM (
      OUT newParameter_4 double NOT NULL RESULT
      ) OPTIONS(NAMEINSOURCE 'RANDOM', "NULL-ON-NULL" 'TRUE');

      ]]></metadata>
      </model>
      <data-role allow-create-temporary-tables="false" any-authenticated="true" grant-all="false" name="DataRole1">
      <description/>
      <permission>
      <resource-name>debbie_pg</resource-name>
      <allow-create>true</allow-create>
      <allow-read>true</allow-read>
      <allow-update>true</allow-update>
      <allow-delete>true</allow-delete>
      <allow-execute>true</allow-execute>
      <allow-alter>true</allow-alter>
      </permission>
      <permission>
      <resource-name>debbie_pg.emp.name</resource-name>
      <condition>name='Jane Doe'</condition>
      <mask order="0">UCASE(name)</mask>
      </permission>
      <permission>
      <resource-name>debbie_pg.emp.id</resource-name>
      <condition>id>0</condition>
      <mask order="1">convert(RAND()*100000,integer)</mask>
      </permission>
      </data-role>
      </vdb>

      Change the mask to use the source function RANDOM() and it works correctly.
      <permission>
      <resource-name>debbie_pg.emp.id</resource-name>
      <condition>id>0</condition>
      <mask order="1">convert(RANDOM()*100000,integer)</mask>
      </permission>

      Show
      Using below VDB with column masking attempting to use RAND() to generate a random number to mask the column <?xml version="1.0" encoding="UTF-8" standalone="no"?> <vdb name="columnmask" version="2"> <description/> <property name="validationDateTime" value="Mon Aug 13 07:19:31 CDT 2018"/> <property name="validationVersion" value="8.12.13"/> <model name="debbie_pg" visible="true"> <source connection-jndi-name="java:/debbie_pg_DS" name="debbie_pg" translator-name="postgresql"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE emp ( id integer NOT NULL OPTIONS(NAMEINSOURCE '"id"', NATIVE_TYPE 'int4', FIXED_LENGTH 'TRUE'), name string(40) OPTIONS(NAMEINSOURCE '"name"', NATIVE_TYPE 'bpchar', FIXED_LENGTH 'TRUE'), CONSTRAINT pk_emp PRIMARY KEY(id) OPTIONS(NAMEINSOURCE '"pk_emp"') ) OPTIONS(NAMEINSOURCE '"public"."emp"', UPDATABLE 'TRUE', CARDINALITY '10'); CREATE FOREIGN FUNCTION RANDOM ( OUT newParameter_4 double NOT NULL RESULT ) OPTIONS(NAMEINSOURCE 'RANDOM', "NULL-ON-NULL" 'TRUE'); ]]></metadata> </model> <data-role allow-create-temporary-tables="false" any-authenticated="true" grant-all="false" name="DataRole1"> <description/> <permission> <resource-name>debbie_pg</resource-name> <allow-create>true</allow-create> <allow-read>true</allow-read> <allow-update>true</allow-update> <allow-delete>true</allow-delete> <allow-execute>true</allow-execute> <allow-alter>true</allow-alter> </permission> <permission> <resource-name>debbie_pg.emp.name</resource-name> <condition>name='Jane Doe'</condition> <mask order="0">UCASE(name)</mask> </permission> <permission> <resource-name>debbie_pg.emp.id</resource-name> <condition>id>0</condition> <mask order="1">convert(RAND()*100000,integer)</mask> </permission> </data-role> </vdb> Change the mask to use the source function RANDOM() and it works correctly. <permission> <resource-name>debbie_pg.emp.id</resource-name> <condition>id>0</condition> <mask order="1">convert(RANDOM()*100000,integer)</mask> </permission>

      Description

      Column masking with RAND() tries to create a temp table and errors:

      09:47:47,601 ERROR [org.teiid.PROCESSOR] (Worker20_QueryProcessorQueue88) TEIID30019 Unexpected exception for request IAgYDrdYXFRm.30: org.teiid.core.TeiidComponentException: TEIID30091 org.teiid.api.exception.query.QueryResolverException: TEIID30091 Cannot create group 'sec AS sec__1' with multiple columns named 'expr'

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dsteigne Deborah Steigner
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: