Domon

Null-safe Comparison in SQL

2018-01-30T00:00:00+00:00

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.

CREATE TABLE tags (
  id           BIGINT NOT NULL AUTO_INCREMENT,
  name         VARCHAR(20),
  tag_group_id BIGINT,

  PRIMARY KEY (id)
);

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

2013-12-25T00:00:00+00:00

To make a class method private, the intuitive way is to put private before the method definition like the following.

class Foo

  private

  def self.bar
    "bar"
  end
end

But that does not seem to be sufficient, unfortunately.

The method is still public:

pry(main)> Foo.bar
=> "bar" 

Actually, to make it private, we have to call private_class_method like the following.

class Foo

  def self.bar
    "bar"
  end
  private_class_method :bar

end

We can confirm that it works by calling the class method in a Ruby console.

pry(main)> Foo.bar
NoMethodError: private method `bar' called for Foo:Class

Why is that?

Before we jump into the explanation, please take a look at the following code, which is another way to make a class method private.

class Foo
  class << self

    private

    def bar
      "bar"
    end
  end
end

In the example above, the singleton class is opened and the method bar is defined as a private instance method on it.

The syntax looks different but it works as same as the previous example.

pry(main)> Foo.bar
NoMethodError: private method `bar' called for Foo:Class

In Ruby, as we know, classes are just objects. When we define a “class method” on Foo, we are just defining an instance method on the singleton class of the Foo object.

pry(main)> Foo.singleton_class.private_instance_methods(false)
=> [:bar]

And private is not a special keyword but a method on Module. It is available on Class because Class.is_a? Module.

When private gets called, it sets the visibility for subsequently methods defined on the current object to private.

Therefore, to set the visibility of methods defined on the singleton class of the current object, we need another method. That is where Module#private_class_method comes in.

To summarize, there are two styles of syntax to create a private class method bar on a class Foo.

Define the method with the syntax def self.bar and then private_class_method :bar to make it private.
Or open the singleton class of Foo and define a private method on the singleton class.

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

When Your Ctrl-C Gets Trapped

2013-07-14T00:00:00+00:00

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.'
  end
end

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

2012-12-12T00:00:00+00:00

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
                   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 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
   is_leap_year
else if year modulo 100 is 0 then
   not_leap_year
else if year modulo 4 is 0 then
   is_leap_year
else
   not_leap_year

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

2012-05-25T00:00:00+00:00

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 p1.id:

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

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

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

def hash
  id.hash
end

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

2012-04-22T00:00:00+00:00

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

2011-09-28T00:00:00+00:00

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

2011-06-30T00:00:00+00:00

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.