SQLite4
Check-in [34138a088b]
Not logged in

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

Overview
Comment:Fix a couple of issues with ORDER BY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | primary-keys
Files: files | file ages | folders
SHA1: 34138a088b831cbfe487124f7b3ab7268213ef00
User & Date: dan 2012-04-17 18:47:02
Context
2012-04-17
18:49
Comment out some tests in select1.test that depend on "PRAGMA full_column_names" and "PRAGMA short_column_names". check-in: 946fe6504a user: dan tags: primary-keys
18:47
Fix a couple of issues with ORDER BY. check-in: 34138a088b user: dan tags: primary-keys
09:10
Enforce NOT NULL on all PRIMARY KEY columns. check-in: 035fdd3f5e user: dan tags: primary-keys
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

1455
1456
1457
1458
1459
1460
1461




1462
1463
1464
1465
1466
1467
1468
....
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) ){
    sqlite4 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    Expr *pExpr;                           /* Expression <column> */
    int iCol;                              /* Index of column <column> */
    int iDb;                               /* Database idx for pTab */





    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
................................................................................
    sqlite4TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

    /* This function is only called from two places. In both cases the vdbe
    ** has already been allocated. So assume sqlite4GetVdbe() is always
    ** successful here.
    */
    assert(v);
    if( iCol<0 ){
      int iAddr;

      iAddr = sqlite4CodeOnce(pParse);

      sqlite4OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
      eType = IN_INDEX_ROWID;

      sqlite4VdbeJumpHere(v, iAddr);
    }else{
      Index *pIdx;                         /* Iterator variable */

      /* The collation sequence used by the comparison. If an index is to
      ** be used in place of a temp-table, it must be ordered according
      ** to this collation sequence.  */
      CollSeq *pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pExpr);

      /* Check that the affinity that will be used to perform the 
      ** comparison is the same as the affinity of the column. If
      ** it is not, it is not possible to use any index.
      */
      char aff = comparisonAffinity(pX);
      int affinity_ok = (pTab->aCol[iCol].affinity==aff||aff==SQLITE_AFF_NONE);

      for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){
        if( (pIdx->aiColumn[0]==iCol)
         && sqlite4FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)==pReq
         && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
        ){
          int iAddr;
          char *pKey;
  
          pKey = (char *)sqlite4IndexKeyinfo(pParse, pIdx);
          iAddr = sqlite4CodeOnce(pParse);
  
          sqlite4VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
                               pKey,P4_KEYINFO_HANDOFF);
          VdbeComment((v, "%s", pIdx->zName));
          eType = IN_INDEX_INDEX;

          sqlite4VdbeJumpHere(v, iAddr);
          if( prNotFound && !pTab->aCol[iCol].notNull ){
            *prNotFound = ++pParse->nMem;
            sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
          }
        }
      }
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.







>
>
>
>







 







<
<

<
<
<
<
<
<
<
<
<
|
|
|
|

|
|
|
|
|
|

|
|
|
|

|
|
|
|
|
|
|
|
|
|

|
|
|
|
<







1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
....
1478
1479
1480
1481
1482
1483
1484


1485









1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517

1518
1519
1520
1521
1522
1523
1524
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) ){
    sqlite4 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    Expr *pExpr;                           /* Expression <column> */
    int iCol;                              /* Index of column <column> */
    int iDb;                               /* Database idx for pTab */
    Index *pIdx;
    CollSeq *pReq;
    char aff;
    int affinity_ok;

    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
................................................................................
    sqlite4TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

    /* This function is only called from two places. In both cases the vdbe
    ** has already been allocated. So assume sqlite4GetVdbe() is always
    ** successful here.
    */
    assert(v);












    /* The collation sequence used by the comparison. If an index is to
    ** be used in place of a temp-table, it must be ordered according
    ** to this collation sequence.  */
    pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pExpr);

    /* Check that the affinity that will be used to perform the 
    ** comparison is the same as the affinity of the column. If
    ** it is not, it is not possible to use any index.
    */
    aff = comparisonAffinity(pX);
    affinity_ok = (pTab->aCol[iCol].affinity==aff||aff==SQLITE_AFF_NONE);

    for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){
      if( (pIdx->aiColumn[0]==iCol)
          && sqlite4FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)==pReq
          && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
        ){
        int iAddr;
        char *pKey;

        pKey = (char *)sqlite4IndexKeyinfo(pParse, pIdx);
        iAddr = sqlite4CodeOnce(pParse);

        sqlite4VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
            pKey,P4_KEYINFO_HANDOFF);
        VdbeComment((v, "%s", pIdx->zName));
        eType = IN_INDEX_INDEX;

        sqlite4VdbeJumpHere(v, iAddr);
        if( prNotFound && !pTab->aCol[iCol].notNull ){
          *prNotFound = ++pParse->nMem;
          sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);

        }
      }
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.

