Saturday, April 28, 2012

Insisting ELSE 0 on COUNT aggregate? You might as well substitute your birthday

Some unenlightened SQL user from stackoverflow wanted to do this, note the ELSE 0 part:

select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables

Due to his insistence on the need for ELSE 0 part, I recommended to him that he can substitute his birthday instead on that ELSE part, it "works" the same anyway as the ELSE 0 approach:

select 
count(case when 'blah' = 'bleh' then 1 else 'April 20, 1939' end) 
from information_schema.tables


Of course, both queries above are a crime against humanity, and I cannot let this wrong deed goes unpunished uncorrected, here's the correct query:

Idiomatic MySQL (duality between boolean and integer) :

select sum('blah' = 'bleh') 
from information_schema.tables


Idiomatic Postgresql:

select sum(('blah' = 'bleh')::int) 
from information_schema.tables


SQL Server:

select sum(case when 'blah' = 'bleh' then 1 end) 
from information_schema.tables


We can also use these, but the above works well, free of noise and devoid of Cargo Cult Programming, it's better to use that code. SUM ignores 0 and NULLs, why include them?

select sum(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables;

select sum(case when 'blah' = 'bleh' then 1 else null end) 
from information_schema.tables;


But for the love of our craft, don't do this (the ELSE 0 on COUNT), this is very wrong :
select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables


If you insist so, re-read from the top

Friday, April 27, 2012

MySQL has WITH ROLLUP, PostgreSQL users must be fuming with envy




Data sample:

create table ProductInventory(
  ProductCode varchar(10) not null,
  Location varchar(50) not null
);


insert into ProductInventory(ProductCode,Location) values
('CPU','US'),
('CPU','PH'),
('CPU','PH'),
('KB','PH'),
('KB','US'),
('KB','US'),
('MSE','US'),
('MSE','JP');


MySQL query works :

select ProductCode, 
    SUM(Location = 'US') as UsQty,
    SUM(Location = 'PH') as PhilippinesQty
from ProductInventory
group by ProductCode with rollup

Postgres query doesn't work:

select ProductCode, 
    SUM((Location = 'US')::int) as UsQty,
    SUM((Location = 'PH')::int) as PhilippinesQty
from ProductInventory
group by ProductCode with rollup




Output:
ProductCode UsQty PhilippinesQty
CPU             1       2
KB              2       1
MSE             1       0
                4       3


Allay our worries Postgres users, ROLLUP for Postgres is in the pipeline too: http://wiki.postgresql.org/wiki/Grouping_Sets

Tuesday, April 24, 2012

Javascript's object and associative array duality. A dynamic method invocation feature hiding in plain sight

The duality of Object and associative array in javascript is a neat functionality you can tap anytime

var something = new Object();

something["FirstName"] = "Michael";
something.LastName = "Buen";

something["MiddleName"] = "Ignite";
something.Age = "Forever";

window.alert(something.FirstName);
window.alert(something["LastName"]);

window.alert(something["MiddleName"]);
window.alert(something.Age);

alert("iterate");

for(var x in something) alert(x + ": " + something[x]);
​


With that in mind, dynamic method invocation like this is possible:

window.alert('ok');
window.confirm('ok');

var arr = ["alert", "confirm"];

for(i = 0; i < arr.length; ++i) window[arr[i]]("Nice!");

And since the alert function is available on implied this object, alert can be done in these ways:

this.alert("would work");
this["alert"]("This would work too");
alert("works"); // the usual

There's another language which has these sort of duality, C language. Its arrays are pointers can be accessed in almost the same ways. Though some might argue that this duality is confusing for beginners.

Saturday, April 21, 2012

Detect if a given column has all values set to null

Given this:

create table test(
x char(1) not null,
y char(1)
);

insert into test(x,y) values
('A',null),
('A','E'),
('B', null),
('B', null);


How you would detect that if column x's y column has all values set to null? in this case, the output is B

One might right away write the code like this:

select x
from test
group by x
having sum((y is not null)::int) = count(x);


That logic is needlessly complicated to detect if a given column has all values set to null. Just use MAX


select x
from test
group by x
having max(y) is null;


UPDATE 2012-05-09

This is way much better:

select x
from test
group by x
having every(y is null)

every works only on Postgresql.

every shall stop as soon any of its element didn't satisfy the condition. It's faster than max or sum-count combo approach. every is an alias for bool_and. For MySQL, use bit_and. Postgresql's every is short-circuited

Saturday, April 14, 2012

Java should not aid inconsistencies

One of some leaky abstractions of Java: unboxing and comparison

class Main
{
  public static void main (String[] args) throws java.lang.Exception
  {
      Integer a = 7;
      Integer b = 7;
 
      Integer c = 200;
      Integer d = 200;
      
      // java should not aid comparing variable to literal, 
      // java language designer should make this a compilation error...
      System.out.println(c == 200); // true 
      System.out.println(d == 200); // true
 
      // ...because it gives the wrong impression that you could likewise 
      // do the same comparison on two variables:
      System.out.println(c == d); // false. what!? leaky abstraction that is!
      
 
      System.out.println(a == 7); // true
      System.out.println(b == 7); // true
 
      // this should be false. but it ain't, all numbers from -128 to 127 are cached(aka interning),
      // hence a and b points to the same memory location
      System.out.println(a == b); // true. what!? leaky abstraction that is!
 
           

      // this is way better. all the output are true
      System.out.println("\nBetter\n");

      System.out.println(a.equals(7));
      System.out.println(a.equals(b));
      System.out.println(c.equals(200)); // now we're talking
      System.out.println(c.equals(d));

       
      // and this comparison is infinitely better. 
      // when you want to compare if a is less than or equal to b, just change the '==' operator to '<='
    
      /*

      System.out.println(a.compareTo(7) == 0);
      System.out.println(a.compareTo(b) == 0);
      System.out.println(c.compareTo(200) == 0); // now we're talking
      System.out.println(c.compareTo(d) == 0);

      */
  }
}
Output:
true
true
false
true
true
true

Better

true
true
true
true
Leaky abstraction: http://www.joelonsoftware.com/articles/LeakyAbstractions.html

Sunday, April 8, 2012

Java and C# varargs difference need a mindset shift

Whereas in C#, this will output integer:

using System;
public class Test
{
        public static void Main()
        {
                int a = 7;
                DoStuff(a);
        }
        
        static void DoStuff(object x)
        {       
                Console.WriteLine("object");
        }
        
        static void DoStuff(params int[] x)
        {
                Console.WriteLine("integer");
        }
}

In Java, this will output object:

public class Main
{
        public static void main(String[] args)
        {
                int a = 7;
                DoStuff(a);
        }
        
        static void DoStuff(Object x)
        {
                System.out.println("object");
        }
        
        static void DoStuff(int... x)
        {
                System.out.println("integer");
        }
}

Thursday, April 5, 2012

int and short compatibility problem, not a versioning problem. it's a bad design decision

On my last post about int compatibility problem, I have an impression that the problem lies in the versioning problem.

On C# and C++, following would compile, but not in Java:

class Alien
{
 String invade(short ships) { return "int"; }
}


public class Invader
{
 public static void main(String[] args)
 {
  System.out.println(new Alien().invade(7));
 }
}


If it really is about versioning problem, why Java allows mapping numbers to float?


Java would compile this:

class Alien
{
        String invade(float ships) { return "float"; }
}

public class Invader
{
        public static void main(String[] args)
        {
                System.out.println(new Alien().invade(7));
        }
}

Now if you put Alien class and Invader class to their own file, the mappability of number to float shall have a versioning problem:

Alien.java:
class Alien
{
 String invade(float ships) { return "float"; }
}

Invader.java:
public class Invader
{
    public static void main(String[] args)
    {
        System.out.println(new Alien().invade(7));
    }
}

Compile both files, then run it, output will be:
float

Now try to modify the Alien.java and then re-compile it (note: don't re-compile Invade.java):

class Alien
{
 String invade(float ships) { return "float"; }
 String invade(int ships) { return "int"; }
}

Then re-run Invader (don't recompile it), output will be:
float

Now that's weird, if you read Invade.java source code, you can see that the number 7 could be mapped to overloaded method with int parameter, but since the burned instruction on the call site is bound to call the method with float parameter, the code would still continue to call the method with float parameter, regardless of the component(Alient.class) having a more compatible method, one that accepts int. To coerce the caller(Invader.class) to sync to the more fitting overloaded method, re-compile the Invader class. Then the output will be:

int


Seeing that a number is compatible to a method that accepts float, why not make it compatible to short then too?