From the changelog of sqlite version 3.6.0 beta:
"The result column names generated for compound subqueries have been simplified to show only the name of the column of the original table and omit the table name. This makes SQLite operate more like other SQL database engines."

This might be where I was seeing inconsistent behaviour where subquery column name return naming