Null-safe Comparison in SQL

By on

Even though I know the concept of 3-valued logic in SQL, I trip over it from time to time.

For example, say, we have some tags and tags belong to tag_groups.

The tags table in MySQL might look like the following.

  name         VARCHAR(20),
  tag_group_id BIGINT,


Imagine we have the following rows in the table.

INSERT INTO tags (name,      tag_group_id)
          VALUES ('mysql',   1),
                 ('psql',    1),
                 ('sqlite',  1),
                 ('android', 2),
                 ('ios',     2),
                 ('emacs',   NULL);

Now, let’s try to answer the following questions.

How many tags in the table?

SELECT count(*) FROM tags; -- 6

How many tags in tag group 1?

SELECT count(*) FROM tags WHERE tag_group_id = 1; -- 3

How many tags that are NOT in tag group 1?

SELECT count(*) FROM tags WHERE tag_group_id != 1; -- 2

Wait… Isn’t the answer 3 instead of 2?

Actually, the result set returned by the query does not include the tag whose tag_group_id is NULL.

This is because in SQL, the equality between NULL and anything else is NULL, instead of TRUE or FALSE.

SELECT        1 = 1,     -- 1
           NULL = NULL,  -- NULL (!)
              1 = NULL,  -- NULL (!)
       NOT(   1 = 1),    -- 0
       NOT(NULL = NULL), -- NULL (!)
       NOT(   1 = NULL), -- NULL (!)

Fortunately, there are so-called null-safe operators which can perform equality comparison in a way that is common in many other programming languages.

In MySQL, <=> is a null-safe operator for equality.

SELECT        1 <=> 1,     -- 1
           NULL <=> NULL,  -- 1
              1 <=> NULL,  -- 0
       NOT(   1 <=> 1),    -- 0
       NOT(NULL <=> NULL), -- 0
       NOT(   1 <=> NULL); -- 1

PostgreSQL provides IS DISTINCT FROM and IS is the counterpart in SQLite.

Finally, I have written this down. I hope this post can save me a few Google searches in the future. 💦

Private Class Methods in Ruby

By on

To make a class method private, we just have to place a private keyword before it, like this:

class Foo




Negative, it is still public:

=> "bar" 

To make it private, we have to call private_class_method, like this:

class Foo

  private_class_method :bar


Then it works:

