/ Check-in [9c8c1092a8]
Login

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

Overview
Comment:Ensure that columns of views and sub-queries that are expressions with no affinity are comparied without any type conversions, as required in the documentation. Tickets [61c853857f40da49] and [d52a29a9e6bc55c5].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9c8c1092a8ce80e114fcfe8ce780332a6f269b8c87df226242b582d2d825c393
User & Date: drh 2019-08-06 15:32:42
Context
2019-08-06
18:40
Add "PRAGMA foreign_keys=OFF;" to the start of the script output by ".recover", just as is done for ".dump". check-in: bfc29e62ef user: dan tags: trunk
15:32
Ensure that columns of views and sub-queries that are expressions with no affinity are comparied without any type conversions, as required in the documentation. Tickets [61c853857f40da49] and [d52a29a9e6bc55c5]. check-in: 9c8c1092a8 user: drh tags: trunk
15:18
Performance optimization to the new affinity handling logic. Closed-Leaf check-in: c9724e761b user: drh tags: pending
2019-08-05
18:01
Refactor field Expr.affinity into Expr.affExpr to avoid confusion with other fields and variables named "affinity" and display affExpr it in sqlite3TreeViewExpr() output. check-in: a29f2a7d07 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
....
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
....
2439
2440
2441
2442
2443
2444
2445
2446

2447
2448
2449
2450
2451
2452
2453
      sqlite3MayAbort(pParse);
      sqlite3VdbeAddOp3(v, OP_OpenWrite, 1, pParse->regRoot, iDb);
      sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
      pParse->nTab = 2;
      addrTop = sqlite3VdbeCurrentAddr(v) + 1;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
      if( pParse->nErr ) return;
      pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect);
      if( pSelTab==0 ) return;
      assert( p->aCol==0 );
      p->nCol = pSelTab->nCol;
      p->aCol = pSelTab->aCol;
      pSelTab->nCol = 0;
      pSelTab->aCol = 0;
      sqlite3DeleteTable(db, pSelTab);
................................................................................
    n = pParse->nTab;
    sqlite3SrcListAssignCursors(pParse, pSel->pSrc);
    pTable->nCol = -1;
    db->lookaside.bDisable++;
#ifndef SQLITE_OMIT_AUTHORIZATION
    xAuth = db->xAuth;
    db->xAuth = 0;
    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
    db->xAuth = xAuth;
#else
    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel);
#endif
    pParse->nTab = n;
    if( pTable->pCheck ){
      /* CREATE VIEW name(arglist) AS ...
      ** The names of the columns in the table are taken from
      ** arglist which is stored in pTable->pCheck.  The pCheck field
      ** normally holds CHECK constraints on an ordinary table, but for
................................................................................
      */
      sqlite3ColumnsFromExprList(pParse, pTable->pCheck, 
                                 &pTable->nCol, &pTable->aCol);
      if( db->mallocFailed==0 
       && pParse->nErr==0
       && pTable->nCol==pSel->pEList->nExpr
      ){
        sqlite3SelectAddColumnTypeAndCollation(pParse, pTable, pSel);

      }
    }else if( pSelTab ){
      /* CREATE VIEW name AS...  without an argument list.  Construct
      ** the column names from the SELECT statement that defines the view.
      */
      assert( pTable->aCol==0 );
      pTable->nCol = pSelTab->nCol;







|







 







|


|







 







|
>







2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
....
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
....
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
      sqlite3MayAbort(pParse);
      sqlite3VdbeAddOp3(v, OP_OpenWrite, 1, pParse->regRoot, iDb);
      sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
      pParse->nTab = 2;
      addrTop = sqlite3VdbeCurrentAddr(v) + 1;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
      if( pParse->nErr ) return;
      pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect, SQLITE_AFF_BLOB);
      if( pSelTab==0 ) return;
      assert( p->aCol==0 );
      p->nCol = pSelTab->nCol;
      p->aCol = pSelTab->aCol;
      pSelTab->nCol = 0;
      pSelTab->aCol = 0;
      sqlite3DeleteTable(db, pSelTab);
