/ Check-in [b1d4c42d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:CREATE TABLE ... AS ... uses short names for columns. Ticket #1036. (CVS 2232)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b1d4c42d2be07adda68d31c570ba7cf8b115c3ad
User & Date: drh 2005-01-18 17:20:10
Context
2005-01-18
17:40
Make sure USING and NATURAL work on joins even if the columns are renamed using an AS phrase. Ticket #523. (CVS 2233) check-in: c06add57 user: drh tags: trunk
17:20
CREATE TABLE ... AS ... uses short names for columns. Ticket #1036. (CVS 2232) check-in: b1d4c42d user: drh tags: trunk
16:02
Column names coming back from a SELECT are now just the name of the source column without the "table." prefix. In other words, "PRAGMA short_column_names=ON" is now the default. This makes the names of columns behave more like other SQL engines. The old behavior can be restored by setting "PRAGMA short_column_names=OFF". (CVS 2231) check-in: 9295050a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.180 2005/01/18 04:00:44 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    **     SELECT a+b AS x FROM table WHERE x<10;
    **
    ** In cases like this, replace pExpr with a copy of the expression that
    ** forms the result set entry ("a+b" in the example) and return immediately.
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && pEList!=0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          pExpr->op = TK_AS;
          pExpr->iColumn = j;
          pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.181 2005/01/18 17:20:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
    **     SELECT a+b AS x FROM table WHERE x<10;
    **
    ** In cases like this, replace pExpr with a copy of the expression that
    ** forms the result set entry ("a+b" in the example) and return immediately.
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && pEList!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          pExpr->op = TK_AS;
          pExpr->iColumn = j;
          pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
825
826
827
828
829
830
831
832
833
834





835
836
837

838
839
840
841

842
843
844
845
846
847
848
849
850

851
852

853
854
855














856
857



858
859
860
861
862
863
864
....
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.226 2005/01/18 16:02:40 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0, pCol=aCol; i<pTab->nCol; i++, pCol++){
    Expr *pR;
    char *zType;
    char *zName;





    Expr *p = pEList->a[i].pExpr;
    assert( p->pRight==0 || p->pRight->token.z==0 || p->pRight->token.z[0]!=0 );
    if( (zName = pEList->a[i].zName)!=0 ){

      zName = sqliteStrDup(zName);
    }else if( p->op==TK_DOT 
               && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
      int cnt;

      zName = sqlite3MPrintf("%T", &pR->token);
      for(j=cnt=0; j<i; j++){
        if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
          sqliteFree(zName);
          zName = sqlite3MPrintf("%T_%d", &pR->token, ++cnt);
          j = -1;
        }
      }
    }else if( p->span.z && p->span.z[0] ){

      zName = sqlite3MPrintf("%T", &p->span);
    }else{

      zName = sqlite3MPrintf("column%d", i+1);
    }
    sqlite3Dequote(zName);














    pCol->zName = zName;




    zType = sqliteStrDup(columnType(pParse, pSelect->pSrc ,p));
    pCol->zType = zType;
    pCol->affinity = SQLITE_AFF_NUMERIC;
    if( zType ){
      pCol->affinity = sqlite3AffinityType(zType, strlen(zType));
    }
    pCol->pColl = sqlite3ExprCollSeq(pParse, p);
