A Beginner’s Guide to the True Order of SQL Operations


The SQL language is very intuitive. Until it isn’t.

Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto completion options to offer, because as long as you don’t specify the FROM clause, there are no tables in scope (yet):

-- Don't you wish this would be completed to first_name?
SELECT first_na...

-- Aaah, now it works:
SELECT first_na...
FROM customer

These things are weird, because the lexical order of operations does not match the logical order of operations. We humans may sometimes (often) intuitively understand this ordering difference. E.g. we know that we’re about to select from the customer table. But the IDE doesn’t know this.

GROUP BY contributes the most confusion

When a junior developer / SQL beginner starts working with SQL, quite quickly, they will find out about aggregation and GROUP BY. And they’ll quickly write things like:

SELECT count(*)
FROM customer

Yay, we have 200 customers!

And then:

SELECT count(*)
FROM customer
WHERE first_name = 'Steve'

Wow, 90 of them are called Steve! Interesting. Let’s find out how many we have per name…

SELECT first_name, count(*)
FROM customer
GROUP BY first_name

Ahaa!

FIRST_NAME   COUNT
------------------
Steve        90
Jane         80
Joe          20
Janet        10

Very nice. But are they all the same? Let’s check out the last name, too

SELECT first_name, last_name, count(*)
FROM customer
GROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jeez, what does it mean? (note, unfortunately, MySQL users that do not use the STRICT mode will still get a result here with arbitrary last names!, so a new MySQL user won’t understand their mistake)

How do you easily explain this to a SQL newbie? It seems obvious to “pros”, but is it really obvious? Is it obvious enough that you can explain it easily to a junior? Think about it. Why are each one of these statements semantically correct or wrong?

-- Wrong
SELECT first_name, count(*)
FROM customer
WHERE count(*) > 1
GROUP BY first_name

-- Correct
SELECT first_name, count(*)
FROM customer
GROUP BY first_name
HAVING count(*) > 1

-- Correct
SELECT first_name, count(*)
FROM customer
GROUP BY first_name
ORDER BY count(*) DESC

-- Wrong
SELECT first_name, last_name, count(*)
FROM customer
GROUP BY first_name

-- Correct
SELECT first_name, MAX(last_name), count(*)
FROM customer
GROUP BY first_name

-- Wrong
SELECT first_name || ' ' || last_name, count(*)
FROM customer
GROUP BY first_name

-- Correct
SELECT first_name || ' ' || MAX(last_name), count(*)
FROM customer
GROUP BY first_name

-- Correct
SELECT MAX(first_name || ' ' || last_name), count(*)
FROM customer
GROUP BY first_name

The problem is syntax related

