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

Minus operator

We have diferences when query a view and the source select. We tried even
select * from view
minus
select * from original_query

The result is not empty, even each time the query returns different number of rows.

We tried also select * from view MINUS select * from view and the result is not empty.

Is that the normal behaviour in Vertica?

Regards.

Comments

  • Jim_KnicelyJim_Knicely Administrator

    What is the SQL for the view?

  • moshegmosheg Employee
    edited September 13

    If you have differences when you query a view and the source select, check the following:
    1. Does the view and the source query tables are in the same schema?
    2. Check your SCHEMA path:
    SHOW SEARCH_PATH;
    SELECT CURRENT_SCHEMA;
    3. Just for troubleshooting, does the view owner, the source tables owner, and the user who run the query are different?
    Or have any ACCESS POLICY definitions?
    Try to query the view and afterwards the source SELECT from DB Super User, like dbadmin.
    4. What is your Vertica version? SELECT version();
    Do VER-77394 or VER-78130 is installed?
    5. Like Jim asked, what is your view definition?
    SELECT view_definition FROM views WHERE table_name ILIKE '%somthing%';
    Or check specific view definition:
    SELECT export_objects('','MY_SCHEMA.MY_VIEW');
    Or if it is a system view, check your system view definition:
    SELECT * from vs_system_views where view_name ilike '%partitions%';

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.