................................................................................
    n = pParse->nTab;
    sqlite3SrcListAssignCursors(pParse, pSel->pSrc);
    pTable->nCol = -1;
    db->lookaside.bDisable++;
#ifndef SQLITE_OMIT_AUTHORIZATION
    xAuth = db->xAuth;
    db->xAuth = 0;
    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel, SQLITE_AFF_NONE);
    db->xAuth = xAuth;
#else
    pSelTab = sqlite3ResultSetOfSelect(pParse, pSel, SQLITE_AFF_NONE);
#endif
    pParse->nTab = n;
    if( pTable->pCheck ){
      /* CREATE VIEW name(arglist) AS ...
      ** The names of the columns in the table are taken from
      ** arglist which is stored in pTable->pCheck.  The pCheck field
      ** normally holds CHECK constraints on an ordinary table, but for
................................................................................
      */
      sqlite3ColumnsFromExprList(pParse, pTable->pCheck, 
                                 &pTable->nCol, &pTable->aCol);
      if( db->mallocFailed==0 
       && pParse->nErr==0
       && pTable->nCol==pSel->pEList->nExpr
      ){
        sqlite3SelectAddColumnTypeAndCollation(pParse, pTable, pSel,
                                               SQLITE_AFF_NONE);
      }
    }else if( pSelTab ){
      /* CREATE VIEW name AS...  without an argument list.  Construct
      ** the column names from the SELECT statement that defines the view.
      */
      assert( pTable->aCol==0 );
      pTable->nCol = pSelTab->nCol;

Changes to src/expr.c.

226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250


251
252
253
254
255
256
257
...
276
277
278
279
280
281
282
283
284
285

286
287
288
289
290

291
292
293
294
295
296
297
....
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
....
3486
3487
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
....
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
/*
** pExpr is an operand of a comparison operator.  aff2 is the
** type affinity of the other operand.  This routine returns the
** type affinity that should be used for the comparison operator.
*/
char sqlite3CompareAffinity(Expr *pExpr, char aff2){
  char aff1 = sqlite3ExprAffinity(pExpr);
  if( aff1 && aff2 ){
    /* Both sides of the comparison are columns. If one has numeric
    ** affinity, use that. Otherwise use no affinity.
    */
    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_BLOB;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column.  Compare the
    ** results directly.
    */
    return SQLITE_AFF_BLOB;
  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    assert( aff1==0 || aff2==0 );
    return (aff1 + aff2);


  }
}

/*
** pExpr is a comparison operator.  Return the type affinity that should
** be applied to both operands prior to doing the comparison.
*/
................................................................................
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);
  switch( aff ){
    case SQLITE_AFF_BLOB:
      return 1;

    case SQLITE_AFF_TEXT:
      return idx_affinity==SQLITE_AFF_TEXT;
    default:
      return sqlite3IsNumericAffinity(idx_affinity);
  }

}