The SQL syntax works in a similar way like the English language. It is a command. We start commands with verbs. The verb is SELECT (or INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Unfortunately, human language is incredibly ill-suited for the much more formal world of programming. While it offers some consolation to new users (possibly non-programmers) who are absolute beginners, it just makes stuff hard for everyone else. All the different SQL clauses have extremely complex interdependencies. For instance:

  • In the presence of a GROUP BY clause, only expressions built from GROUP BY expressions (or functional dependencies thereof), or aggregate functions can be used in HAVING, SELECT, and ORDER BY clauses.
  • For simplicity reasons, let’s not even talk about GROUPING SETS
  • In fact, there are even a few cases in which GROUP BY is implied. E.g. if you write a “naked” HAVING clause
  • A single aggregate function in the SELECT clause (in the absence of GROUP BY) will force aggregation into a single row
  • In fact, this can also be implied by putting that aggregate function in ORDER BY (for whatever reason)
  • You can ORDER BY quite a few expressions that reference any columns from the FROM clause without SELECTing them. But that’s no longer true if you write SELECT DISTINCT

The list is endless. If you’re interested, you can read the SQL standard documents and check out how many weird and complicated inter-dependencies there exist between the many clauses of the SELECT statement.

Can this ever be understood?

Luckily, yes! There’s a simple trick, which I’m always explaining to the delegates that visit my SQL Masterclass. The lexical (syntactical) order of SQL operations (clauses) does not correspond at all to the logical order of operations (although, sometimes, they do coincidentally). Thanks to modern optimisers, the order also doesn’t correspond to the actual order of operations, so we really have: syntactical -> logical -> actual order, but let’s leave that aside for now.

The logical order of operations is the following (for “simplicity” I’m leaving out vendor specific things like CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT and all the others):

  • FROM: This is actually the first thing that happens, logically. Before anything else, we’re loading all the rows from all the tables and join them. Before you scream and get mad: Again, this is what happens first logically, not actually. The optimiser will very probably not do this operation first, that would be silly, but access some index based on the WHERE clause. But again, logically, this happens first. Also: all the JOIN clauses are actually part of this FROM clause. JOIN is an operator in relational algebra. Just like + and - are operators in arithmetics. It is not an independent clause, like SELECT or FROM
  • WHERE: Once we have loaded all the rows from the tables above, we can now throw them away again using WHERE
  • GROUP BY: If you want, you can take the rows that remain after WHERE and put them in groups or buckets, where each group contains the same value for the GROUP BY expression (and all the other rows are put in a list for that group). In Java, you would get something like: Map<String, List<Row>>. If you do specify a GROUP BY clause, then your actual rows contain only the group columns, no longer the remaining columns, which are now in that list. Those columns in the list are only visible to aggregate functions that can operate upon that list. See below.
  • aggregations: This is important to understand. No matter where you put your aggregate function syntactically (i.e. in the SELECT clause, or in the ORDER BY clause), this here is the step where aggregate functions are calculated. Right after GROUP BY. (remember: logically. Clever databases may have calculated them before, actually). This explains why you cannot put an aggregate function in the WHERE clause, because its value cannot be accessed yet. The WHERE clause logically happens before the aggregation step. Aggregate functions can access columns that you have put in “this list” for each group, above. After aggregation, “this list” will disappear and no longer be available. If you don’t have a GROUP BY clause, there will just be one big group without any key, containing all the rows.
  • HAVING: … but now you can access aggregation function values. For instance, you can check that count(*) > 1 in the HAVING clause. Because HAVING is after GROUP BY (or implies GROUP BY), we can no longer access columns or expressions that were not GROUP BY columns.
  • WINDOW: If you’re using the awesome window function feature, this is the step where they’re all calculated. Only now. And the cool thing is, because we have already calculated (logically!) all the aggregate functions, we can nest aggregate functions in window functions. It’s thus perfectly fine to write things like sum(count(*)) OVER () or row_number() OVER (ORDER BY count(*)). Window functions being logically calculated only now also explains why you can put them only in the SELECT or ORDER BY clauses. They’re not available to the WHERE clause, which happened before. Note that PostgreSQL and Sybase SQL Anywhere have an actual WINDOW clause!
  • SELECT: Finally. We can now use all the rows that are produced from the above clauses and create new rows / tuples from them using SELECT. We can access all the window functions that we’ve calculated, all the aggregate functions that we’ve calculated, all the grouping columns that we’ve specified, or if we didn’t group/aggregate, we can use all the columns from our FROM clause. Remember: Even if it looks like we’re aggregating stuff inside of SELECT, this has happened long ago, and the sweet sweet count(*) function is nothing more than a reference to the result.
  • DISTINCT: Yes! DISTINCT happens after SELECT, even if it is put before your SELECT column list, syntax-wise. But think about it. It makes perfect sense. How else can we remove distinct rows, if we don’t know all the rows (and their columns) yet?
  • UNION, INTERSECT, EXCEPT: This is a no-brainer. A UNION is an operator that connects two subqueries. Everything we’ve talked about thus far was a subquery. The output of a union is a new query containing the same row types (i.e. same columns) as the first subquery. Usually. Because in wacko Oracle, the penultimate subquery is the right one to define the column name. Oracle database, the syntactic troll 😉
  • ORDER BY: It makes total sense to postpone the decision of ordering a result until the end, because all other operations might use hashmaps, internally, so any intermediate order might be lost again. So we can now order the result. Normally, you can access a lot of rows from the ORDER BY clause, including rows (or expressions) that you did not SELECT. But when you specified DISTINCT, before, you can no longer order by rows / expressions that were not selected. Why? Because the ordering would be quite undefined.
  • OFFSET: Don’t use offset
  • LIMIT, FETCH, TOP: Now, sane databases put the LIMIT (MySQL, PostgreSQL) or FETCH (DB2, Oracle 12c, SQL Server 2012) clause at the very end, syntactically. In the old days, Sybase and SQL Server thought it would be a good idea to have TOP as a keyword in SELECT. As if the correct ordering of SELECT DISTINCT wasn’t already confusing enough.

There, we have it. It makes total sense. And if you ever want to do something that is not in the “right order”, the simplest trick is always to resort to a derived table. E.g. when you want to group on a window function:

-- Doesn't work, cannot put window functions in GROUP BY
SELECT ntile(4) ORDER BY (age) AS bucket, MIN(age), MAX(age)
FROM customer
GROUP BY ntile(4) ORDER BY (age)

-- Works:
SELECT bucket, MIN(age), MAX(age)
FROM (
  SELECT age, ntile(4) ORDER BY (age) AS bucket
  FROM customer
) c
GROUP BY bucket

Why does it work? Because:

  • In the derived table, FROM happens first, and then the WINDOW is calculated, then the bucket is SELECTed.
  • The outer SELECT can now treat the result of this window function calculation like any ordinary table in the FROM clause, then GROUP BY an ordinary column, then aggregate, then SELECT

Let’s review our original examples with an explanation why they work or why they don’t.

-- Wrong: Because aggregate functions are calculated
-- *after* GROUP BY, and WHERE is applied *before* GROUP BY
SELECT first_name, count(*)
FROM customer
WHERE count(*) > 1
GROUP BY first_name

-- logical order         -- available columns after operation
-------------------------------------------------------------
FROM customer            -- customer.*
WHERE ??? > 1            -- customer.* (count not yet available!)
GROUP BY first_name      -- first_name (customer.* for aggs only)
<aggregate> count(*)     -- first_name, count
SELECT first_name, count -- first_name, count



-- Correct: Because aggregate functions are calculated
-- *after* GROUP BY but *before* HAVING, so they're 
-- available to the HAVING clause.
SELECT first_name, count(*)
FROM customer
GROUP BY first_name
HAVING count(*) > 1

-- logical order         -- available columns after operation
-------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
<aggregate> count(*)     -- first_name, count
HAVING count > 1         -- first_name, count
SELECT first_name, count -- first_name, count



-- Correct: Both SELECT and ORDER BY are applied *after*
-- the aggregation step, so aggregate function results are 
-- available
SELECT first_name, count(*)
FROM customer
GROUP BY first_name
ORDER BY count(*) DESC

-- logical order         -- available columns after operation
-------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
<aggregate> count(*)     -- first_name, count
SELECT first_name, count -- first_name, count
ORDER BY count DESC      -- first_name, count



-- Wrong: Because the GROUP BY clause creates groups of
-- first names, and all the remaining customer columns
-- are aggregated into a list, which is only visiblbe to
-- aggregate functions
SELECT first_name, last_name, count(*)
FROM customer
GROUP BY first_name

-- logical order         -- available columns after operation
-----------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
<aggregate> count(*)     -- first_name, count
                         -- first_name, count (last_name removed)
SELECT first_name, ???, count 



-- Correct: Because now, we're using an aggregate function
-- to access one of the columns that have been put into that
-- list of columns that are otherwise no longer available
-- after the GROUP BY clause
SELECT first_name, MAX(last_name), count(*)
FROM customer
GROUP BY first_name

-- logical order         -- available columns after operation
-----------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
                         -- first_name, max, count
<aggregate> MAX(last_name), count(*) 
                         -- first_name, max, count
SELECT first_name, max, count



-- Wrong: Because we still cannot access the last name column
-- which is in that list after the GROUP BY clause.
SELECT first_name || ' ' || last_name, count(*)
FROM customer
GROUP BY first_name

-- logical order         -- available columns after operation
-----------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
<aggregate> count(*)     -- first_name, count
                         -- first_name, count (last_name removed)
SELECT first_name || ' ' || ???, count 




-- Correct: Because we can access the last name column from
-- aggregate functions, which can see that list
SELECT first_name || ' ' || MAX(last_name), count(*)
FROM customer
GROUP BY first_name

-- logical order         -- available columns after operation
-----------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
                         -- first_name, max, count
<aggregate> MAX(last_name), count(*)  
                         -- first_name, max, count (no last_name)
SELECT first_name || ' ' || max, count




-- Correct: Because both GROUP BY columns and aggregated
-- columns are available to aggregate functions
SELECT MAX(first_name || ' ' || last_name), count(*)
FROM customer
GROUP BY first_name

-- logical order         -- available columns after operation
-----------------------------------------------------------------
FROM customer            -- customer.*
GROUP BY first_name      -- first_name (customer.* for aggs only)
                         -- first_name, max, count
<aggregate> MAX(first_name || ' ' || last_name), count(*)
SELECT max, count        -- first_name, max, count

Always think about the logical order of operations

If you’re not a frequent SQL writer, the syntax can indeed be confusing. Especially GROUP BY and aggregations “infect” the rest of the entire SELECT clause, and things get really weird. When confronted with this weirdness, we have two options:

  • Get mad and scream at the SQL language designers
  • Accept our fate, close our eyes, forget about the snytax and remember the logical operations order

I generally recommend the latter, because then things start making a lot more sense, including the beautiful cumulative daily revenue calculation below, which nests the daily revenue (SUM(amount) aggregate function) inside of the cumulative revenue (SUM(...) OVER (...) window function):

SELECT
  payment_date,
  SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenue
FROM payment
GROUP BY payment_date

… because aggregations logically happen before window functions.

Want to learn more? We also have these articles for you to read:

10 Things You Didn’t Know About Java


So, you’ve been working with Java since the very beginning? Remember the days when it was called “Oak”, when OO was still a hot topic, when C++ folks thought that Java had no chance, when Applets were still a thing?

I bet that you didn’t know at least half of the following things. Let’s start this week with some great surprises about the inner workings of Java.

1. There is no such thing as a checked exception

That’s right! The JVM doesn’t know any such thing, only the Java language does.

Today, everyone agrees that checked exceptions were a mistake. As Bruce Eckel said on his closing keynote at GeeCON, Prague, no other language after Java has engaged in using checked exceptions, and even Java 8 does no longer embrace them in the new Streams API (which can actually be a bit of a pain, when your lambdas use IO or JDBC).

Do you want proof that the JVM doesn’t know such a thing? Try the following code:

public class Test {
 
    // No throws clause here
    public static void main(String[] args) {
        doThrow(new SQLException());
    }
 
    static void doThrow(Exception e) {
        Test.<RuntimeException> doThrow0(e);
    }
 
    @SuppressWarnings("unchecked")
    static <E extends Exception> 
    void doThrow0(Exception e) throws E {
        throw (E) e;
    }
}

Not only does this compile, this also actually throws the SQLException, you don’t even need Lombok’s @SneakyThrows for that.

More details about the above can be found in this article here, or here, on Stack Overflow.

2. You can have method overloads differing only in return types

That doesn’t compile, right?

class Test {
    Object x() { return "abc"; }
    String x() { return "123"; }
}

Right. The Java language doesn’t allow for two methods to be “override-equivalent” within the same class, regardless of their potentially differing throws clauses or return types.

But wait a second. Check out the Javadoc of Class.getMethod(String, Class...). It reads:

Note that there may be more than one matching method in a class because while the Java language forbids a class to declare multiple methods with the same signature but different return types, the Java virtual machine does not. This increased flexibility in the virtual machine can be used to implement various language features. For example, covariant returns can be implemented with bridge methods; the bridge method and the method being overridden would have the same signature but different return types.

Wow, yes that makes sense. In fact, that’s pretty much what happens when you write the following:

abstract class Parent<T> {
    abstract T x();
}

class Child extends Parent<String> {
    @Override
    String x() { return "abc"; }
}

Check out the generated byte code in Child:

  // Method descriptor #15 ()Ljava/lang/String;
  // Stack: 1, Locals: 1
  java.lang.String x();
    0  ldc <String "abc"> [16]
    2  areturn
      Line numbers:
        [pc: 0, line: 7]
      Local variable table:
        [pc: 0, pc: 3] local: this index: 0 type: Child
  
  // Method descriptor #18 ()Ljava/lang/Object;
  // Stack: 1, Locals: 1
  bridge synthetic java.lang.Object x();
    0  aload_0 [this]
    1  invokevirtual Child.x() : java.lang.String [19]
    4  areturn
      Line numbers:
        [pc: 0, line: 1]

So, T is really just Object in byte code. That’s well understood.

The synthetic bridge method is actually generated by the compiler because the return type of the Parent.x() signature may be expected to Object at certain call sites. Adding generics without such bridge methods would not have been possible in a binary compatible way. So, changing the JVM to allow for this feature was the lesser pain (which also allows covariant overriding as a side-effect…) Clever, huh?

Are you into language specifics and internals? Then find some more very interesting details here.

3. All of these are two-dimensional arrays!

class Test {
    int[][] a()  { return new int[0][]; }
    int[] b() [] { return new int[0][]; }
    int c() [][] { return new int[0][]; }
}

Yes, it’s true. Even if your mental parser might not immediately understand the return type of the above methods, they are all the same! Similar to the following piece of code:

class Test {
    int[][] a = {{}};
    int[] b[] = {{}};
    int c[][] = {{}};
}

You think that’s crazy? Imagine using JSR-308 / Java 8 type annotations on the above. The number of syntactic possibilities explodes!

@Target(ElementType.TYPE_USE)
@interface Crazy {}

class Test {
    @Crazy int[][]  a1 = {{}};
    int @Crazy [][] a2 = {{}};
    int[] @Crazy [] a3 = {{}};

    @Crazy int[] b1[]  = {{}};
    int @Crazy [] b2[] = {{}};
    int[] b3 @Crazy [] = {{}};

    @Crazy int c1[][]  = {{}};
    int c2 @Crazy [][] = {{}};
    int c3[] @Crazy [] = {{}};
}

Type annotations. A device whose mystery is only exceeded by its power

Or in other words:

When I do that one last commit just before my 4 week vacation

When I do that one last commit just before my 4 week vacation

I let the actual exercise of finding a use-case for any of the above to you.

4. You don’t get the conditional expression

So, you thought you knew it all when it comes to using the conditional expression? Let me tell you, you didn’t. Most of you will think that the below two snippets are equivalent:

Object o1 = true ? new Integer(1) : new Double(2.0);

… the same as this?

Object o2;

if (true)
    o2 = new Integer(1);
else
    o2 = new Double(2.0);

Nope. Let’s run a quick test

System.out.println(o1);
System.out.println(o2);

This programme will print:

1.0
1

Yep! The conditional operator will implement numeric type promotion, if “needed”, with a very very very strong set of quotation marks on that “needed”. Because, would you expect this programme to throw a NullPointerException?

Integer i = new Integer(1);
if (i.equals(1))
    i = null;
Double d = new Double(2.0);
Object o = true ? i : d; // NullPointerException!
System.out.println(o);

More information about the above can be found here.

5. You also don’t get the compound assignment operator

Quirky enough? Let’s consider the following two pieces of code:

i += j;
i = i + j;

Intuitively, they should be equivalent, right? But guess what. They aren’t! The JLS specifies:

A compound assignment expression of the form E1 op= E2 is equivalent to E1 = (T)((E1) op (E2)), where T is the type of E1, except that E1 is evaluated only once.

This is so beautiful, I would like to cite Peter Lawrey‘s answer to this Stack Overflow question:

A good example of this casting is using *= or /=

byte b = 10;
b *= 5.7;
System.out.println(b); // prints 57

or

byte b = 100;
b /= 2.5;
System.out.println(b); // prints 40

or

char ch = '0';
ch *= 1.1;
System.out.println(ch); // prints '4'

or

char ch = 'A';
ch *= 1.5;
System.out.println(ch); // prints 'a'

Now, how incredibly useful is that? I’m going to cast/multiply chars right there in my application. Because, you know…

6. Random integers

Now, this is more of a puzzler. Don’t read the solution yet. See if you can find this one out yourself. When I run the following programme:

for (int i = 0; i < 10; i++) {
  System.out.println((Integer) i);
}

… then “sometimes”, I get the following output:

92
221
45
48
236
183
39
193
33
84

How is that even possible??

.

.

.

.

.

. spoiler… solution ahead…

.

.

.

.

.

OK, the solution is here (https://blog.jooq.org/2013/10/17/add-some-entropy-to-your-jvm/) and has to do with overriding the JDK’s Integer cache via reflection, and then using auto-boxing and auto-unboxing. Don’t do this at home! Or in other words, let’s think about it this way, once more

When I do that one last commit just before my 4 week vacation

When I do that one last commit just before my 4 week vacation

7. GOTO

This is one of my favourite. Java has GOTO! Type it…

int goto = 1;

This will result in:

Test.java:44: error: <identifier> expected
    int goto = 1;
       ^

This is because goto is an unused keyword, just in case…

But that’s not the exciting part. The exciting part is that you can actually implement goto with break, continue and labelled blocks:

Jumping forward

label: {
  // do stuff
  if (check) break label;
  // do more stuff
}

In bytecode:

2  iload_1 [check]
3  ifeq 6          // Jumping forward
6  ..

Jumping backward

label: do {
  // do stuff
  if (check) continue label;
  // do more stuff
  break label;
} while(true);

In bytecode:

 2  iload_1 [check]
 3  ifeq 9
 6  goto 2          // Jumping backward
 9  ..

8. Java has type aliases

In other languages (e.g. Ceylon), we can define type aliases very easily:

interface People => Set<Person>;

A People type constructed in such a way can then be used interchangably with Set<Person>:

People?      p1 = null;
Set<Person>? p2 = p1;
People?      p3 = p2;

In Java, we can’t define type aliases at a top level. But we can do so for the scope of a class, or a method. Let’s consider that we’re unhappy with the namings of Integer, Long etc, we want shorter names: I and L. Easy:

class Test<I extends Integer> {
    <L extends Long> void x(I i, L l) {
        System.out.println(
            i.intValue() + ", " + 
            l.longValue()
        );
    }
}

In the above programme, Integer is “aliased” to I for the scope of the Test class, whereas Long is “aliased” to L for the scope of the x() method. We can then call the above method like this:

new Test().x(1, 2L);

This technique is of course not to be taken seriously. In this case, Integer and Long are both final types, which means that the types I and L are effectively aliases (almost. assignment-compatibility only goes one way). If we had used non-final types (e.g. Object), then we’d be really using ordinary generics.

Enough of these silly tricks. Now for something truly remarkable!

9. Some type relationships are undecidable!

OK, this will now get really funky, so take a cup of coffee and concentrate. Consider the following two types:

// A helper type. You could also just use List
interface Type<T> {}

class C implements Type<Type<? super C>> {}
class D<P> implements Type<Type<? super D<D<P>>>> {}

Now, what do the types C and D even mean?

They are somewhat recursive, in a similar (yet subtly different) way that java.lang.Enum is recursive. Consider:

public abstract class Enum<E extends Enum<E>> { ... }

With the above specification, an actual enum implementation is just mere syntactic sugar:

// This
enum MyEnum {}

// Is really just sugar for this
class MyEnum extends Enum<MyEnum> { ... }

With this in mind, let’s get back to our two types. Does the following compile?

class Test {
    Type<? super C> c = new C();
    Type<? super D<Byte>> d = new D<Byte>();
}

Hard question, and Ross Tate has an answer to it. The question is in fact undecidable:

Is C a subtype of Type<? super C>?

Step 0) C <?: Type<? super C>
Step 1) Type<Type<? super C>> <?: Type (inheritance)
Step 2) C  (checking wildcard ? super C)
Step . . . (cycle forever)

