Fun April Fool’s Day Trick Using Directed Queries

Posted April 1, 2019 by Jim Knicely, Vertica Principal Solution Architect

Directed Queries in Vertica encapsulate information that the optimizer can use to create a query plan. Directed queries can serve the following goals:
  • Preserve current query plans before a scheduled upgrade. In most instances, queries perform more efficiently after a Vertica upgrade. In the few cases where this is not so, you can use directed queries that you created before upgrading to recreate query plans from the earlier version.
  • Enable you to create query plans that improve optimizer performance. Occasionally, you might want to influence the optimizer to make better choices in executing a given query. For example, you can choose a different projection, or force a different join order. In this case, you can use a directed query to create a query plan that preempts any plan that the optimizer might otherwise create.
  • Redirect an input query to a query that uses different semantics. For example, map a join query to a SELECT statement that queries a flattened table.
But, since today is April Fool’s Day, the Directed Query function can lead to some fun tricks a DBA can play on developers!

Example:

Developer Jim has a table named VERY_IMPORTANT_DATA: dbadmin=> SELECT * FROM very_important_data; do_not_lose_this_info -------------------------- Secret of Life Encrypted (1 row) As an April Fool’s joke, let’s change the output of his query. dbadmin=> SAVE QUERY SELECT do_not_lose_this_info FROM very_important_data; SAVE QUERY dbadmin=> CREATE DIRECTED QUERY CUSTOM 'aprilfools' dbadmin-> SELECT 'The VERY_IMPORTANT_DATA table has been dropped!' do_not_lose_this_info; CREATE DIRECTED QUERY dbadmin=> ACTIVATE DIRECTED QUERY aprilfools; ACTIVATE DIRECTED QUERY So now what does our unsuspecting developer Jim see? dbadmin=> \c - jim You are now connected as user "jim". dbadmin=> SELECT do_not_lose_this_info FROM very_important_data; do_not_lose_this_info ------------------------------------------------- The VERY_IMPORTANT_DATA table has been dropped! (1 row) Ha-Ha!

Note: This was just for fun!

Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/DirectedQueries.htm

Have fun!