Why SELECT 0, ... instead of SELECT

Some frameworks do this in order to tell, without any doubt, whether a row from that table was returned.

Consider

  A      B+---+  +---+------+| a |  | a | b    |+---+  +---+------+| 0 |  | 0 |    1 |+---+  +---+------+| 1 |  | 1 | NULL |+---+  +---+------+| 2 |+---+SELECT A.a, B.bFROM ALEFT JOIN BON B.a = A.a  Results+---+------+| a | b    |+---+------+| 0 |    1 |+---+------+| 1 | NULL |+---+------+| 2 | NULL |+---+------+

In this result set, it is not possible to see that a = 1 exists in table B, but a = 2 does not. To get that information, you need to select a non-nullable expression from table b, and the simplest way to do that is to select a simple constant value.

SELECT A.a, B.x, B.bFROM ALEFT JOIN (SELECT 0 AS x, B.a, B.b FROM B) AS BON B.a = A.a  Results+---+------+------+| a | x    | b    |+---+------+------+| 0 |    0 |    1 |+---+------+------+| 1 |    0 | NULL |+---+------+------+| 2 | NULL | NULL |+---+------+------+

There are a lot of situations where these constant values are not strictly required, for example when you have no joins, or when you could choose a non-nullable column from b instead, but they don't cause any harm either, so they can just be included unconditionally.

When I have code to dynamically generate a WHERE clause, I usually start the clause with a:

WHERE 1 = 1

Then the loop to add additional conditions always adds each condition in the same format:

AND x = y

without having to put conditional logic in place to check if this is the first condition or not: "if this is the first condition then start with the WHERE keyword, else add the AND keyword.

So I can imagine a framework doing this for similar reasons. If you start the statement with a SELECT 0 then the code to add subsequent columns can be in a loop without any conditional statements. Just add , colx each time without any conditional checking along the lines of "if this is the first column, don't put a comma before the column name, otherwise do".

Example pseudo code:

String query = "SELECT 0";for (Column col in columnList)    query += ", col";

Only Apple knows … but I see two possibilities:

1. Inserting a dummy column ensures that the actual output columns are numbered beginning with 1, not 0. If some existing interface already assumed one-based numbering, doing it this way in the SQL backend might have been the easiest solution.

2. If you make a query for multiple objects using multiple subqueries, a value like this could be used to determine from which subquery a record originates:

   SELECT 0, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 123UNION ALLSELECT 1, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 456 

   (I don't know if Core Data actually does this.)

Your EXPLAIN output shows that the only difference is (at address 4) that the second program sets the extra column to zero, so there is only a minimal performance difference.