And then:

Is D a subtype of Type<? super D<Byte>>?

Step 0) D<Byte> <?: Type<? super C<Byte>>
Step 1) Type<Type<? super D<D<Byte>>>> <?: Type<? super D<Byte>>
Step 2) D<Byte> <?: Type<? super D<D<Byte>>>
Step 3) Type<type<? super C<C>>> <?: Type<? super C<C>>
Step 4) D<D<Byte>> <?: Type<? super D<D<Byte>>>
Step . . . (expand forever)

Try compiling the above in your Eclipse, it’ll crash! (don’t worry. I’ve filed a bug)

Let this sink in…

Some type relationships in Java are undecidable!

If you’re interested in more details about this peculiar Java quirk, read Ross Tate’s paper “Taming Wildcards in Java’s Type System” (co-authored with Alan Leung and Sorin Lerner), or also our own musings on correlating subtype polymorphism with generic polymorphism

10. Type intersections

Java has a very peculiar feature called type intersections. You can declare a (generic) type that is in fact the intersection of two types. For instance:

class Test<T extends Serializable & Cloneable> {
}

The generic type parameter T that you’re binding to instances of the class Test must implement both Serializable and Cloneable. For instance, String is not a possible bound, but Date is:

// Doesn't compile
Test<String> s = null;

