Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
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: |
718ead555b09892f3cbaddb1775ede4a |
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. |
Ticket [de3403bf5ae] describes a three-step algorithm for determining column names, here repeated and expanded:
- If there is an AS clause, then always use the right-hand side of the AS as the column name. No exceptions.
- 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.
- 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):
- Use a name of the form "COLUMN"
- Use a name of the form "TABLE.COLUMN"
- 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.
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 to src/select.c.
︙ | ︙ | |||
2082 2083 2084 2085 2086 2087 2088 | */ Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){ Table *pTab; sqlite3 *db = pParse->db; u64 savedFlags; savedFlags = db->flags; | > | | > | 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; |
︙ | ︙ |