Fun April Fool’s Day Trick Using Directed Queries

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited April 2019 in Tips from the Team

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!


Developer Jim has a table named VERY_IMPORTANT_DATA:

dbadmin=> SELECT * FROM very_important_data;
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;

dbadmin=> CREATE DIRECTED QUERY CUSTOM 'aprilfools'
dbadmin->   SELECT 'The VERY_IMPORTANT_DATA table has been dropped!' do_not_lose_this_info;

dbadmin=> ACTIVATE DIRECTED QUERY aprilfools;

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;
The VERY_IMPORTANT_DATA table has been dropped!
(1 row)


Note: This was just for fun!

Helpful Link:

Have fun!

Sign In or Register to comment.