// Compiles
Test<Date> d = null;

This feature has seen reuse in Java 8, where you can now cast types to ad-hoc type intersections. How is this useful? Almost not at all, but if you want to coerce a lambda expression into such a type, there’s no other way. Let’s assume you have this crazy type constraint on your method:

<T extends Runnable & Serializable> void execute(T t) {}

You want a Runnable that is also Serializable just in case you’d like to execute it somewhere else and send it over the wire. Lambdas and serialisation are a bit of a quirk.

Lambdas can be serialised:

You can serialize a lambda expression if its target type and its captured arguments are serializable

But even if that’s true, they do not automatically implement the Serializable marker interface. To coerce them to that type, you must cast. But when you cast only to Serializable

execute((Serializable) (() -> {}));

… then the lambda will no longer be Runnable.

Egh…

So…

Cast it to both types:

execute((Runnable & Serializable) (() -> {}));

Conclusion

I usually say this only about SQL, but it’s about time to conclude an article with the following:

Java is a device whose mystery is only exceeded by its power

Found this article interesting?

How about this one: 10 Subtle Best Practices when Coding Java

Top 10 Ceylon Language Features I Wish We Had In Java


What does one do when Hibernate is “finished” and feature complete and one needs new challenges? Right. One creates a new JVM language called Ceylon.

