Thursday, May 20, 2010

Postgresql users are spoiled with conveniences

Saw this question on stackoverflow

Are there any updates in SQL 2008 to allow a variable for the IN of a where clause?

Declare @InParams varchar(100)
Set @InParams = '1,2'

Select * from Category
Where CategoryID in @InParams

Well it is easy to do that in Postgres:

Select * from Category
Where CategoryID = Any(string_to_array('1,2', ',')::int[])

Tuesday, May 18, 2010

varchar IP address sorting

Equivalent of Oracle code with varchar field containing ip (Note: 2.5.6.7 should sort first than 10.3.4.5) ...

SELECT col
  FROM table
 ORDER BY 
(regexp_substr(col, '[^.]+', 1, 1) * 256  * 256  * 256 ) + (regexp_substr(col, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(col, '[^.]+', 1, 3) * 256 ) + regexp_substr(col, '[^.]+', 1, 4)

...To Postgres:

select x.col
from (values('10.3.4.5'),('2.5.6.7')) as x(col)
order by 
split_part(x.col, '.', 1)::int * 256 * 256 * 256
+ split_part(x.col, '.', 2)::int * 256 * 256 
+ split_part(x.col, '.', 3)::int * 256 
+ split_part(x.col, '.', 3)::int

Postgres has built-in ip address type (inet):

select x.col
from (values('10.3.4.5'),('2.5.6.7')) as x(col)
order by 
x.col::inet

Saturday, May 15, 2010

Custom sort order on hierarchical query

How to make Project Manager 2 sort before Project Manager 1. Tester 4 sort before Tester 3?

emp_id |            emp_name             
--------+---------------------------------
      1 | President
      2 |   Vice President
      3 |     CEO
      4 |     CTO
      5 |       Group Project Manager
      6 |         Project Manager 1
      8 |           Team Leader 1
      9 |             Software Engineer 1
     10 |             Software Engineer 2
     11 |           Test Lead 1
     12 |             Tester 1
     13 |             Tester 2
      7 |         Project Manager 2
     14 |           Team Leader 2
     15 |             Software Engineer 3
     16 |             Software Engineer 4
     17 |           Test Lead 2
     18 |             Tester 3
     19 |             Tester 4
     20 |             Tester 5
(20 rows)


Wednesday, May 12, 2010

What's in a name? .NET Framework naming gotchas

I saw a question in Stackoverflow

I was recently working with a DateTime object, and wrote something like this:

DateTime dt = DateTime.Now;
dt.AddDays(1);
return dt; // still today's date!

The intellisense documentation for AddDays says it adds a day to the date, which it doesn't - it actually returns a date with a day added to it, so you have to write it like:

DateTime dt = DateTime.Now;
dt = dt.AddDays(1);
return dt; // tomorrow's date


I think AddDays should be named NextDays. dt.NextDays(1) doesn't have a notion that it changes its own value, in the same vein that data structure constructs, e.g. node->next, node.next, doesn't modify the value of node. And.. it's too late now, they could give dt.AddDays the semantics that it add days to its own value

Sunday, May 9, 2010

Why is EXISTS (SELECT 1 ...) a cargo cult programming?

Why application programmers are using this kind of data existence checking...
select author_id, post_id, post from posts 
where exists(select 1 from comments where author_id = posts.author_id and post_id = posts.post_id)


 author_id | post_id |         post
-----------+---------+----------------------
       777 |       1 | what is optimization
(1 row)

...instead of this?

select author_id, post_id, post from posts 
where exists(select * from comments where author_id = posts.author_id and post_id = posts.post_id)


To create test data, expand the following:
create table posts
(
author_id int not null, -- composite pk
post_id int not null, -- composite pk
post varchar(50)  not null
);

insert into posts(author_id, post_id, post) values(777, 1, 'what is optimization');
insert into posts(author_id, post_id, post) values(777, 2, 'how to profile');

create table comments
(
author_id int not null,
post_id int not null,
commenter_id int not null,
comment varchar(50)
);

insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'magnificent');
insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'great');
insert into comments(author_id, post_id, commenter_id, comment) values(777,1, 8, 'excellent');


Why use 1 instead of asterisk? Most application programmers when are in sight of asterisk inside EXIST clause, they cringe for its purported inefficiency, they thought that asterisk always project data, and as such can incur delays to query. They have this ill-conceived notion that the asterisk fetches columns. If there's a way to avoid the SELECT asterisk altogether, all will be happy, no one will have a nagging feeling that the engineers of their favorite database are not smart.

Any takers of this syntax?
select * from posts 
where exists( from comments where author_id = posts.author_id and post_id = posts.post_id)

That EXIST clause doesn't have a look and feel of projecting unnecessary data. Now, you might not immediately like that (though might be if you are C# programmer that is accustomed to Linq syntax), so for better or for worse, we have to contend with the fact that in order to make parsing job easier, the language designer have designed that the query inside of EXISTS clause must conform to normal SQL syntax too. Language aesthetics or language symmetry also played a factor here.

Now are the irrational feelings that the asterisk inside EXISTS project data warranted? It's not, contrary to popular belief, database engineers are smarter than you and I will ever be. Do application programmers think that the database vendors wanted to lose their product mindshares to their competitors?

Database is a complex product that potential bottleneck like that construct won't let go past unnoticed, the keyword EXISTS is already a directive to RDBMS that data projecting should be suppressed from query inside of EXISTS. Why should they blindly project data inside of EXISTS?

To disprove that irrational fear that EXISTS clause is projecting data(it isn't), try this in your favorite database:

select * from posts 
where exists(select 1/0 from comments where author_id = posts.author_id and post_id = posts.post_id)

Did it result to divide by zero error? If it is, try to hurl criticisms to the vendor of your favorite database. But rest assured, we can be practically sure that our database won't blindly project data inside of EXISTS clause.

If only all database supports tuple testing, we can do this:

select * from posts where (author_id,post_id) in (select author_id, post_id from comments)


But some database don't support that tuple testing. So we have to do this:

select * from posts 
where /* (author_id,post_id) in */
exists 
    (select author_id, post_id 
    from comments 
    where author_id = posts.author_id and post_id = posts.post_id)

If I would have to translate the tuple testing to WHERE EXISTS clause, I would retain the two fields in first WHERE clause and put them in comment brackets; and inside of EXISTS clause, I would preserve the tuples to be tested upon, it makes for great documentation. It's better than EXISTS(SELECT 1) and EXISTS(SELECT *)

On some database, they have sleep function to simulate delay. To prove that our database discard data projection of EXISTS clause. Try to do this (change the pg_sleep with your database's equivalent):

select * from posts 
where exists(
select pg_sleep(5000) -- 5 seconds delay
from comments where author_id = posts.author_id and post_id = posts.post_id)

test=# select * from posts 
where exists(
    select pg_sleep(5000) 
    from comments 
    where author_id = posts.author_id and post_id = posts.post_id);

 author_id | post_id |         post
-----------+---------+----------------------
       777 |       1 | what is optimization
(1 row)

Time: 1.548 ms

As we can see, it didn't incur any delay

Strive for readability first before applying cargo cult programming. Don't have that irrational fear that asterisk always gets expanded to data projection

Now, I'm not suggesting to application programmers that they convert all their EXISTS(SELECT 1 FROM ..) to EXISTS(SELECT * FROM ..). Just remove the irrational fear, eliminate or minimize your Cargo Cult Programming.

I don't mind if application programmers or colleagues are used to with EXISTS(SELECT 1..), but I would do mind if they will go out their way to bikeshed and point out that EXISTS(SELECT * ) is not efficient. I trust the engineers of database more than the programmers who just merely use database.