Tricky question

The Problem

A friend of mine had a job interview a while ago. One of the questions was:

In what case SELECT * FROM table WHERE ColName = ColName query will return incomplete table content?

Canonic Answer

I’m pretty sure most of you know the canonic answer. According to SQL standard query will return incomplete table content if at least one ColName value equals to NULL.

But if you want to surprise interviewer you should expand your respond. Let’s see how.

First Shot

Almost any advanced DBMS has special NULL comparison option. PostgreSQL is not an exclusion. Here we have transform_null_equals parameter. But it works only if you are trying to compare some expression exactly with NULL value.

That means changing this parameter value will not affect on the result of query introduced. But you will show hirer knowing and understanding of low-level tuning.😉

Head Shot

Who said that “incomplete table content” means not all rows from the table? How about not all columns?🙂 Yeah, baby. God bless PostgreSQL for having system columns. At least for ability of brilliant display of knowledge. Thus you may answer that query stated above will always return incomplete table content unless you rewrite it to

SELECT oid, tableoid, xmin, xmax, cmin, cmax, ctid, *
  FROM table
-- or if WITHOUT OIDS specified
SELECT tableoid, xmin, xmax, cmin, cmax, ctid, *
  FROM table

Conclusion

As my professor used to say: “Exam is a conversation of two smart people”. The same thing applies to any job interview. Don’t be shy to show your understanding of topic even if some details are very specific.

Keep your eyes peeled.

2 thoughts on “Tricky question

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s