On November 12, 2013, Ceylon 1.0.0 was finally released and we congratulate the whole team at Red Hat for their achievements in what looks like a very promising new JVM language. While it will be a slight challenge for Ceylon to compete with Scala, there are lots of very interesting features that distinguish it.

In fact, this language has so many interesting features, it’ll be hard to write up a blog post about the 10 most interesting ones. Which ones to choose? On Google Plus, I’ve had a short chat with Gavin King who also brought us Hibernate, Ross Tate who is also involved with JetBrains’ Kotlin, and Lukas Rytz who was a PhD student and committer for EPFL’s Scala and now works at Google Dart. I wanted those language Uberdesigners to help me find the 10 most thrilling language features that they have and we Java developers don’t. Now I have 20 interesting ones. I’ll certainly write a follow-up post to this one.

I have observed Gavin King and the other guys to be very enthusiastic and knowledgeable. I’ve already had this impression before when I first heard about Ceylon from Stéphane Épardaud at the JUGS in Berne, Switzerland in February 2013, another one of RedHat’s passionate engineers (see his presentation’s slides here).

Anyway, enough of the who’s who. Here’s our personal Top 10 List of Ceylon Language Features I Wish We Had In Java:

1. Modules

In Java, Jigsaw has been postponed about 34 times and we’re only now closing in on Java 8 GA! Yes, we have OSGi and Maven, and both work very well to manage dependencies at runtime (OSGi) or at compile-time (Maven). But compare this black magic Maven/OSGi configuration using Apache Felix

<plugin>
  <groupId>org.apache.felix</groupId>
  <artifactId>maven-bundle-plugin</artifactId>
  <version>2.1.0</version>
  <extensions>true</extensions>
  <executions>
    <execution>
      <id>bundle-manifest</id>
      <phase>process-classes</phase>
      <goals>
        <goal>manifest</goal>
      </goals>
    </execution>
  </executions>
  <configuration>
    <supportedProjectTypes>
      <supportedProjectType>
        jar
      </supportedProjectType>
    </supportedProjectTypes>
    <instructions>
      <Bundle-SymbolicName>
        org.jooq
      </Bundle-SymbolicName>
      <Export-Package>*</Export-Package>
      <Import-Package>
        javax.persistence;resolution:=optional,
        org.apache.log4j;resolution:=optional,
        *
      </Import-Package>
      <_versionpolicy>
        [$(version;==;$(@)),$(version;+;$(@)))
      </_versionpolicy>
    </instructions>
  </configuration>
</plugin>

… with this one by Ceylon:

"The second best ever ORM solution!"
license "http://www.gnu.org/licenses/lgpl.html"
module org.hibernate "3.0.0.beta" {
    import ceylon.collection "1.0.0";
    import java.base "7";
    shared import java.jdbc "7";
}

Finally, things can be controlled on a jar-level, including visibility of packages. With only few lines of code. Please, Java, integrate Ceylon’s powerful module support.

It may be worth mentioning that Fantom is another language with integrated module support. See JodaTime’s Stephen Colebourne’s talk at Devoxx 2011: “Is Fantom Light Years Ahead of Scala?”. Stephen has also brought us ElSql, a new external SQL DSL for Java templating.

2. Sequences

This is the first time I’ve seen this kind of first class support for sequences in a typesafe language. Not only does Ceylon ship with all sorts of collection literals, it also knows types for these constructs. Concretely, you can declare an Iterable as such:

{String+} words = { "hello", "world" };

Notice the notation of the literal. It is of type {String+}, meaning that it contains at least one element. The type is assignment-compatible with {String*}, which represents a possibly empty sequence. Very interesting.

This goes on by supporting array literals as such:

String[] operators = [ "+", "-", "*", "/" ];
String? plus = operators[0];
String[] multiplicative = operators[2..3];

… or tuple literals:

[Float,Float,String] point = [0.0, 0.0, "origin"];

