Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Possible Bug: Views do not retain schema of User Defined Aggregations

This is in version 7.2.  Please let me know if I'm overlooking something.  Goal is to create a User Defined Aggregate in Schema B and view in Schema A that uses that UDA.  If Schema B is in user's search path, the view works.  But if schema B is not in the user's search path, queries against view fail.

 

CREATE VIEW A.BAR AS SELECT B.FOO(5);

 

SET SEARCH_PATH = A,B;

SELECT * FROM A.BAR; -- this works

SELECT B.FOO(5); -- this works

 

SET SEARCH_PATH = A;

 

SELECT B.FOO(5); -- this works

SELECT * FROM A.BAR; -- but this doesnt:

 

 

ERROR 3457: Function FOO(INTEGER) does not exist, or permission is denied for FOO(INTEGER)

Comments

  • can you give me the output of the export_object('','<view>'); and what version of vertica are you using?

     

    Thanks, 

    Eugenia

  • Vertica version 7.2

     

    DUMMY_USER=> CREATE AGGREGATE FUNCTION DUMMY_SCHEMA1.LIST_AGG AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY IQI_PUBLIC.AGGREGATE_FUNCTIONS;
    CREATE AGGREGATE FUNCTION
    DUMMY_USER=> CREATE VIEW DUMMY_SCHEMA2.LIST_AGG_VIEW AS SELECT DUMMY_SCHEMA1.LIST_AGG('X');
    CREATE VIEW
    DUMMY_USER=> SET SEARCH_PATH=DUMMY_SCHEMA1;
    SET
    DUMMY_USER=> SELECT DUMMY_SCHEMA1.LIST_AGG('X');
    LIST_AGG
    ----------
    X
    (1 row)

    DUMMY_USER=> SELECT * FROM DUMMY_SCHEMA2.LIST_AGG_VIEW;
    LIST_AGG
    ----------
    X
    (1 row)

    DUMMY_USER=> SET SEARCH_PATH = DUMMY_SCHEMA2;
    SET
    DUMMY_USER=> SELECT DUMMY_SCHEMA1.LIST_AGG('X');
    LIST_AGG
    ----------
    X
    (1 row)

    DUMMY_USER=> SELECT * FROM DUMMY_SCHEMA2.LIST_AGG_VIEW;
    ERROR 3457: Function LIST_AGG(varchar) does not exist, or permission is denied for LIST_AGG(varchar)
    HINT: No function matches the given name and argument types. You may need to add explicit type casts
    DUMMY_USER=> SELECT EXPORT_OBJECTS('','DUMMY_SCHEMA2.LIST_AGG_VIEW');
    EXPORT_OBJECTS
    --------------------------------------------------------------------------------------------------------------------------------------------------

     


    CREATE VIEW DUMMY_SCHEMA2.LIST_AGG_VIEW AS
    SELECT LIST_AGG('X'::varchar(1)) AS LIST_AGG FROM v_catalog.dual;

    SELECT MARK_DESIGN_KSAFE(1);

    (1 row)

    DUMMY_USER=>

  • Copy/pasted from my thread on Toad Data Point:


    This is tangentially related to my previously-reported bug on UDAF schema in views.  Combined, the TOAD Data Point SEARCH_PATH-override bug and the UDAF-schema bug prevent me from joining any two views which have UDAF from separate schemas (I can only point TOAD to one schema, and whichever I choose, the view with UDAF from the other schema will give error).

     

     

  • Can you send me the export_object('',<view_name>');?

     

    There was a bug in vertica that if you create the view with your search_path set to the schema where you want the view to reside, Vertica removes all of the search_path schema prefixes from within the view definition. When the view is called and the search_path does not contain the search_path that was used during creation the view throws an error.

     

    That issue was resolved in one of the 7.2.2 hot fixes, but I would like to see your view definition in export_object to see if this is an instance of that bug. If that is the case updating to the latest 7.2 version should fix the issue; but I would like to confirm before recomending anything. 

     

     

     

  • Thanks Eugenia - export_object was included as last step of my listing above.  Per your feedback, this sounds like the same bug.  Please confirm, and I will initiate an upgrade to get the patch.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.