CODEWITHSUNDEEP
iossqliteios11
Rob
Rob

Reputation: 437622

SQLite column names for SQL with subselect statements now prefix column name with table name

In iOS 11, if you use sqlite3_column_name with SQL with subselect statements, it now returns the column name with the table prefix, whereas iOS 10 does not.

E.g. consider this SQL:

SELECT f.foo_value, b.bar_value
  FROM foo as f LEFT JOIN
    (SELECT * FROM bar) AS b ON f.foo_id = b.foo_id

If you then retrieve the column names with sqlite3_column_name (note this is Objective-C snippet, but this is a SQLite issue, not unique to Objective-C or Swift):

const char *name1 = sqlite3_column_name(statement, 0);
const char *name2 = sqlite3_column_name(statement, 1);

NSLog(@"SQLite version: %s", sqlite3_version);
NSLog(@"name of column 0: %s", name1);
NSLog(@"name of column 1: %s", name2);

In iOS 11.1, this reports:

SQLite version: 3.19.3
name of column 0: f.foo_value
name of column 1: b.bar_value

In iOS 10.1, this reports:

SQLite version: 3.14.0
name of column 0: foo_value
name of column 1: bar_value

Why?

BTW, this issue appears to only manifest itself when the SQL contains subselect statements.

Upvotes: 2

Views: 484

Answers (1)

Rob
Rob

Reputation: 437622

As the SQLite documentation for sqlite3_column_name says:

The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

So, if you want to use the result of sqlite3_column_name for anything other than informational purposes, you really should use the AS clause in your SQL, e.g.

SELECT f.foo_value AS foo_value, b.bar_value AS bar_value
  FROM ...

As Gwendal Roué noted, this is a known issue introduced in the version of SQLite included with iOS 11:

This change has been introduced in SQLite 3.19.0. It is present in SQLite 3.19.3 shipped with iOS 11. It has been reverted in SQLite 3.20.0.

For more information, see:

Upvotes: 1

Related Questions