................................................................................
              pExpr->token.z = 0;
              pExpr->token.n = 0;
              pExpr->token.dyn = 0;
            }else{
              pExpr = pRight;
              pExpr->span = pExpr->token;
            }
            pNew = sqlite3ExprListAppend(pNew, pExpr, 0);
          }
        }
        if( !tableSeen ){
          if( zTName ){
            sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
          }else{
            sqlite3ErrorMsg(pParse, "no tables specified");







|







 







|


>
>
>
>
>
|


>


|
<
>

<
<
<
<
<
<
<

>


>



>
>
>
>
>
>
>
>
>
>
>
>
>
>


>
>
>







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846

847
848







849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
....
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.227 2005/01/18 17:20:10 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
  pTab->nCol = pEList->nExpr;
  assert( pTab->nCol>0 );
  pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
  for(i=0, pCol=aCol; i<pTab->nCol; i++, pCol++){
    Expr *p, *pR;
    char *zType;
    char *zName;
    char *zBasename;
    int cnt;
    
    /* Get an appropriate name for the column
    */
    p = pEList->a[i].pExpr;
    assert( p->pRight==0 || p->pRight->token.z==0 || p->pRight->token.z[0]!=0 );
    if( (zName = pEList->a[i].zName)!=0 ){
      /* If the column contains an "AS <name>" phrase, use <name> as the name */
      zName = sqliteStrDup(zName);
    }else if( p->op==TK_DOT 
              && (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){

      /* For columns of the from A.B use B as the name */
      zName = sqlite3MPrintf("%T", &pR->token);







    }else if( p->span.z && p->span.z[0] ){
      /* Use the original text of the column expression as its name */
      zName = sqlite3MPrintf("%T", &p->span);
    }else{
      /* If all else fails, make up a name */
      zName = sqlite3MPrintf("column%d", i+1);
    }
    sqlite3Dequote(zName);

    /* Make sure the column name is unique.  If the name is not unique,
    ** append a integer to the name so that it becomes unique.
    */
    zBasename = zName;
    for(j=cnt=0; j<i; j++){
      if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
        zName = sqlite3MPrintf("%s:%d", zBasename, ++cnt);
        j = -1;
      }
    }
    if( zBasename!=zName ){
      sqliteFree(zBasename);
    }
    pCol->zName = zName;

    /* Get the typename, type affinity, and collating sequence for the
    ** column.
    */
    zType = sqliteStrDup(columnType(pParse, pSelect->pSrc ,p));
    pCol->zType = zType;
    pCol->affinity = SQLITE_AFF_NUMERIC;
    if( zType ){
      pCol->affinity = sqlite3AffinityType(zType, strlen(zType));
    }
    pCol->pColl = sqlite3ExprCollSeq(pParse, p);
................................................................................
              pExpr->token.z = 0;
              pExpr->token.n = 0;
              pExpr->token.dyn = 0;
            }else{
              pExpr = pRight;
              pExpr->span = pExpr->token;
            }
            pNew = sqlite3ExprListAppend(pNew, pExpr, &pRight->token);
          }
        }
        if( !tableSeen ){
          if( zTName ){
            sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
          }else{
            sqlite3ErrorMsg(pParse, "no tables specified");

Changes to test/misc4.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
114
115
116
117
118
119
120
121























122
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.10 2005/01/18 14:45:49 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    insert into b values ('+1',4);
    
    select a.*, x.*
      from a, (select key,sum(period) from b group by key) as x
      where a.key=x.key;
  }
} {01 data01 01 3.0 +1 data+1 +1 7.0}
























finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.11 2005/01/18 17:20:10 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    insert into b values ('+1',4);
    
    select a.*, x.*
      from a, (select key,sum(period) from b group by key) as x
      where a.key=x.key;
  }
} {01 data01 01 3.0 +1 data+1 +1 7.0}

# Ticket #1036.  When creating tables from a SELECT on a view, use the
# short names of columns.
#
do_test misc4-5.1 {
  execsql {
    create table t4(a,b);
    create table t5(a,c);
    insert into t4 values (1,2);
    insert into t5 values (1,3);
    create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
    create table problem as select * from myview; 
  }
  execsql2 {
    select * FROM problem;
  }
} {a 1}
do_test misc4-5.2 {
  execsql2 {
    create table t6 as select * from t4, t5;
    select * from t6;
  }
} {a 1 b 2 a:1 1 c 3}

finish_test

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.39 2005/01/18 16:02:41 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-6.1.2 {
  set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1 11 f1 33}}
do_test select1-6.1.3 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {test1.f1 11 test1.f2 22}}
do_test select1-6.1.4 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  execsql {PRAGMA full_column_names=off}
  lappend v $msg
} {0 {test1.f1 11 test1.f2 22}}
do_test select1-6.1.5 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.6 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg







|







 







|




|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.40 2005/01/18 17:20:10 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-6.1.2 {
  set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1 11 f1 33}}
do_test select1-6.1.3 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.4 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  execsql {PRAGMA full_column_names=off}
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.5 {
  set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg
} {0 {f1 11 f2 22}}
do_test select1-6.1.6 {
  set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
  lappend v $msg