Changes to src/select.c.

410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427






428
429
430
431

432
433




434
435
436
437
438
439
440


441
442

443
444
445
446
447
448
449
...
762
763
764
765
766
767
768
769
770
771
772
773
774





775


776

777

778


779
780
781
782
783


784
785
786
787
788
789
790
791
792
793
794
795
796
797
...
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917

918
919
920
921
922
923
924
...
958
959
960
961
962
963
964



965
966
967
968
969
970
971
972
973
974
975
976
977
978
...
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
....
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
....
3939
3940
3941
3942
3943
3944
3945
3946


3947
3948
3949
3950
3951
3952
3953
....
3970
3971
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
....
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
}

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(
  Parse *pParse,         /* Parser context */
  ExprList *pOrderBy,    /* The ORDER BY clause */
  Select *pSelect,       /* The whole SELECT statement */
  int regData            /* Register holding data to be sorted */
){
  Vdbe *v = pParse->pVdbe;
  int nExpr = pOrderBy->nExpr;
  int regBase = sqlite4GetTempRange(pParse, nExpr+2);
  int regRecord = sqlite4GetTempReg(pParse);
  int regKey = sqlite4GetTempReg(pParse);
  int op;






  sqlite4ExprCacheClear(pParse);
  sqlite4ExprCodeExprList(pParse, pOrderBy, regBase, 0);
  sqlite4VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr);
  sqlite4ExprCodeMove(pParse, regData, regBase+nExpr+1, 1);

  sqlite4VdbeAddOp2(v, OP_MakeKey, pOrderBy->iECursor, regKey);
  sqlite4VdbeAddOp3(v, OP_MakeRecord, regBase, nExpr + 2, regRecord);




  if( pSelect->selFlags & SF_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite4VdbeAddOp3(v, op, pOrderBy->iECursor, regRecord, regKey);
  sqlite4ReleaseTempReg(pParse, regRecord);


  sqlite4ReleaseTempReg(pParse, regKey);
  sqlite4ReleaseTempRange(pParse, regBase, nExpr+2);

  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;
................................................................................
** index to implement a DISTINCT test.
**
** Space to hold the KeyInfo structure is obtain from malloc.  The calling
** function is responsible for seeing that this structure is eventually
** freed.  Add the KeyInfo structure to the P4 field of an opcode using
** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
*/
static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList){
  sqlite4 *db = pParse->db;
  int nExpr;
  KeyInfo *pInfo;
  struct ExprList_item *pItem;
  int i;








  nExpr = pList->nExpr;

  pInfo = sqlite4DbMallocZero(db, sizeof(*pInfo) + nExpr*(sizeof(CollSeq*)+1) );

  if( pInfo ){


    pInfo->aSortOrder = (u8*)&pInfo->aColl[nExpr];
    pInfo->nField = (u16)nExpr;
    pInfo->enc = ENC(db);
    pInfo->db = db;
    for(i=0, pItem=pList->a; i<nExpr; i++, pItem++){


      CollSeq *pColl;
      pColl = sqlite4ExprCollSeq(pParse, pItem->pExpr);
      if( !pColl ){
        pColl = db->pDfltColl;
      }
      pInfo->aColl[i] = pColl;
      pInfo->aSortOrder[i] = pItem->sortOrder;
    }
  }
  return pInfo;
}

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
................................................................................
#else
/* No-op versions of the explainXXX() functions and macros. */
# define explainComposite(v,w,x,y,z)
#endif

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter.  After the loop is terminated
** we need to run the sorter and output the results.  The following
** routine generates the code needed to do that.
*/
static void generateSortTail(
  Parse *pParse,    /* Parsing context */
  Select *p,        /* The SELECT statement */
  Vdbe *v,          /* Generate code into this VDBE */
  int nColumn,      /* Number of columns of data */
  SelectDest *pDest /* Write the sorted results here */
){
  int addrBreak = sqlite4VdbeMakeLabel(v);     /* Jump here to exit loop */
  int addrContinue = sqlite4VdbeMakeLabel(v);  /* Jump here for next cycle */
  int addr;
  int iTab;
  int pseudoTab = 0;
  ExprList *pOrderBy = p->pOrderBy;

  int eDest = pDest->eDest;
  int iParm = pDest->iParm;

  int regRow;
  int regRowid;

  iTab = pOrderBy->iECursor;
  regRow = sqlite4GetTempReg(pParse);
  if( eDest==SRT_Output || eDest==SRT_Coroutine ){
    pseudoTab = pParse->nTab++;
    sqlite4VdbeAddOp3(v, OP_OpenPseudo, pseudoTab, regRow, nColumn);
    regRowid = 0;
  }else{
    regRowid = sqlite4GetTempReg(pParse);
  }

  if( p->selFlags & SF_UseSorter ){
    int regSortOut = ++pParse->nMem;
    int ptab2 = pParse->nTab++;
    sqlite4VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
    addr = 1 + sqlite4VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
................................................................................
    }
#endif
    default: {
      int i;
      assert( eDest==SRT_Output || eDest==SRT_Coroutine ); 
      testcase( eDest==SRT_Output );
      testcase( eDest==SRT_Coroutine );



      for(i=0; i<nColumn; i++){
        assert( regRow!=pDest->iMem+i );
        sqlite4VdbeAddOp3(v, OP_Column, pseudoTab, i, pDest->iMem+i);
        if( i==0 ){
          sqlite4VdbeChangeP5(v, OPFLAG_CLEARCACHE);
        }
      }
      if( eDest==SRT_Output ){
        sqlite4VdbeAddOp2(v, OP_ResultRow, pDest->iMem, nColumn);
        sqlite4ExprCacheAffinityChange(pParse, pDest->iMem, nColumn);
      }else{
        sqlite4VdbeAddOp1(v, OP_Yield, pDest->iParm);
      }
      break;
................................................................................
  sqlite4VdbeResolveLabel(v, addrContinue);
  if( p->selFlags & SF_UseSorter ){
    sqlite4VdbeAddOp2(v, OP_SorterNext, iTab, addr);
  }else{
    sqlite4VdbeAddOp2(v, OP_Next, iTab, addr);
  }
  sqlite4VdbeResolveLabel(v, addrBreak);
  if( eDest==SRT_Output || eDest==SRT_Coroutine ){
    sqlite4VdbeAddOp2(v, OP_Close, pseudoTab, 0);
  }
}

/*
** Return a pointer to a string containing the 'declaration type' of the
** expression pExpr. The string may be treated as static by the caller.
**
** The declaration type is the exact datatype definition extracted from the
................................................................................
      Expr *pE = pFunc->pExpr;
      assert( !ExprHasProperty(pE, EP_xIsSelect) );
      if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
        sqlite4ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
           "argument");
        pFunc->iDistinct = -1;
      }else{
        KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList);
        sqlite4VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
                          (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
      }
    }
  }
}

................................................................................
  ** extracted in pre-sorted order.  If that is the case, then the
  ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  ** we figure out that the sorting index is not needed.  The addrSortIndex
  ** variable is used to facilitate that change.
  */
  if( pOrderBy ){
    KeyInfo *pKeyInfo;
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);


    pOrderBy->iECursor = pParse->nTab++;
    p->addrOpenEphm[2] = addrSortIndex =
      sqlite4VdbeAddOp4(v, OP_OpenEphemeral,
                           pOrderBy->iECursor, pOrderBy->nExpr+2, 0,
                           (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    addrSortIndex = -1;
................................................................................
  }

  /* Open a virtual index to use for the distinct set.
  */
  if( p->selFlags & SF_Distinct ){
    KeyInfo *pKeyInfo;
    distinct = pParse->nTab++;
    pKeyInfo = keyInfoFromExprList(pParse, p->pEList);
    addrDistinctIndex = sqlite4VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0,
        (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    distinct = addrDistinctIndex = -1;
  }

  /* Aggregate and non-aggregate queries are handled differently */
................................................................................

      /* If there is a GROUP BY clause we might need a sorting index to
      ** implement it.  Allocate that sorting index now.  If it turns out
      ** that we do not need it after all, the OP_SorterOpen instruction
      ** will be converted into a Noop.  
      */
      sAggInfo.sortingIdx = pParse->nTab++;
      pKeyInfo = keyInfoFromExprList(pParse, pGroupBy);
      addrSortingIdx = sqlite4VdbeAddOp4(v, OP_SorterOpen, 
          sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 
          0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF);

      /* Initialize memory locations used by GROUP BY aggregate processing
      */
      iUseFlag = ++pParse->nMem;







|
|
|
|



|
<


>
>
>
>
>
>



<
>
|
|
>
>
>
>





|
<
>
>

|
>







 







|
|
<
<
|
|
>
>
>
>
>

>
>
|
>
|
>

>
>
|
|


<
>
>

|
<
|
<

|







 







|
|
|


|
|
|
|
|




|
<






|



|
<
<
<
<


>







 







>
>
>

<
|
<
<
|
|







 







<
<
<







 







|







 







|
>
>







 







|







 







|







410
411
412
413
414
415
416
417
418
419
420
421
422
423
424

425
426
427
428
429
430
431
432
433
434
435

436
437
438
439
440
441
442
443
444
445
446
447
448

449
450
451
452
453
454
455
456
457
458
459
460
...
773
774
775
776
777
778
779
780
781


782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802

803
804
805
806

807

808
809
810
811
812
813
814
815
816
...
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918

919
920
921
922
923
924
925
926
927
928
929




930
931
932
933
934
935
936
937
938
939
...
973
974
975
976
977
978
979
980
981
982
983

984


985
986
987
988
989
990
991
992
993
....
1001
1002
1003
1004
1005
1006
1007



1008
1009
1010
1011
1012
1013
1014
....
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
....
3951
3952
3953
3954
3955
3956
3957
3958
3959
3960
3961
3962
3963
3964
3965
3966
3967
....
3984
3985
3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
....
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
}

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(
  Parse *pParse,                  /* Parser context */
  ExprList *pOrderBy,             /* The ORDER BY clause */
  Select *pSelect,                /* The whole SELECT statement */
  int regData                     /* Register holding data to be sorted */
){
  Vdbe *v = pParse->pVdbe;
  int nExpr = pOrderBy->nExpr;
  int regBase = sqlite4GetTempRange(pParse, nExpr+1);

  int regKey = sqlite4GetTempReg(pParse);
  int op;

  /* Assemble the sort-key values in a contiguous array of registers
  ** starting at regBase. The sort-key consists of the result of each 
  ** expression in the ORDER BY clause followed by a unique sequence 
  ** number. The sequence number allows more than one row with the same
  ** sort-key.  */
  sqlite4ExprCacheClear(pParse);
  sqlite4ExprCodeExprList(pParse, pOrderBy, regBase, 0);
  sqlite4VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr);


  /* Encode the sort-key. */
  sqlite4VdbeAddOp3(v, OP_MakeIdxKey, pOrderBy->iECursor, regBase, regKey);

  /* Insert an entry into the sorter. The key inserted is the encoded key
  ** created by the OP_MakeIdxKey coded above. The value is the record
  ** currently stored in register regData.  */
  if( pSelect->selFlags & SF_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite4VdbeAddOp3(v, op, pOrderBy->iECursor, regData, regKey);


  /* Release the temporary registers */
  sqlite4ReleaseTempReg(pParse, regKey);
  sqlite4ReleaseTempRange(pParse, regBase, nExpr+1);

  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;
................................................................................
** index to implement a DISTINCT test.
**
** Space to hold the KeyInfo structure is obtain from malloc.  The calling
** function is responsible for seeing that this structure is eventually
** freed.  Add the KeyInfo structure to the P4 field of an opcode using
** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
*/
static KeyInfo *keyInfoFromExprList(
  Parse *pParse, 


  ExprList *pList,
  int bOrderBy
){
  sqlite4 *db = pParse->db;       /* Database handle */
  int nField;                     /* Number of fields in keys */
  KeyInfo *pInfo;                 /* Object to return */
  int nByte;                      /* Bytes of space to allocate */

  assert( bOrderBy==0 || bOrderBy==1 );

  nField = pList->nExpr + bOrderBy;
  nByte = sizeof(KeyInfo) + nField * sizeof(CollSeq *) + nField;
  pInfo = (KeyInfo *)sqlite4DbMallocZero(db, nByte);

  if( pInfo ){
    int i;                        /* Used to iterate through pList */

    pInfo->aSortOrder = (u8*)&pInfo->aColl[nField];
    pInfo->nField = (u16)nField;
    pInfo->enc = ENC(db);
    pInfo->db = db;


    for(i=0; i<pList->nExpr; i++){
      CollSeq *pColl;
      pColl = sqlite4ExprCollSeq(pParse, pList->a[i].pExpr);

      if( !pColl ) pColl = db->pDfltColl;

      pInfo->aColl[i] = pColl;
      pInfo->aSortOrder[i] = pList->a[i].sortOrder;
    }
  }
  return pInfo;
}

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
................................................................................
#else
/* No-op versions of the explainXXX() functions and macros. */
# define explainComposite(v,w,x,y,z)
#endif

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter. After the loop is terminated
** we need to loop through the contents of the sorter and output the 
** results. The following routine generates the code needed to do that.
*/
static void generateSortTail(
  Parse *pParse,                  /* Parsing context */
  Select *p,                      /* The SELECT statement */
  Vdbe *v,                        /* Generate code into this VDBE */
  int nColumn,                    /* Number of columns of data */
  SelectDest *pDest               /* Write the sorted results here */
){
  int addrBreak = sqlite4VdbeMakeLabel(v);     /* Jump here to exit loop */
  int addrContinue = sqlite4VdbeMakeLabel(v);  /* Jump here for next cycle */
  int addr;
  int iTab;                       /* Sorter object cursor */

  ExprList *pOrderBy = p->pOrderBy;

  int eDest = pDest->eDest;
  int iParm = pDest->iParm;

  int regRow;
  int regRowid = 0;

  iTab = pOrderBy->iECursor;
  regRow = sqlite4GetTempReg(pParse);
  if( eDest!=SRT_Output && eDest!=SRT_Coroutine ){




    regRowid = sqlite4GetTempReg(pParse);
  }

  if( p->selFlags & SF_UseSorter ){
    int regSortOut = ++pParse->nMem;
    int ptab2 = pParse->nTab++;
    sqlite4VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
    addr = 1 + sqlite4VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
................................................................................
    }
#endif
    default: {
      int i;
      assert( eDest==SRT_Output || eDest==SRT_Coroutine ); 
      testcase( eDest==SRT_Output );
      testcase( eDest==SRT_Coroutine );

      /* Read the data out of the sorter and into the array of nColumn
      ** contiguous registers starting at pDest->iMem.  */
      for(i=0; i<nColumn; i++){

        sqlite4VdbeAddOp3(v, OP_Column, iTab, i, pDest->iMem+i);


      }

      if( eDest==SRT_Output ){
        sqlite4VdbeAddOp2(v, OP_ResultRow, pDest->iMem, nColumn);
        sqlite4ExprCacheAffinityChange(pParse, pDest->iMem, nColumn);
      }else{
        sqlite4VdbeAddOp1(v, OP_Yield, pDest->iParm);
      }
      break;
................................................................................
  sqlite4VdbeResolveLabel(v, addrContinue);
  if( p->selFlags & SF_UseSorter ){
    sqlite4VdbeAddOp2(v, OP_SorterNext, iTab, addr);
  }else{
    sqlite4VdbeAddOp2(v, OP_Next, iTab, addr);
  }
  sqlite4VdbeResolveLabel(v, addrBreak);



}

/*
** Return a pointer to a string containing the 'declaration type' of the
** expression pExpr. The string may be treated as static by the caller.
**
** The declaration type is the exact datatype definition extracted from the
................................................................................
      Expr *pE = pFunc->pExpr;
      assert( !ExprHasProperty(pE, EP_xIsSelect) );
      if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
        sqlite4ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
           "argument");
        pFunc->iDistinct = -1;
      }else{
        KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0);
        sqlite4VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
                          (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
      }
    }
  }
}

................................................................................
  ** extracted in pre-sorted order.  If that is the case, then the
  ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  ** we figure out that the sorting index is not needed.  The addrSortIndex
  ** variable is used to facilitate that change.
  */
  if( pOrderBy ){
    KeyInfo *pKeyInfo;
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
    if( pKeyInfo ) pKeyInfo->nData = pEList->nExpr;

    pOrderBy->iECursor = pParse->nTab++;
    p->addrOpenEphm[2] = addrSortIndex =
      sqlite4VdbeAddOp4(v, OP_OpenEphemeral,
                           pOrderBy->iECursor, pOrderBy->nExpr+2, 0,
                           (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    addrSortIndex = -1;
................................................................................
  }

  /* Open a virtual index to use for the distinct set.
  */
  if( p->selFlags & SF_Distinct ){
    KeyInfo *pKeyInfo;
    distinct = pParse->nTab++;
    pKeyInfo = keyInfoFromExprList(pParse, p->pEList, 0);
    addrDistinctIndex = sqlite4VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0,
        (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  }else{
    distinct = addrDistinctIndex = -1;
  }

  /* Aggregate and non-aggregate queries are handled differently */
................................................................................

      /* If there is a GROUP BY clause we might need a sorting index to
      ** implement it.  Allocate that sorting index now.  If it turns out
      ** that we do not need it after all, the OP_SorterOpen instruction
      ** will be converted into a Noop.  
      */
      sAggInfo.sortingIdx = pParse->nTab++;
      pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0);
      addrSortingIdx = sqlite4VdbeAddOp4(v, OP_SorterOpen, 
          sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 
          0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF);

      /* Initialize memory locations used by GROUP BY aggregate processing
      */
      iUseFlag = ++pParse->nMem;

Changes to src/vdbe.c.

2122
2123
2124
2125
2126
2127
2128

2129
2130

2131
2132
2133
2134
2135
2136
2137
....
2849
2850
2851
2852
2853
2854
2855

2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881

2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
....
3581
3582
3583
3584
3585
3586
3587


3588
3589
3590
3591
3592
3593
3594
    aData = (const KVByteArray*)pReg->z;
    nData = pReg->n;
  }else{
    aData = 0;
    MemSetTypeFlag(pDest, MEM_Null);
  }
  if( rc==SQLITE_OK && aData ){

    int nField = pC->nField;
    if( pC->pKeyInfo ) nField = pC->pKeyInfo->nData;

    rc = sqlite4VdbeCreateDecoder(db, aData, nData, nField, &pCodec);
    if( rc==0 ){
      pDefault = (pOp->p4type==P4_MEM) ? pOp->p4.pMem : 0;
      rc = sqlite4VdbeDecodeValue(pCodec, pOp->p2, pDefault, pDest);
      assert( rc==SQLITE_OK );
      sqlite4VdbeDestroyDecoder(pCodec);
    }
................................................................................
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pseudoTableReg==0 );
  assert( OP_SeekLe == OP_SeekLt+1 );
  assert( OP_SeekGe == OP_SeekLt+2 );
  assert( OP_SeekGt == OP_SeekLt+3 );
  assert( pC->isOrdered );

  oc = pOp->opcode;
  pC->nullRow = 0;
  if( pC->isTable ){
    nField = 1;
  }else{
    nField = pOp->p4.i;
  }
  pIn3 = &aMem[pOp->p3];
  rc = sqlite4VdbeEncodeKey(db, pIn3, nField, pC->iRoot, pC->pKeyInfo,
                            &aProbe, &nProbe, 0);
  if( rc ){
    sqlite4DbFree(db, aProbe);
    break;
  }
  rc = sqlite4KVCursorSeek(pC->pKVCur, aProbe, nProbe, 
                           oc<=OP_SeekLe ? -1 : 1);
  sqlite4DbFree(db, aProbe);
  if( rc==SQLITE_OK ){
    if( oc==OP_SeekLt ){
      rc = sqlite4KVCursorPrev(pC->pKVCur);
    }else if( oc==OP_SeekGt ){
      rc = sqlite4KVCursorNext(pC->pKVCur);
    }
  }else if( rc==SQLITE_INEXACT ){
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK ){
    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE_OK ){
      iRoot = 0;
      n = sqlite4GetVarint64(aKey, nKey, &iRoot);
      if( iRoot!=pC->iRoot ) rc = SQLITE_DONE;
      c = aKey[n];
      if( c<0x05 || c>0xfa ) rc = SQLITE_DONE;
    }
  }
  if( rc==SQLITE_DONE ){
    rc = SQLITE_OK;
    pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: Seek P1 P2 * * *
................................................................................
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  rc = sqlite4VdbeSeekEnd(pC, -1);
  if( rc==SQLITE_NOTFOUND ){  
    rc = SQLITE_OK;
    if( pOp->p2 ) pc = pOp->p2 - 1;


  }
  break;
}


/* Opcode: Sort P1 P2 * * *
**







>

|
>







 







>


<
<
<
|
<







|
<










>





|

|


|







 







>
>







2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
....
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860



2861

2862
2863
2864
2865
2866
2867
2868
2869

2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
....
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
    aData = (const KVByteArray*)pReg->z;
    nData = pReg->n;
  }else{
    aData = 0;
    MemSetTypeFlag(pDest, MEM_Null);
  }
  if( rc==SQLITE_OK && aData ){
    /* TODO: Fix this somehow... */
    int nField = pC->nField;
    if( pC->pKeyInfo && pC->pKeyInfo->nData ) nField = pC->pKeyInfo->nData;

    rc = sqlite4VdbeCreateDecoder(db, aData, nData, nField, &pCodec);
    if( rc==0 ){
      pDefault = (pOp->p4type==P4_MEM) ? pOp->p4.pMem : 0;
      rc = sqlite4VdbeDecodeValue(pCodec, pOp->p2, pDefault, pDest);
      assert( rc==SQLITE_OK );
      sqlite4VdbeDestroyDecoder(pCodec);
    }
................................................................................
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pseudoTableReg==0 );
  assert( OP_SeekLe == OP_SeekLt+1 );
  assert( OP_SeekGe == OP_SeekLt+2 );
  assert( OP_SeekGt == OP_SeekLt+3 );
  assert( pC->isOrdered );

  oc = pOp->opcode;
  pC->nullRow = 0;



  nField = pOp->p4.i;

  pIn3 = &aMem[pOp->p3];
  rc = sqlite4VdbeEncodeKey(db, pIn3, nField, pC->iRoot, pC->pKeyInfo,
                            &aProbe, &nProbe, 0);
  if( rc ){
    sqlite4DbFree(db, aProbe);
    break;
  }
  rc = sqlite4KVCursorSeek(pC->pKVCur, aProbe, nProbe, oc<=OP_SeekLe ? -1 : 1);

  sqlite4DbFree(db, aProbe);
  if( rc==SQLITE_OK ){
    if( oc==OP_SeekLt ){
      rc = sqlite4KVCursorPrev(pC->pKVCur);
    }else if( oc==OP_SeekGt ){
      rc = sqlite4KVCursorNext(pC->pKVCur);
    }
  }else if( rc==SQLITE_INEXACT ){
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK ){
    rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey);
    if( rc==SQLITE_OK ){
      iRoot = 0;
      n = sqlite4GetVarint64(aKey, nKey, &iRoot);
      if( iRoot!=pC->iRoot ) rc = SQLITE_NOTFOUND;
      c = aKey[n];
      if( c<0x05 || c>0xfa ) rc = SQLITE_NOTFOUND;
    }
  }
  if( rc==SQLITE_NOTFOUND ){
    rc = SQLITE_OK;
    pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: Seek P1 P2 * * *
................................................................................
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  rc = sqlite4VdbeSeekEnd(pC, -1);
  if( rc==SQLITE_NOTFOUND ){  
    rc = SQLITE_OK;
    if( pOp->p2 ) pc = pOp->p2 - 1;
  }else{
    pC->nullRow = 0;
  }
  break;
}


/* Opcode: Sort P1 P2 * * *
**

Changes to test/permutations.test.

129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
#   quick
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files [
  test_set simple.test fkey1.test conflict.test trigger2.test
]

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [







|







129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
#   quick
#   full
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files [
  test_set simple.test fkey1.test conflict.test trigger2.test select1.test
]

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [

Changes to test/select1.test.

238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
....
1034
1035
1036
1037
1038
1039
1040

1041
1042
1043
1044
1045
1046
1047
      GROUP BY f1
     HAVING max(m+5)<10
  }
} {1 {misuse of aliased aggregate m}}
do_test select1-2.23 {
  execsql {
    CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
    INSERT INTO tkt2526 VALUES('x','y',NULL);
    INSERT INTO tkt2526 VALUES('x','z',NULL);
  }
  catchsql {
    SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
  }
} {1 {misuse of aliased aggregate cn}}

# WHERE clause expressions
................................................................................
    SELECT * FROM sqlite_master WHERE rowid=10;
    SELECT * FROM sqlite_master WHERE rowid<10;
    SELECT * FROM sqlite_master WHERE rowid<=10;
    SELECT * FROM sqlite_master WHERE rowid>=10;
    SELECT * FROM sqlite_master;
  }
} {}

do_test select1-14.2 {
  execsql { 
    SELECT 10 IN (SELECT rowid FROM sqlite_master);
  }
} {0}

if {[db one {PRAGMA locking_mode}]=="normal"} {







|
|







 







>







238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
....
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
      GROUP BY f1
     HAVING max(m+5)<10
  }
} {1 {misuse of aliased aggregate m}}
do_test select1-2.23 {
  execsql {
    CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
    INSERT INTO tkt2526 VALUES('x','y',1);
    INSERT INTO tkt2526 VALUES('x','z',2);
  }
  catchsql {
    SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
  }
} {1 {misuse of aliased aggregate cn}}

# WHERE clause expressions
................................................................................
    SELECT * FROM sqlite_master WHERE rowid=10;
    SELECT * FROM sqlite_master WHERE rowid<10;
    SELECT * FROM sqlite_master WHERE rowid<=10;
    SELECT * FROM sqlite_master WHERE rowid>=10;
    SELECT * FROM sqlite_master;
  }
} {}

do_test select1-14.2 {
  execsql { 
    SELECT 10 IN (SELECT rowid FROM sqlite_master);
  }
} {0}

if {[db one {PRAGMA locking_mode}]=="normal"} {

Changes to test/simple.test.

704
705
706
707
708
709
710
711
712
713
714




































715
716
717
718
719
720
721
} {4 3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 39.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}
breakpoint
do_catchsql_test 39.2 {
  INSERT INTO t1 VALUES(NULL, 'xyz');
} {1 {t1.a may not be NULL}}





































#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');







<



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







704
705
706
707
708
709
710

711
712
713
714
715
716
717
718
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
} {4 3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 39.1 {
  CREATE TABLE t1(a PRIMARY KEY, b);
}

do_catchsql_test 39.2 {
  INSERT INTO t1 VALUES(NULL, 'xyz');
} {1 {t1.a may not be NULL}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 40.1 {
    CREATE TABLE abc(a, b, c, PRIMARY KEY(a, b));
    INSERT INTO abc VALUES(1, 1, 1);
    SELECT * FROM abc;
} {1 1 1}
do_execsql_test 40.2 { SELECT max(a) FROM abc } {1}
do_execsql_test 40.3 {
  SELECT a+(select max(a) FROM abc), 
         b+(select max(a) FROM abc), 
         c+(select max(a) FROM abc) 
  FROM abc
} {2 2 2}
do_execsql_test 40.4 {
  INSERT INTO abc SELECT 
      a+(select max(a) FROM abc), 
      b+(select max(a) FROM abc), 
      c+(select max(a) FROM abc) 
  FROM abc;
}
do_execsql_test 40.5 { SELECT * FROM abc } {1 1 1 2 2 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 41.1 {
  CREATE TABLE x(a, b);
  INSERT INTO x VALUES(1, 'one');
  INSERT INTO x VALUES(2, 'two');
  INSERT INTO x VALUES(1, 'three');
} 

do_execsql_test 41.2 {
  SELECT * FROM x ORDER BY a;
} {1 one 1 three 2 two}

#proc populate_t1 {} {
#  db eval {
#    INSERT INTO t1(a, b) VALUES(4, 'four');
#    INSERT INTO t1(a, b) VALUES(9, 'nine');
#    INSERT INTO t1(a, b) VALUES(5, 'five');
#    INSERT INTO t1(a, b) VALUES(1, 'one');