/*
** Return the P5 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
*/
static u8 binaryCompareP5(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){
................................................................................
    */
    char affinity;            /* Affinity of the LHS of the IN */
    int i;
    ExprList *pList = pExpr->x.pList;
    struct ExprList_item *pItem;
    int r1, r2, r3;
    affinity = sqlite3ExprAffinity(pLeft);
    if( !affinity ){
      affinity = SQLITE_AFF_BLOB;
    }
    if( pKeyInfo ){
      assert( sqlite3KeyInfoIsWriteable(pKeyInfo) );
      pKeyInfo->aColl[0] = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
    }

................................................................................
        ** constraints, and that constant is coded by the pExpr->pLeft
        ** expresssion.  However, make sure the constant has the correct
        ** datatype by applying the Affinity of the table column to the
        ** constant.
        */
        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
        int aff = sqlite3TableColumnAffinity(pExpr->y.pTab, pExpr->iColumn);
        if( aff!=SQLITE_AFF_BLOB ){
          static const char zAff[] = "B\000C\000D\000E";
          assert( SQLITE_AFF_BLOB=='A' );
          assert( SQLITE_AFF_TEXT=='B' );
          if( iReg!=target ){
            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
            iReg = target;
          }
................................................................................
      */
      if( pDef->funcFlags & SQLITE_FUNC_AFFINITY ){
        const char *azAff[] = { "blob", "text", "numeric", "integer", "real" };
        char aff;
        assert( nFarg==1 );
        aff = sqlite3ExprAffinity(pFarg->a[0].pExpr);
        sqlite3VdbeLoadString(v, target, 
                              aff ? azAff[aff-SQLITE_AFF_BLOB] : "none");
        return target;
      }
#endif

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );







|








<
<
<
<
<


<
<
>
>







 







<
|
|
>
|
|
<
<

>







 







|







 







|







 







|







226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241





242
243


244
245
246
247
248
249
250
251
252
...
271
272
273
274
275
276
277

278
279
280
281
282


283
284
285
286
287
288
289
290
291
....
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
....
3480
3481
3482
3483
3484
3485
3486
3487
3488
3489
3490
3491
3492
3493
3494
....
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
/*
** pExpr is an operand of a comparison operator.  aff2 is the
** type affinity of the other operand.  This routine returns the
** type affinity that should be used for the comparison operator.
*/
char sqlite3CompareAffinity(Expr *pExpr, char aff2){
  char aff1 = sqlite3ExprAffinity(pExpr);
  if( aff1>SQLITE_AFF_NONE && aff2>SQLITE_AFF_NONE ){
    /* Both sides of the comparison are columns. If one has numeric
    ** affinity, use that. Otherwise use no affinity.
    */
    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_BLOB;
    }





  }else{
    /* One side is a column, the other is not. Use the columns affinity. */


    assert( aff1<=SQLITE_AFF_NONE || aff2<=SQLITE_AFF_NONE );
    return (aff1<=SQLITE_AFF_NONE ? aff2 : aff1) | SQLITE_AFF_NONE;
  }
}

/*
** pExpr is a comparison operator.  Return the type affinity that should
** be applied to both operands prior to doing the comparison.
*/
................................................................................
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);

  if( aff<SQLITE_AFF_TEXT ){
    return 1;
  }
  if( aff==SQLITE_AFF_TEXT ){
    return idx_affinity==SQLITE_AFF_TEXT;


  }
  return sqlite3IsNumericAffinity(idx_affinity);
}