Notice also the range literal 2..3 which allows for extracting sub-arrays from the original array. So much sequence goodness in Ceylon!

Notice also the question mark in String?, which is Ceylon’s way of declaring …

3. Nullable types

While Scala knows the Option type and Haskell knows the Maybe type and Java 8 tries to compete by adding the new, unenforceable Optional type, Ceylon has a very simple notion of something that is nullable. If there’s a question mark behind a type, it’s nullable. Otherwise, it’s not null. Always.

In order to convert a nullable type into a not nullable type, you have to explicitly check:

void hello() {
    String? name = process.arguments.first;
    String greeting;
    if (exists name) {
        greeting = "Hello, ``name``!";
    }
    else {
        greeting = "Hello, World!";
    }
    print(greeting);
}

Notice the exists operator. It defines a new scope within which the name variable is known to be not null, i.e. it is promoted from String? to String. This locally scoped type promotion is commonly referred to as flow-sensitive typing, which has already been observed in the Whiley language, according to Lukas Rytz.

If you omit the exists check, you’d get a compilation error on that string interpolation there. There are also other useful constructs to perform ad-hoc type conversions:

String greeting = "Hello, " + (name else "World");

The else clause acts like a SQL COALESCE() function and can even be chained. Read more about Ceylon’s nullable goodness.

4. Defaulted parameters

OMG, how I wish we had that in Java. Every time we overload methods, we think, why not just support defaulted parameters like PL/SQL, for instance??

void hello(String name="World") {
    print("Hello, ``name``!");
}

I cannot think of a single good reason why languages wouldn’t have named and defaultable parameters like PL/SQL:

-- One of the parameters is optional
CREATE PROCEDURE MY_PROCEDURE (
  P1 IN NUMBER,
  P2 IN VARCHAR2 := 'ABC',
  P3 IN VARCHAR2
);

-- Calling the procedure
MY_PROCEDURE(
  P1 => 1,
  P3 => 'XYZ'
);

So this is one way to circumvent method overloading in most common cases. Method overloading is still tedious when we want to deal with alternative, incompatible types. But not in Ceylon, as Ceylon knows …

5. Union types

OK, this is a bit esoteric. The creators of Ceylon really really wanted to get rid of method overloading, partially because Ceylon also compiles to JavaScript, and JavaScript does not know function overloading. In fact, it is not possible to overload methods in Ceylon at all. To be able to interoperate with Java, however, union types needed to be introduced. A union type String|Integer can be either a String or an Integer. There’s method overloading right there!

void printType(String|Integer|Float val) { ... }
 
printType("hello");
printType(69);
printType(-1.0);

In order to “untangle” the union type, you can again take advantage of flow-sensitive typing for the val parameter by performing type-checks similar to Java’s instanceof

void printType(String|Integer|Float val) {
    switch (val)
    case (is String) { print("String: ``val``"); }
    case (is Integer) { print("Integer: ``val``"); }
    case (is Float) { print("Float: ``val``"); }
}

Within that scope, val is known to the compiler to be of type String, for example. This goes on to allowing crazy stuff like enumerated types where a type can be one or another thing, simultaneously:

abstract class Point()
        of Polar | Cartesian {
    // ...
}

Note that this is very different from multiple inheritance where such a Point would be both Polar and Cartesian. But that’s not all. Ceylon also has …

6. Intersection types

Now, as you may have guessed, that’s the exact inverse of a union type, and this is actually also supported by Java’s generics. In Java, you can write:

class X<E extends Serializable & Comparable<E>> {}

In the above example, X accepts only type parameters that are both Serializable and Comparable. This is much crazier in Ceylon where you can assign values to a locally declared intersection type. And that’s not it! In our chat, Gavin has pointed out this incredible language feature to me, where union / intersection types can interact with flow-sensitive typing to form the following (due for Ceylon 1.2):

value x = X();
//x has type X
if (something) {
    x = Y();
    //x has type Y
}
//x has type X|Y

Makes sense, right? So I asked him, if I will be able to intersect that type again with Z and Gavin said, yes! The following can be done:

value x = X();
//x has type X
if (something) {
    x = Y();
    //x has type Y
}
//x has type X|Y
if (is Z x) {
    //x has type <X|Y>&Z
}

And this goes on, because type intersections also interact with generics in a very interesting way. Under certain circumstances, X<A>&X<B> can be the same as X<A&B>. In other words, intersections (and unions) are distributive with generics, just like additions are with multiplications (in an informal understanding of “just like”). If you’re willing to delve into the language spec for this, see §3.7.2 Principal instantiation inheritance.

Now, union and intersection types can get quite nasty und hard to reuse. This is why Ceylon has …

7. Type aliases

Is there any other programming language that ever thought of this awesome feature?? This is so useful, even if you’re not supporting union and/or intersection types. Think about Java’s generics. With the advent of generics, people started writing stuff like:

Map<String, List<Map<Integer, String>>> map = // ...

Two things can be said:

  • Generics are extremely useful to the Java libraries
  • Generics become extremely verbose when doing the above

Here’s where type aliases come into play. Check out this example:

interface People => Set<Person>;

The point here is that even if some verbose types are reused very often, you don’t often want to create an explicit subtype for the above. In other words, you don’t want to abuse subtype polymorphism as a shortcut to “simplify” generic polymorphism.

Think of aliases as an expandable macro, which is mutually assignment-compatible. In other words, you can write:

People?      p1 = null;
Set<Person>? p2 = p1;
People?      p3 = p2;

So as the term “alias” suggests, you’re not creating a new type. You’re just giving a complex type a simpler name. But even better than type aliasing is …

8. Type inference

Many other languages have this and so does Java to a certain extent, at least as far as generics are involved. Java 8 goes one step further in allowing type inference with generics. But Java is far away from what languages like Scala or Ceylon can do with local variables:

interface Foo {}
interface Bar {}
object foobar satisfies Foo&Bar {}
//inferred type Basic&Foo&Bar
value fb = foobar; 
//inferred type {Basic&Foo&Bar+}
value fbs = { foobar, foobar };

