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?
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.
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. 😉
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
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.