/*
** Return the P5 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
*/
static u8 binaryCompareP5(Expr *pExpr1, Expr *pExpr2, int jumpIfNull){
................................................................................
    */
    char affinity;            /* Affinity of the LHS of the IN */
    int i;
    ExprList *pList = pExpr->x.pList;
    struct ExprList_item *pItem;
    int r1, r2, r3;
    affinity = sqlite3ExprAffinity(pLeft);
    if( affinity<=SQLITE_AFF_NONE ){
      affinity = SQLITE_AFF_BLOB;
    }
    if( pKeyInfo ){
      assert( sqlite3KeyInfoIsWriteable(pKeyInfo) );
      pKeyInfo->aColl[0] = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
    }

................................................................................
        ** constraints, and that constant is coded by the pExpr->pLeft
        ** expresssion.  However, make sure the constant has the correct
        ** datatype by applying the Affinity of the table column to the
        ** constant.
        */
        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
        int aff = sqlite3TableColumnAffinity(pExpr->y.pTab, pExpr->iColumn);
        if( aff>SQLITE_AFF_BLOB ){
          static const char zAff[] = "B\000C\000D\000E";
          assert( SQLITE_AFF_BLOB=='A' );
          assert( SQLITE_AFF_TEXT=='B' );
          if( iReg!=target ){
            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
            iReg = target;
          }
................................................................................
      */
      if( pDef->funcFlags & SQLITE_FUNC_AFFINITY ){
        const char *azAff[] = { "blob", "text", "numeric", "integer", "real" };
        char aff;
        assert( nFarg==1 );
        aff = sqlite3ExprAffinity(pFarg->a[0].pExpr);
        sqlite3VdbeLoadString(v, target, 
                (aff<=SQLITE_AFF_NONE) ? "none" : azAff[aff-SQLITE_AFF_BLOB]);
        return target;
      }
#endif

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );

Changes to src/insert.c.

84
85
86
87
88
89
90

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
...
135
136
137
138
139
140
141

142
143
144
145
146
147
148
149
150
151
152
153
    pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
    if( !pIdx->zColAff ){
      sqlite3OomFault(db);
      return 0;
    }
    for(n=0; n<pIdx->nColumn; n++){
      i16 x = pIdx->aiColumn[n];

      if( x>=0 ){
        pIdx->zColAff[n] = pTab->aCol[x].affinity;
      }else if( x==XN_ROWID ){
        pIdx->zColAff[n] = SQLITE_AFF_INTEGER;
      }else{
        char aff;
        assert( x==XN_EXPR );
        assert( pIdx->aColExpr!=0 );
        aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
        if( aff==0 ) aff = SQLITE_AFF_BLOB;
        pIdx->zColAff[n] = aff;
      }
    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}

................................................................................
    zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
    if( !zColAff ){
      sqlite3OomFault(db);
      return;
    }

    for(i=0; i<pTab->nCol; i++){

      zColAff[i] = pTab->aCol[i].affinity;
    }
    do{
      zColAff[i--] = 0;
    }while( i>=0 && zColAff[i]==SQLITE_AFF_BLOB );
    pTab->zColAff = zColAff;
  }
  assert( zColAff!=0 );
  i = sqlite3Strlen30NN(zColAff);
  if( i ){
    if( iReg ){
      sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);







>

|

|

<



|
|
|







 







>




|







84
85
86
87
88
89
90
91
92
93
94
95
96

97
98
99
100
101
102
103
104
105
106
107
108
109
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
    pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
    if( !pIdx->zColAff ){
      sqlite3OomFault(db);
      return 0;
    }
    for(n=0; n<pIdx->nColumn; n++){
      i16 x = pIdx->aiColumn[n];
      char aff;
      if( x>=0 ){
        aff = pTab->aCol[x].affinity;
      }else if( x==XN_ROWID ){
        aff = SQLITE_AFF_INTEGER;
      }else{

        assert( x==XN_EXPR );
        assert( pIdx->aColExpr!=0 );
        aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
      }
      if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;
      pIdx->zColAff[n] = aff;
    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}

................................................................................
    zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
    if( !zColAff ){
      sqlite3OomFault(db);
      return;
    }

    for(i=0; i<pTab->nCol; i++){
      assert( pTab->aCol[i].affinity!=0 );
      zColAff[i] = pTab->aCol[i].affinity;
    }
    do{
      zColAff[i--] = 0;
    }while( i>=0 && zColAff[i]<=SQLITE_AFF_BLOB );
    pTab->zColAff = zColAff;
  }
  assert( zColAff!=0 );
  i = sqlite3Strlen30NN(zColAff);
  if( i ){
    if( iReg ){
      sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);

Changes to src/select.c.

2031
2032
2033
2034
2035
2036
2037
2038

2039
2040
2041
2042
2043
2044
2045
....
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
....
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
....
5191
5192
5193
5194
5195
5196
5197
5198

5199
5200
5201
5202
5203
5204
5205
**
** This routine requires that all identifiers in the SELECT
** statement be resolved.
*/
void sqlite3SelectAddColumnTypeAndCollation(
  Parse *pParse,        /* Parsing contexts */
  Table *pTab,          /* Add column type information to this table */
  Select *pSelect       /* SELECT used to determine types and collations */

){
  sqlite3 *db = pParse->db;
  NameContext sNC;
  Column *pCol;
  CollSeq *pColl;
  int i;
  Expr *p;
................................................................................
      n = sqlite3Strlen30(pCol->zName);
      pCol->zName = sqlite3DbReallocOrFree(db, pCol->zName, n+m+2);
      if( pCol->zName ){
        memcpy(&pCol->zName[n+1], zType, m+1);
        pCol->colFlags |= COLFLAG_HASTYPE;
      }
    }
    if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_BLOB;
    pColl = sqlite3ExprCollSeq(pParse, p);
    if( pColl && pCol->zColl==0 ){
      pCol->zColl = sqlite3DbStrDup(db, pColl->zName);
    }
  }
  pTab->szTabRow = 1; /* Any non-zero value works */
}