NoMethodError: private method `bar' called for Foo:Class

Why is that?

Before I try to explain it, I’d like to introduce another way to create a private class method:

class Foo
  class << self


    def bar

Have you noticed the problem?

In Ruby, as many of us know, classes are just objects. When we define “class methods” on Foo, we are just defining methods on the singleton class of the Foo object.

private is not a special keyword in Ruby. It is just a method of Module. (Reminder: Class.is_a? Module.)

When private gets called, it sets the visibility for subsequently methods defined on the current object to private. To set the visibility of methods defined on the singleton class of the current object, we need another method, i.e. Module#private_class_method.

P.S. The term “singleton class” referred in this post is also called “metaclass” or “eigenclass”.

When Your Ctrl-C Gets Trapped

By on

Have you ever met a program that no matter how hard you hit Ctrl-C, it just wouldn’t stop?

I was a little annoyed by such behavior of the bc (basic calculator) program on Mac (or FreeBSD):

$ bc -q
(interrupt) use quit to exit.

In fact, when hitting Ctrl-C, we are just sending a SIGINT signal to the program. The program may trap the signal and probably ignore it.

To achieve that in Ruby is simple:

loop do
  input = gets.chomp

  exit if input == 'quit'

  # calculate(input)

  trap(:INT) do
    puts '(interrupt) use quit to exit.'

Some processes even wouldn’t stop when we try to kill it:

$ kill <pid>

In such cases, the SIGTERM signal sent by kill is trapped.

We can force them to stop by sending the the SIGKILL signal, since it cannot be caught:

$ kill -s KILL <pid>

Shame Driven Development

By on

Several days ago, I saw a code challenge on CodeIQ.

It challenges users to write a ruby program that mimics the basic functionalities of the UNIX cal command. Users are encouraged to not rely on Date, Time, Datetime or other libraries. Expected time of writing this program is 15 minutes.

$ cal 12 2012
   December 2012
Su Mo Tu We Th Fr Sa
 2  3  4  5  6  7  8
 9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31

That should be an easy exercise. Almost, if not every, programmer has written a cal or two in their early days of programming. I should have written one in C in my first year of college. But, could I write a simple cal now, say, in 15 minutes? Without relying on any libraries?

I pondered. Then a question flashed into my mind: How do I know if a year is a leap year or not? In elementary school, I was taught that there is a leap year every 4 years. Is it really as simple as that?

As usual, Wikipedia has the answer. And there is some pseudo code kindly listed:

if year modulo 400 is 0 then
else if year modulo 100 is 0 then
else if year modulo 4 is 0 then

Now I know that the year of 1900 (and 1800, 1700, …) was not a leap year:

$ cal 2 1900
   February 1900
Su Mo Tu We Th Fr Sa
             1  2  3
 4  5  6  7  8  9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28

Furthermore, with some quick search on the command cal, I found a more surprising fact. In the year 1752, there were no such days as September 3, 4, … till 13. At least the command told me so:

$ cal 9 1752
   September 1752
Su Mo Tu We Th Fr Sa
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

(You can read more about the history here.)

Now, back to the title of this post.

Actually, I have written a stupid cal. (Not in 15 minutes, sorry.) It takes month and year as the arguments, and outputs a calendar similar to cal. It does not take care of the years before 1970, and has no any other fancy features.

Why do I bother putting a stupid program online? Because I am so shameful about it. And I feel that I have to accept it and do something to force me grow.

By attacking problems continuously, I will learn and hopefully be able to write less shameful code.

Equality of Active Record Objects

By on

There was once a caching problem in a project I am working on.

The hash of some posts is used as cache keys. According to the document, I expect Object#hash will return different numbers if any attribute changes. But the cache just won’t refresh.

To figure out that problem, let’s start with two posts (in rails console):

[1] pry(main)> p1 = Post.first
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" LIMIT 1
=> #<Post id: 4, title: "Hello", content: "World.", created_at: "2012-05-20 08:38:46", updated_at: "2012-05-20 08:38:46">
[2] pry(main)> p2 = Post.first
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" LIMIT 1
=> #<Post id: 4, title: "Hello", content: "World.", created_at: "2012-05-20 08:38:46", updated_at: "2012-05-20 08:38:46">

They are the same post:

[3] pry(main)> p1 == p2
=> true

If we change an attribute of p1:

[4] pry(main)> p1.title = "hi"
=> "hi"

Then p1 still equals to p2:

[5] pry(main)> p1 == p2
=> true

They also have the same hash:

[6] pry(main)> p1.hash == p2.hash
=> true

But, why?

Actually, the hash of p1 is the hash of

[7] pry(main)> p1.hash
=> -2154204912980276923
[8] pry(main)>
=> -2154204912980276923

We can confirm that from activerecord/lib/active_record/core.rb:

def ==(comparison_object)
  super ||
    comparison_object.instance_of?(self.class) &&
    id.present? && == id
alias :eql? :==

def hash

That is, when we compare two Active Record objects, we are comparing their ids.

For a cache key, you may consider using cache_key method:

[9] pry(main)> p1.cache_key
=> "posts/5-20120523014730"

It returns a string, which contains the model name, id, and updated_at timestamp of the record.

A New Design

By on

Yesterday I was kind of fed up with the unpleasant design of my blog, and decided to do something about that.

An attractive solution is migrating to Octopress. But that way I could no longer rely on Github to build and host the pages for me.

Then I found Jekyll-Bootstrap, a simple yet powerful framework. Since it does not include any plugins, pages can still be built and hosted on Github. I was so excited and definitely tried it, but was not fully satisfied with its DOM structure.

Of course I can customize JB to fit my needs, but suddenly I realized what I really need is just a new design. Although I am not a designer, I can utilize my newly learned knowledge of CSS and make it look better. And this is the result.

Please feel free to give me any feedback or suggestions about this site. I’d be glad to hear your thoughts!

Version Control by Example

By on

This morning after I woke up, my dad told me that my book has arrived.

“I opened the envelope because it was somewhat damp.” he explains.

Damn postal service. But what book?

The envelope from Chicaco almost answered my question. Yes, the book lying next to the envelope is Eric Sink’s Version Control by Example.

I requested a free copy of the book, and totally forgot about that. I didn’t expect such a nice book would be mailed overseas to my house, for free. But now the book is on my desk.

Thank you, Eric!

Blogging like a hacker

By on

This is the very first post.

Ever since reading “Blogging Like a Hacker” long ago, I’ve been wanting to own a similar site. No worries on database things. Writing articles in plain text feels me good.

I also considered nanoc, since it sounds so powerful. But I chose to learn the simpler Jekyll first.

After setting up this site, I found the existence of Octopress, a helpful framework based on Jekyll. It features a semantic HTML5 template, Disqus Comments support, beautiful syntax highlightings, and many more. Using it would surely make the building process easier. I wish I could know this earlier, but I also learned a lot through building the site from scratch.

There’s still a lot more to do, but right now I’d like to call it a day.