We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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