/*
** Given a SELECT statement, generate a Table structure that describes
** the result set of that SELECT.
*/
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;
................................................................................
  if( pTab==0 ){
    return 0;
  }
  pTab->nTabRef = 1;
  pTab->zName = 0;
  pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
  return pTab;
}
................................................................................
    Table *pTab = pFrom->pTab;
    assert( pTab!=0 );
    if( (pTab->tabFlags & TF_Ephemeral)!=0 ){
      /* A sub-query in the FROM clause of a SELECT */
      Select *pSel = pFrom->pSelect;
      if( pSel ){
        while( pSel->pPrior ) pSel = pSel->pPrior;
        sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSel);

      }
    }
  }
}
#endif









|
>







 







|












|







 







|







 







|
>







2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
....
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
....
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
....
5192
5193
5194
5195
5196
5197
5198
5199
5200
5201
5202
5203
5204
5205
5206
5207
**
** This routine requires that all identifiers in the SELECT
** statement be resolved.
*/
void sqlite3SelectAddColumnTypeAndCollation(
  Parse *pParse,        /* Parsing contexts */
  Table *pTab,          /* Add column type information to this table */
  Select *pSelect,      /* SELECT used to determine types and collations */
  char aff              /* Default affinity for columns */
){
  sqlite3 *db = pParse->db;
  NameContext sNC;
  Column *pCol;
  CollSeq *pColl;
  int i;
  Expr *p;
................................................................................
      n = sqlite3Strlen30(pCol->zName);
      pCol->zName = sqlite3DbReallocOrFree(db, pCol->zName, n+m+2);
      if( pCol->zName ){
        memcpy(&pCol->zName[n+1], zType, m+1);
        pCol->colFlags |= COLFLAG_HASTYPE;
      }
    }
    if( pCol->affinity<=SQLITE_AFF_NONE ) pCol->affinity = aff;
    pColl = sqlite3ExprCollSeq(pParse, p);
    if( pColl && pCol->zColl==0 ){
      pCol->zColl = sqlite3DbStrDup(db, pColl->zName);
    }
  }
  pTab->szTabRow = 1; /* Any non-zero value works */
}

/*
** Given a SELECT statement, generate a Table structure that describes
** the result set of that SELECT.
*/
Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect, char aff){
  Table *pTab;
  sqlite3 *db = pParse->db;
  u64 savedFlags;

  savedFlags = db->flags;
  db->flags &= ~(u64)SQLITE_FullColNames;
  db->flags |= SQLITE_ShortColNames;
................................................................................
  if( pTab==0 ){
    return 0;
  }
  pTab->nTabRef = 1;
  pTab->zName = 0;
  pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect, aff);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
  return pTab;
}
................................................................................
    Table *pTab = pFrom->pTab;
    assert( pTab!=0 );
    if( (pTab->tabFlags & TF_Ephemeral)!=0 ){
      /* A sub-query in the FROM clause of a SELECT */
      Select *pSel = pFrom->pSelect;
      if( pSel ){
        while( pSel->pPrior ) pSel = pSel->pPrior;
        sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSel,
                                               SQLITE_AFF_NONE);
      }
    }
  }
}
#endif


