SQLite

Check-in [718ead55]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:While PRAGMA full_column_names is off (the default) do not make changes to the short_column_names pragma when computing the column names for subqueries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | literal-column-names
Files: files | file ages | folders
SHA3-256: 718ead555b09892f3cbaddb1775ede4acfa4a2986d69f15da1270c433e5dd578
User & Date: drh 2019-01-24 16:51:06
Original Comment: While PRAGME full_column_names is off (the default) do not make changes to the short_column_names pragma when computing the column names for subqueries.
About

Ticket [de3403bf5ae] describes a three-step algorithm for determining column names, here repeated and expanded:

  1. If there is an AS clause, then always use the right-hand side of the AS as the column name. No exceptions.
  1. If the expression is a reference to a column of a table in the FROM clause, then use that column as the name of the result set column. If the connection is configured with PRAGMA full_column_names=ON setting, then the result-set column name is "TABLE.COLUMN". Otherwise if the connection is configured with PRAGMA short_column_names=ON then the result-set column name is just "COLUMN". Skip this step if the connection is configured with both PRAGMA short_column_names=OFF and PRAGMA full_column_names=OFF.
  1. Use the text of the expression as it appears in the SELECT statement as the column name. So if the expression is "a+b" then the result-set column name will be "a+b".

The default column-name configuration is

  • PRAGMA short_column_names=ON;
  • PRAGMA full_column_names=OFF;

When computing the names of columns is a subquery in the FROM clause, the default column-name configuration is always used, regardless of how the connection is otherwise configured.

The above is how column names where computed beginning with check-in [be0e24a0293f31b8] until now. This check-in introduces one very simple change:

  • The configured column-name setup is also used to compute the names of columns in FROM-clause subqueries as long as the full_column_names setting is OFF. The default column-name configuration is only used if full_column_names is ON.

Formerly, the default column-name configuration would be used to compute the names of FROM-clause subqueries regardless of the application-defined setting.

Steps (1) and (3) above are always used when computing column names. This check-in and the PRAGMAs only affect step (2). There are three possibilities for step (2):

  1. Use a name of the form "COLUMN"
  2. Use a name of the form "TABLE.COLUMN"
  3. Skip step (2) entirely.

The following table shows how the PRAGMAs affect step (2):

full_column_names short_column_names Step (2) for result-set columns Step (2) for the column names of FROM-clause subqueries
ON Don't care B A
OFF ON A A
OFF OFF C C

This check-in changes the bahavior of the square in the lower right (the light blue box) from "A" to "C". Since the default column-name configuration shown in in middle row is used by 99.9% of all applications, this should should have minimal impact. Indeed, all rows other than the middle row are deprecated, so applications should not be using any rows other than the middle row anyhow.

Context
2019-01-24
16:51
While PRAGMA full_column_names is off (the default) do not make changes to the short_column_names pragma when computing the column names for subqueries. (Leaf check-in: 718ead55 user: drh tags: literal-column-names)
16:07
Omit deprecated PRAGMAs when compiling with the -DSQLITE_OMIT_DEPRECATED option. (check-in: 008112bc user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

2082
2083
2084
2085
2086
2087
2088

2089
2090

2091
2092
2093
2094
2095
2096
2097
*/
Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){
  Table *pTab;
  sqlite3 *db = pParse->db;
  u64 savedFlags;

  savedFlags = db->flags;

  db->flags &= ~(u64)SQLITE_FullColNames;
  db->flags |= SQLITE_ShortColNames;

  sqlite3SelectPrep(pParse, pSelect, 0);
  db->flags = savedFlags;
  if( pParse->nErr ) return 0;
  while( pSelect->pPrior ) pSelect = pSelect->pPrior;
  pTab = sqlite3DbMallocZero(db, sizeof(Table) );
  if( pTab==0 ){
    return 0;







>
|
|
>







2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
*/
Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){
  Table *pTab;
  sqlite3 *db = pParse->db;
  u64 savedFlags;

  savedFlags = db->flags;
  if( (db->flags & SQLITE_FullColNames)!=0 ){
    db->flags &= ~(u64)SQLITE_FullColNames;
    db->flags |= SQLITE_ShortColNames;
  }
  sqlite3SelectPrep(pParse, pSelect, 0);
  db->flags = savedFlags;
  if( pParse->nErr ) return 0;
  while( pSelect->pPrior ) pSelect = pSelect->pPrior;
  pTab = sqlite3DbMallocZero(db, sizeof(Table) );
  if( pTab==0 ){
    return 0;