Fun April Fool’s Day Trick Using Directed Queries

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited April 1 in Vertica Tips

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/9.2.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/DirectedQueries.htm

Have fun!

Sign In or Register to comment.