Changes to src/sqliteInt.h.

1870
1871
1872
1873
1874
1875
1876

1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
....
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
** But rather than start with 0 or 1, we begin with 'A'.  That way,
** when multiple affinity types are concatenated into a string and
** used as the P4 operand, they will be more readable.
**
** Note also that the numeric types are grouped together so that testing
** for a numeric type is a single comparison.  And the BLOB type is first.
*/

#define SQLITE_AFF_BLOB     'A'
#define SQLITE_AFF_TEXT     'B'
#define SQLITE_AFF_NUMERIC  'C'
#define SQLITE_AFF_INTEGER  'D'
#define SQLITE_AFF_REAL     'E'

#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)

/*
** The SQLITE_AFF_MASK values masks off the significant bits of an
** affinity value.
*/
................................................................................
#endif
void sqlite3ResetAllSchemasOfConnection(sqlite3*);
void sqlite3ResetOneSchema(sqlite3*,int);
void sqlite3CollapseDatabaseArray(sqlite3*);
void sqlite3CommitInternalChanges(sqlite3*);
void sqlite3DeleteColumnNames(sqlite3*,Table*);
int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**);
void sqlite3SelectAddColumnTypeAndCollation(Parse*,Table*,Select*);
Table *sqlite3ResultSetOfSelect(Parse*,Select*);
void sqlite3OpenMasterTable(Parse *, int);
Index *sqlite3PrimaryKeyIndex(Table*);
i16 sqlite3ColumnOfIndex(Index*, i16);
void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  void sqlite3ColumnPropertiesFromName(Table*, Column*);
#else







>
|
|
|
|
|







 







|
|







1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
....
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
** But rather than start with 0 or 1, we begin with 'A'.  That way,
** when multiple affinity types are concatenated into a string and
** used as the P4 operand, they will be more readable.
**
** Note also that the numeric types are grouped together so that testing
** for a numeric type is a single comparison.  And the BLOB type is first.
*/
#define SQLITE_AFF_NONE     0x40  /* '@' */
#define SQLITE_AFF_BLOB     0x41  /* 'A' */
#define SQLITE_AFF_TEXT     0x42  /* 'B' */
#define SQLITE_AFF_NUMERIC  0x43  /* 'C' */
#define SQLITE_AFF_INTEGER  0x44  /* 'D' */
#define SQLITE_AFF_REAL     0x45  /* 'E' */

#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)

/*
** The SQLITE_AFF_MASK values masks off the significant bits of an
** affinity value.
*/
................................................................................
#endif
void sqlite3ResetAllSchemasOfConnection(sqlite3*);
void sqlite3ResetOneSchema(sqlite3*,int);
void sqlite3CollapseDatabaseArray(sqlite3*);
void sqlite3CommitInternalChanges(sqlite3*);
void sqlite3DeleteColumnNames(sqlite3*,Table*);
int sqlite3ColumnsFromExprList(Parse*,ExprList*,i16*,Column**);
void sqlite3SelectAddColumnTypeAndCollation(Parse*,Table*,Select*,char);
Table *sqlite3ResultSetOfSelect(Parse*,Select*,char);
void sqlite3OpenMasterTable(Parse *, int);
Index *sqlite3PrimaryKeyIndex(Table*);
i16 sqlite3ColumnOfIndex(Index*, i16);
void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  void sqlite3ColumnPropertiesFromName(Table*, Column*);
#else

Changes to src/vdbe.c.

339
340
341
342
343
344
345

346
347
348
349
350
351
352
**    always preferred, even if the affinity is REAL, because
**    an integer representation is more space efficient on disk.
**
** SQLITE_AFF_TEXT:
**    Convert pRec to a text representation.
**
** SQLITE_AFF_BLOB:

**    No-op.  pRec is unchanged.
*/
static void applyAffinity(
  Mem *pRec,          /* The value to apply affinity to */
  char affinity,      /* The affinity to be applied */
  u8 enc              /* Use this text encoding */
){







>







339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
**    always preferred, even if the affinity is REAL, because
**    an integer representation is more space efficient on disk.
**
** SQLITE_AFF_TEXT:
**    Convert pRec to a text representation.
**
** SQLITE_AFF_BLOB:
** SQLITE_AFF_NONE:
**    No-op.  pRec is unchanged.
*/
static void applyAffinity(
  Mem *pRec,          /* The value to apply affinity to */
  char affinity,      /* The affinity to be applied */
  u8 enc              /* Use this text encoding */
){

Changes to src/where.c.

1301
1302
1303
1304
1305
1306
1307

1308
1309
1310
1311
1312
1313
1314
** Return the affinity for a single column of an index.
*/
char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){
  assert( iCol>=0 && iCol<pIdx->nColumn );
  if( !pIdx->zColAff ){
    if( sqlite3IndexAffinityStr(db, pIdx)==0 ) return SQLITE_AFF_BLOB;
  }

  return pIdx->zColAff[iCol];
}
#endif


#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/* 







>







1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
** Return the affinity for a single column of an index.
*/
char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){
  assert( iCol>=0 && iCol<pIdx->nColumn );
  if( !pIdx->zColAff ){
    if( sqlite3IndexAffinityStr(db, pIdx)==0 ) return SQLITE_AFF_BLOB;
  }
  assert( pIdx->zColAff[iCol]!=0 );
  return pIdx->zColAff[iCol];
}
#endif


#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
/* 

Changes to src/wherecode.c.

314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338

339
340
341
342
343
344
345
346
347
348
349
350
351
  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**
** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
** beginning and end of zAff are ignored.  If all entries in zAff are
** SQLITE_AFF_BLOB, then no code gets generated.
**
** This routine makes its own copy of zAff so that the caller is free
** to modify zAff after this routine returns.
*/
static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  Vdbe *v = pParse->pVdbe;
  if( zAff==0 ){
    assert( pParse->db->mallocFailed );
    return;
  }
  assert( v!=0 );

  /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
  ** and end of the affinity string.
  */

  while( n>0 && zAff[0]==SQLITE_AFF_BLOB ){
    n--;
    base++;
    zAff++;
  }
  while( n>1 && zAff[n-1]==SQLITE_AFF_BLOB ){
    n--;
  }

  /* Code the OP_Affinity opcode if there is anything left to do. */
  if( n>0 ){
    sqlite3VdbeAddOp4(v, OP_Affinity, base, n, 0, zAff, n);
  }







|
|
|












|
|

>
|




|







314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**
** As an optimization, SQLITE_AFF_BLOB and SQLITE_AFF_NONE entries (which
** are no-ops) at the beginning and end of zAff are ignored.  If all entries
** in zAff are SQLITE_AFF_BLOB or SQLITE_AFF_NONE, then no code gets generated.
**
** This routine makes its own copy of zAff so that the caller is free
** to modify zAff after this routine returns.
*/
static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  Vdbe *v = pParse->pVdbe;
  if( zAff==0 ){
    assert( pParse->db->mallocFailed );
    return;
  }
  assert( v!=0 );

  /* Adjust base and n to skip over SQLITE_AFF_BLOB and SQLITE_AFF_NONE
  ** entries at the beginning and end of the affinity string.
  */
  assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB );
  while( n>0 && zAff[0]<=SQLITE_AFF_BLOB ){
    n--;
    base++;
    zAff++;
  }
  while( n>1 && zAff[n-1]<=SQLITE_AFF_BLOB ){
    n--;
  }

  /* Code the OP_Affinity opcode if there is anything left to do. */
  if( n>0 ){
    sqlite3VdbeAddOp4(v, OP_Affinity, base, n, 0, zAff, n);
  }

