Fun April Fool’s Day Trick Using Directed Queries
Jim_Knicely
- Select Field - Administrator
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!
Have fun!
1