So, this example shows a lot of features combined, including type constraints, sequence types, union types. With such a rich type system it is very important to support this level of type inference where a value keyword indicates that you don’t want to (or you cannot) explicitly declare a type. This, I’d really love to see in Java 9!

Read more about Ceylon’s awesome type inference capabilities.

9. Declaration-site variance

Now, this feature might be a bit harder to understand, as Java’s generics are already quite difficult to understand. I’ve recently read a very interesting paper by Ross Tate, Alan Leung and Sorin Lerner about the challenges brought to Java generics through wildcards: Taming Wildcards in Java’s Type System. Generics are still a very active research topic neither researchers nor language designers completely agree on whether use-site variance (as in Java) or declaration-site variance (as in C#, Scala, or Ceylon) is really better for mainstream programmers. Older languages talking about variance are Eiffel and OCaml.

Microsoft has introduced declaration-site variance in C#. I’ll cite the example from Wikipedia, which is very easy to understand. In C#, the IEnumerator interface has a covariant generic type parameter:

interface IEnumerator<out T>
{
    T Current { get; }
    bool MoveNext();
}

This simply means that the following will work:

IEnumerator<Cat> cats = ...
IEnumerator<Animal> animals = cats;

This is quite different from Java’s use-site variance, where the above wouldn’t compile, but the following would:

Iterator<Cat> cats = ...
Iterator<? extends Animal> animals = cats;

The main reason for declaration-site covariance is the simple fact that verbosity is greatly reduced at the use-site. Wildcards are a major pain to Java developers and they lead to numerous Stack Overflow questions as this one, which is about locally scoped wild-cards:

// Given this interface:
public interface X<E> {
    E get();
    E set(E e);
}

// This does not compile:
public void foo(X<?> x) {
    x.set(x.get());
}

As can be seen in the Ceylon language tour, Ceylon generics support declaration-site variance, just like C# and Scala. It will be interesting to see how these things evolve, as both types of variance support have their pros and cons, while at the same time, Ross Tate advocates mixed-site variance, which would really be a great addition for the Java language!

Now this was a bit complex, so let’s have a look at a simpler, yet awesome feature to round things up …

10. Functions and methods

One of the main things outlined by Stéphane Épardaud was the fact that the Ceylon language is a very regular language. This is particularly apparent when considering how Ceylon treats functions (and methods, which are type member functions). I can put a function everywhere. Consider this example:

Integer f1() => 1;
class C() {
    shared Integer f2() {
        Integer f3() => 2;
        return f3();
    }
}

print(f1());
print(C().f2());

In the above example,

  • f1() is a package-level function (much like a “global” static function in Java)
  • f2() is a regular method on the C class
  • f3() is a local function within the f2() method

With Java 8’s support for lambda expressions, these things get a bit better, but isn’t it awesome to be able to declare functions anywhere, in almost the same syntax?

Conclusion: Play around with Ceylon

That’s it for now. We might be publishing a follow-up article about the more esoteric language features in Ceylon, some time soon. In any case, you can download this interesting JVM language for free with first-class IDE support in Eclipse. You can also visit the Ceylon documentation website and have their website compile Ceylon code into JavaScript for execution in your browser.

Visit the Community and interact with the language designers from RedHat and Serli, and when you’re done, share this post on our jOOQ blog and help the JCP recognise that this wonderful language has a couple of very interesting features to put on the Java 9 or 10 roadmap!

Java, if this were a better world


Just a little dreaming about a better world, where some old blunders in the Java platform would’ve been corrected and some awesome missing features would’ve been implemented. Don’t get me wrong. I think Java is awesome. But it still has some issues, like any other platform.

Without any particular order, without claiming to be anything near exhaustive, and most importantly, without claiming to be well thought-through and entirely correct, I wish for these things:

Serialisability

Within an object, serialisability is the default. If you don’t want a member to be serialisable, you mark it “transient”. Why on earth do we have to add this silly marker interface “Serializable” to all of our classes?

All objects should be Serializable by default. Non-serialisability should be the “feature” that is marked explicitly

Of course, serialisability itself has a lot of weird details that I won’t go into, here

Cloning

Since all objects should be Serializable by default,

All objects should also be Cloneable by default. Non-cloneability should be the “feature” that is marked explicitly

Besides, shallow cloning is hardly ever useful. Hence

All objects should deep-clone themselves by default. Shallow cloning can be implemented explicitly

Note, the clone method should be some native method in java.lang.System or some other utility. It shouldn’t be on java.lang.Object, allowing client code to implement their proper interpretation of cloning, without any accidental name clashes.

Alternatively, similar private callback methods could be implemented, the same way that this is done for serialisation, if cloning should be customised.

Unsigned numbers

Why isn’t this part of Java?

There should be an unsigned version of all integer primitives, as well as java.lang.Number wrappers

Primitives

Primitives are a pain to support in APIs. int and Integer should be the same from a syntax perspective. int[] and Integer[] should be, too

Primitives and their wrappers should be better integrated in the language and in the JVM

This one is probably not really resolveable without giving up on the performance advantage that true primitives offer. See Scala…

Properties

Getters and setters aren’t really state-of-the-art.

Properties should be supported more formally

See also a recent article and its comments on this blog:

https://blog.jooq.org/2013/01/12/bloated-javabeans-part-ii-or-dont-add-getters-to-your-api/

Collections

The collection API should be better integrated with the language. Like in many other languages, it should be possible to dereference collection contents using square brackets and curly braces. The JSON syntax would be an obvious choice. It should be possible to write:

// Translates to new ArrayList<>(...);
List<Integer> list = [ 1, 2, 3 ];

// Translates to list.get(0);
Integer value = list[0];

// Translates to list.set(0, 3);
list[0] = 3;

// Translates to list.add(4);
list[] = 4;

// Translates to new LinkedHashMap<>(...);
Map<String, Integer> map = { "A": 1, "B": 2 }; 

// Translates to map.get(0);
Integer value = map["A"]

// Translates to map.put("C", 3);
map["C"] = 3;

ThreadLocal

ThreadLocal can be a nice thing in some contexts. Probably, the concept of ThreadLocal isn’t 100% sound, as it can cause memory leaks. But assuming that there were no problems,

threadlocal should be a keyword, like volatile and transient

If transient deserves to be a keyword, then threadlocal should be, too. This would work as follows:

class Foo {
  threadlocal Integer bar;

  void baz() {
    bar = 1;           // Corresponds to ThreadLocal.set()
    Integer baz = bar; // Corresponds to ThreadLocal.get()
    bar = null;        // Corresponds to ThreadLocal.remove()
  }
}

Of course, such a keyword could be applied to primitives as well

References

References are something weird in Java. They’re implemented as Java objects in the java.lang.ref package, but treated very specially by the JVM and the GC.

Just like for threadlocal, there should be keywords to denote a reference

Of course, with the introduction of generics, there is only little gain in adding such a keyword. But it still feels smelly that some classes are “very special” within the JVM, but not language syntax features.

Reflection

Please! Why on earth does it have to be so verbose?? Why can’t Java (Java-the-language) be much more dynamic? I’m not asking for a Smalltalk-kind of dynamic, but couldn’t reflection be built into the language somehow, as syntactic sugar?

The Java language should allow for a special syntax for reflection

Some pain-easing can be achieved on a library-level, of course. jOOR is one example. There are many others.

Interfaces

Interfaces in Java always feel very weird. Specifically, with Java 8’s extension methods, they start losing their right to exist, as they move closer to abstract classes. Of course, even with Java 8, the main difference is that classes do not allow multiple inheritance. Interfaces do – at least, they allow for multiple inheritance of specification (abstract methods) and behaviour (default methods), not for state.

But they still feel weird, mainly because their syntax diverges from classes, while their features converge. Why did the lambda expert group decide to introduce a default keyword?? If interfaces allow for abstract methods (as today) and concrete methods (defender methods, extension methods), why can’t interfaces have the same syntax as classes? I’ve asked the expert group with no luck:

http://mail.openjdk.java.net/pipermail/lambda-dev/2012-August/005393.html

Still, I’d wish that…

Interface syntax should be exactly the same as class syntax, wherever appropriate

This includes static methods, final methods, private methods, package-private methods, protected methods, etc.

Default visibility

Default visibility shouldn’t be specified by the absence of a private/protected/public keyword. First of all, this absence isn’t dealt with the same way in classes and interfaces. Then, it is not very readable.

Default visibility should be specified by a “package” or “local” or similar keyword

Literals

This would be an awesome addition in everyday work.

There should be list, map, regex, tuple, record, string (improved), range literals

I’ve blogged about this before:

https://blog.jooq.org/2012/06/01/array-list-set-map-tuple-record-literals-in-java/

Some ideas mentioned by Brian Goetz on the lambda-dev mailing list were found here:

http://mail.openjdk.java.net/pipermail/lambda-dev/2012-May/004979.html

#[ 1, 2, 3 ]                          // Array, list, set
#{ "foo" : "bar", "blah" : "wooga" }  // Map literals
#/(\d+)$/                             // Regex
#(a, b)                               // Tuple
#(a: 3, b: 4)                         // Record
#"There are {foo.size()} foos"        // String literal

I’ll add

#(1..10)                              // Range (producing a List)

Final

Methods, attributes, parameters, local variables, they can all be declared as “final”. Immutability is a good thing in many ways, and should be encouraged (I’ll blog about this, soon). Other languages, such as Scala, distinguish the “val” and “var” keywords. In addition to those other languages’ impressive type inference capabilities, in most cases, val is preferred to var. If one wants to express a modifiable variable, they can still use “var”

Final should be the default behaviour for members, parameters and local variables

Override

It is dangerous to accidentally override a method. Other languages have solved this by causing compilation errors on overrides

An override keyword should be introduced to explicitly override a method

Some Java compilers (e.g. the Eclipse compiler) can be configured to emit a warning / error on the absence of the java.lang.Override annotation. However, this really should be a keyword, not an annotation.

Modules

Dependency management is a nightmare in Java. There is one other language, that builds compilation units in terms of modules: Fantom. Stephen Colebourne (the JodaTime guy) is a big fan of Fantom, and has held a speech at Devoxx. He also blogs about Fantom, from time to time:
http://blog.joda.org/search/label/fantom

A compilation unit should be expressed in the form of a “module” / jar file

This would of course make Maven obsolete, as the Java compiler could already handle dependencies much better.

Varargs and generics

Come on. @SafeVarargs?? Of course, this can never be resolved entirely correctly, due to generic type erasure. But still

There should be no generic type erasure

Tuples and Records

I really think that this is something missing from Java

There should be language support for tuples and records

Scala has integrated tuples up to a degree of 22, .NET supports tuples up to a degree of 8. This would be a nice feature in the Java language as well. Specifically, records (or structs) would be a nice thing to have. As mentioned before, there should be literals for tuples and records, too. Something along these lines:

#(a, b)                               // Tuple
#(a: 3, b: 4)                         // Record

Compiler

A compiler API that goes far beyond adding some annotation processing would be nice. I’d love to be able to extend Java-the-language itself. I’d like to embed SQL statements directly into Java code, similar to SQL being embeddable in PL/SQL. Of course, such SQL code would be backed by a library like jOOQ.

The compiler API should allow for arbitrary language extension

Of course, this improved compiler API should be done in a way that auto-completion, syntax highlighting and other features work automatically in IDEs like Eclipse, as the compiler extensions would be able to expose necessary artefacts to IDEs.

OK, I agree, this improvement is a lot of dreaming 🙂

Type inference

If unambiguous, couldn’t type inference just be as powerful as Scala’s? I don’t want to write down the complete type of every local variable.

Scala’s local type inference should be supported

Operator overloading

OK, this is a highly religious topic. Many of you won’t agree. But I just like it

Java should support operator overloading

Some library operations are just better expressed using operators, rather than methods. Think of BigInteger and BigDecimal’s horribly verbose API.

Any other ideas? Add comments!

Of course, lambdas and extension methods are missing and generics are erased. While the latter will never be fixed, the first will be in Java 8. So lets forgive Sun and Oracle for making us wait so long for lambdas

A very refreshing point of view about language design


Have fun:

http://joshondesign.com/2012/03/09/open-letter-language-designers