Changes to src/window.c.

990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
    );
    p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
    if( p->pSrc ){
      Table *pTab2;
      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      pSub->selFlags |= SF_Expanded;
      pTab2 = sqlite3ResultSetOfSelect(pParse, pSub);
      if( pTab2==0 ){
        rc = SQLITE_NOMEM;
      }else{
        memcpy(pTab, pTab2, sizeof(Table));
        pTab->tabFlags |= TF_Ephemeral;
        p->pSrc->a[0].pTab = pTab;
        pTab = pTab2;







|







990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
    );
    p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
    if( p->pSrc ){
      Table *pTab2;
      p->pSrc->a[0].pSelect = pSub;
      sqlite3SrcListAssignCursors(pParse, p->pSrc);
      pSub->selFlags |= SF_Expanded;
      pTab2 = sqlite3ResultSetOfSelect(pParse, pSub, SQLITE_AFF_NONE);
      if( pTab2==0 ){
        rc = SQLITE_NOMEM;
      }else{
        memcpy(pTab, pTab2, sizeof(Table));
        pTab->tabFlags |= TF_Ephemeral;
        p->pSrc->a[0].pTab = pTab;
        pTab = pTab2;

Changes to test/view.test.

719
720
721
722
723
724
725
726





































727
  WITH v17(x,y) AS (SELECT max(a), min(b) FROM t16 GROUP BY c)
  SELECT * FROM v17 AS one, v17 AS two WHERE one.x=1;
} {
  1 1 1 1 
  1 1 2 2 
  1 1 3 3
}






































finish_test








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

719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
  WITH v17(x,y) AS (SELECT max(a), min(b) FROM t16 GROUP BY c)
  SELECT * FROM v17 AS one, v17 AS two WHERE one.x=1;
} {
  1 1 1 1 
  1 1 2 2 
  1 1 3 3
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test view-27.0 {
  CREATE TABLE t0(c0 TEXT, c1);
  INSERT INTO t0(c0, c1) VALUES (-1, 0);
  CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0;
}

do_execsql_test view-27.1 {
  SELECT c0, typeof(c0), affinity(c0), c1, typeof(c1), affinity(c1) FROM v0;
} {
  -1   text text
   0.0 real none
}

do_execsql_test view-27.2 { SELECT c0<c1 FROM v0 } 1
do_execsql_test view-27.3 { SELECT c1<c0 FROM v0 } 0
do_execsql_test view-27.4 {
  SELECT 1 FROM v0 WHERE c1<c0
} {}
do_execsql_test view-27.5 {
  SELECT 1 FROM v0 WHERE c0<c1
} {1}

do_execsql_test view-27.6 { 
  SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) 
} 1
do_execsql_test view-27.7 { 
  SELECT c1<c0 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) 
} 0
do_execsql_test view-27.8 {
  SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c1<c0
} {}
do_execsql_test view-27.9 {
  SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c0<c1
} {1}

finish_test

Changes to test/window9.test.

126
127
128
129
130
131
132















133
134
135
136
      SELECT c IN (
        SELECT min(a) OVER (),
        (abs(row_number() OVER())+22)/19,
        max(a) OVER () FROM t1
        ) AS y FROM t2
      );
} {1 {sub-select returns 3 columns - expected 1}}

















finish_test








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




126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
      SELECT c IN (
        SELECT min(a) OVER (),
        (abs(row_number() OVER())+22)/19,
        max(a) OVER () FROM t1
        ) AS y FROM t2
      );
} {1 {sub-select returns 3 columns - expected 1}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t1(a, b TEXT);
  INSERT INTO t1 VALUES('A', 1), ('A', 2), ('2', 1), ('2', 2);
}

do_execsql_test 4.1.1 {
  SELECT b, b=count(*), '1,2'                   FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}
do_execsql_test 4.1.2 {
  SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b;
} {1 0 1,2 2 1 1,2}



finish_test