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

KKirkpatrick

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)

[Deleted User]

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

 

Thanks, 

Eugenia

KKirkpatrick

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=>

KKirkpatrick

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).

 

 

[Deleted User]

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. 

 

 

 

KKirkpatrick

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.