/ Check-in [bbddf16a]
Login

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

Overview
Comment:Towards getting ORDER BY to match against the correctin columns. This version only looks at the left-most column in a compound SELECT. That is the correct thing to do, but not what SQLite has historically done. (CVS 4620)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bbddf16ac9539c7d48adfc73c5a90eecb8df6865
User & Date: drh 2007-12-13 02:45:31
Context
2007-12-13
03:45
ORDER BY in a compound SELECT will first match against the left-most SELECT. If there is no match there, it begins working its way to the right. (CVS 4621) check-in: 56063ec8 user: drh tags: trunk
02:45
Towards getting ORDER BY to match against the correctin columns. This version only looks at the left-most column in a compound SELECT. That is the correct thing to do, but not what SQLite has historically done. (CVS 4620) check-in: bbddf16a user: drh tags: trunk
2007-12-12
22:24
Minor cleanup changes on the OP_StackDepth opcode. Added the sidedelete test for additional testing of ticket #2832. (CVS 4619) check-in: c0689409 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/printf.c.

70
71
72
73
74
75
76

77
78
79
80
81
82
83
...
129
130
131
132
133
134
135

136
137
138
139
140
141
142
...
380
381
382
383
384
385
386

387
388
389
390
391
392
393
...
406
407
408
409
410
411
412



413
414
415
416
417
418
419
420
421
422







423
424
425
426
427
428
429
#define etSQLESCAPE  11 /* Strings with '\'' doubled.  %q */
#define etSQLESCAPE2 12 /* Strings with '\'' doubled and enclosed in '',
                          NULL pointers replaced by SQL NULL.  %Q */
#define etTOKEN      13 /* a pointer to a Token structure */
#define etSRCLIST    14 /* a pointer to a SrcList */
#define etPOINTER    15 /* The %p conversion */
#define etSQLESCAPE3 16 /* %w -> Strings with '\"' doubled */



/*
** An "etByte" is an 8-bit unsigned value.
*/
typedef unsigned char etByte;

................................................................................
#endif
  {  'i', 10, 1, etRADIX,      0,  0 },
  {  'n',  0, 0, etSIZE,       0,  0 },
  {  '%',  0, 0, etPERCENT,    0,  0 },
  {  'p', 16, 0, etPOINTER,    0,  1 },
  {  'T',  0, 2, etTOKEN,      0,  0 },
  {  'S',  0, 2, etSRCLIST,    0,  0 },

};
#define etNINFO  (sizeof(fmtinfo)/sizeof(fmtinfo[0]))

/*
** If SQLITE_OMIT_FLOATING_POINT is defined, then none of the floating point
** conversions will work.
*/
................................................................................
    **   infop                       Pointer to the appropriate info struct.
    */
    switch( xtype ){
      case etPOINTER:
        flag_longlong = sizeof(char*)==sizeof(i64);
        flag_long = sizeof(char*)==sizeof(long int);
        /* Fall through into the next case */

      case etRADIX:
        if( infop->flags & FLAG_SIGNED ){
          i64 v;
          if( flag_longlong )   v = va_arg(ap,i64);
          else if( flag_long )  v = va_arg(ap,long int);
          else                  v = va_arg(ap,int);
          if( v<0 ){
................................................................................
          prefix = 0;
        }
        if( longvalue==0 ) flag_alternateform = 0;
        if( flag_zeropad && precision<width-(prefix!=0) ){
          precision = width-(prefix!=0);
        }
        bufpt = &buf[etBUFSIZE-1];



        {
          register const char *cset;      /* Use registers for speed */
          register int base;
          cset = &aDigits[infop->charset];
          base = infop->base;
          do{                                           /* Convert to ascii */
            *(--bufpt) = cset[longvalue%base];
            longvalue = longvalue/base;
          }while( longvalue>0 );
        }







        length = &buf[etBUFSIZE-1]-bufpt;
        for(idx=precision-length; idx>0; idx--){
          *(--bufpt) = '0';                             /* Zero pad */
        }
        if( prefix ) *(--bufpt) = prefix;               /* Add sign */
        if( flag_alternateform && infop->prefix ){      /* Add "0" or "0x" */
          const char *pre;







>







 







>







 







>







 







>
>
>










>
>
>
>
>
>
>







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
...
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
...
409
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
#define etSQLESCAPE  11 /* Strings with '\'' doubled.  %q */
#define etSQLESCAPE2 12 /* Strings with '\'' doubled and enclosed in '',
                          NULL pointers replaced by SQL NULL.  %Q */
#define etTOKEN      13 /* a pointer to a Token structure */
#define etSRCLIST    14 /* a pointer to a SrcList */
#define etPOINTER    15 /* The %p conversion */
#define etSQLESCAPE3 16 /* %w -> Strings with '\"' doubled */
#define etORDINAL    17 /* %r -> 1st, 2nd, 3rd, 4th, etc.  English only */


/*
** An "etByte" is an 8-bit unsigned value.
*/
typedef unsigned char etByte;

................................................................................
#endif
  {  'i', 10, 1, etRADIX,      0,  0 },
  {  'n',  0, 0, etSIZE,       0,  0 },
  {  '%',  0, 0, etPERCENT,    0,  0 },
  {  'p', 16, 0, etPOINTER,    0,  1 },
  {  'T',  0, 2, etTOKEN,      0,  0 },
  {  'S',  0, 2, etSRCLIST,    0,  0 },
  {  'r', 10, 3, etORDINAL,    0,  0 },
};
#define etNINFO  (sizeof(fmtinfo)/sizeof(fmtinfo[0]))

/*
** If SQLITE_OMIT_FLOATING_POINT is defined, then none of the floating point
** conversions will work.
*/
................................................................................
    **   infop                       Pointer to the appropriate info struct.
    */
    switch( xtype ){
      case etPOINTER:
        flag_longlong = sizeof(char*)==sizeof(i64);
        flag_long = sizeof(char*)==sizeof(long int);
        /* Fall through into the next case */
      case etORDINAL:
      case etRADIX:
        if( infop->flags & FLAG_SIGNED ){
          i64 v;
          if( flag_longlong )   v = va_arg(ap,i64);
          else if( flag_long )  v = va_arg(ap,long int);
          else                  v = va_arg(ap,int);
          if( v<0 ){
................................................................................
          prefix = 0;
        }
        if( longvalue==0 ) flag_alternateform = 0;
        if( flag_zeropad && precision<width-(prefix!=0) ){
          precision = width-(prefix!=0);
        }
        bufpt = &buf[etBUFSIZE-1];
        if( xtype==etORDINAL ){
          bufpt -= 2;
        }
        {
          register const char *cset;      /* Use registers for speed */
          register int base;
          cset = &aDigits[infop->charset];
          base = infop->base;
          do{                                           /* Convert to ascii */
            *(--bufpt) = cset[longvalue%base];
            longvalue = longvalue/base;
          }while( longvalue>0 );
        }
        if( xtype==etORDINAL ){
          static const char zOrd[] = "thstndrd";
          int x = buf[etBUFSIZE-4] - '0';
          if( x>=4 ) x = 0;
          buf[etBUFSIZE-3] = zOrd[x*2];
          buf[etBUFSIZE-2] = zOrd[x*2+1];
        }
        length = &buf[etBUFSIZE-1]-bufpt;
        for(idx=precision-length; idx>0; idx--){
          *(--bufpt) = '0';                             /* Zero pad */
        }
        if( prefix ) *(--bufpt) = prefix;               /* Add sign */
        if( flag_alternateform && infop->prefix ){      /* Add "0" or "0x" */
          const char *pre;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1408
1409
1410
1411
1412
1413
1414
1415
1416

1417
1418
1419
1420
1421
1422
1423
1424








1425
1426
1427
1428
1429
1430






1431
1432
1433
1434
1435



1436
1437
1438












1439
1440
1441









1442
1443
1444
1445




1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460

1461
1462
1463


1464
1465
1466
1467
1468
1469
1470
1471
1472
1473










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
....
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
....
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
....
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
....
2719
2720
2721
2722
2723
2724
2725
2726

2727
2728

2729
2730
2731
2732
2733
2734
2735
2736
....
2941
2942
2943
2944
2945
2946
2947









2948
2949
2950
2951
2952
2953
2954
....
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
**    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.365 2007/12/10 18:51:48 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM;
  }
  return rc;
}

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*

** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
**
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.








*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable              /* Insert this value in iTable */






){
  int nErr = 0;
  int i, j;
  sqlite3 *db = pParse->db;
  int nExpr;




  if( pSelect==0 || pOrderBy==0 ) return 1;
  if( sqlite3SelectResolve(pParse, pSelect, 0) ){












    return 1;
  }










  nExpr = pSelect->pEList->nExpr;
  for(i=0; nErr==0 && i<pOrderBy->nExpr; i++){
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;





    if( sqlite3ExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>nExpr ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY position %d should be between 1 and %d",
          iCol, nExpr);
        nErr++;
        break;
      }
      iCol--;
    }else{
      Select *p;
      for(p=pSelect; p; p=p->pPrior){
        ExprList *pEList = p->pEList;
        Expr *pDup = sqlite3ExprDup(db, pE);


        NameContext nc;



        memset(&nc, 0, sizeof(nc));
        nc.pParse = pParse;
        nc.pSrcList = p->pSrc;
        nc.pEList = pEList;
        nc.allowAgg = 1;
        nc.nErr = 0;
        if( sqlite3ExprResolveNames(&nc, pDup) ){
          sqlite3ErrorClear(pParse);
        }else{
          struct ExprList_item *pItem;










          for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){
            if( sqlite3ExprCompare(pItem->pExpr, pDup) ){
              if( iCol>=0 && iCol!=j ){



                sqlite3ErrorMsg(
                    pParse, "ORDER BY term number %d is ambiguous", i+1
                );


              }else{
                iCol = j;

              }
            }
          }
        }
        sqlite3ExprDelete(pDup);



















      }





    }








    if( iCol<0 ){




      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
    }else{
      pE->op = TK_COLUMN;
      pE->iTable = iTable;
      pE->iAgg = -1;
      pE->iColumn = iCol;









      pOrderBy->a[i].done = 1;



    }

    if( pParse->nErr ){

      return pParse->nErr;
    }
  }





























  return SQLITE_OK;
}
#endif /* #ifndef SQLITE_OMIT_COMPOUND_SELECT */





























/*
** Get a VDBE for the given parser context.  Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
*/
Vdbe *sqlite3GetVdbe(Parse *pParse){
  Vdbe *v = pParse->pVdbe;
................................................................................
        */
        unionTab = iParm;
      }else{
        /* We will need to create our own temporary table to hold the
        ** intermediate results.
        */
        unionTab = pParse->nTab++;
        if( pOrderBy && matchOrderbyToColumn(pParse, p, pOrderBy, unionTab) ){
          rc = 1;
          goto multi_select_end;
        }
        addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, unionTab, 0);
        if( priorOp==SRT_Table ){
          assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
          aSetP2[nSetP2++] = addr;
................................................................................

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
      tab2 = pParse->nTab++;
      if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,tab1) ){
        rc = 1;
        goto multi_select_end;
      }
      createSortingIndex(pParse, p, pOrderBy);

      addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, tab1, 0);
      assert( p->addrOpenEphm[0] == -1 );
................................................................................
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp(v, OP_Close, base, 0);
  
  return 1;
}

/*
** Analyze and ORDER BY or GROUP BY clause in a SELECT statement.  Return
** the number of errors seen.
**
** An ORDER BY or GROUP BY is a list of expressions.  If any expression
** is an integer constant, then that expression is replaced by the
** corresponding entry in the result set.
*/
static int processOrderGroupBy(
  NameContext *pNC,     /* Name context of the SELECT statement. */
  ExprList *pOrderBy,   /* The ORDER BY or GROUP BY clause to be processed */
  const char *zType     /* Either "ORDER" or "GROUP", as appropriate */
){
  int i;
  ExprList *pEList = pNC->pEList;     /* The result set of the SELECT */
  Parse *pParse = pNC->pParse;     /* The result set of the SELECT */
  assert( pEList );

  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    sqlite3ErrorMsg(pParse, "too many terms in %s BY clause", zType);
    return 1;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    int iCol;
    Expr *pE = pOrderBy->a[i].pExpr;
    if( sqlite3ExprIsInteger(pE, &iCol) ){
      if( iCol>0 && iCol<=pEList->nExpr ){
        CollSeq *pColl = pE->pColl;
        int flags = pE->flags & EP_ExpCollate;
        sqlite3ExprDelete(pE);
        pE = sqlite3ExprDup(pParse->db, pEList->a[iCol-1].pExpr);
        pOrderBy->a[i].pExpr = pE;
        if( pColl && flags ){
          pE->pColl = pColl;
          pE->flags |= flags;
        }
      }else{
        sqlite3ErrorMsg(pParse, 
           "%s BY column number %d out of range - should be "
           "between 1 and %d", zType, iCol, pEList->nExpr);
        return 1;
      }
    }
    if( sqlite3ExprResolveNames(pNC, pE) ){
      return 1;
    }
  }
  return 0;
}

/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/
int sqlite3SelectResolve(
................................................................................
  ** re-evaluated for each reference to it.
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 && processOrderGroupBy(&sNC, p->pOrderBy, "ORDER") ){

    return SQLITE_ERROR;
  }

  if( processOrderGroupBy(&sNC, pGroupBy, "GROUP") ){
    return SQLITE_ERROR;
  }

  if( pParse->db->mallocFailed ){
    return SQLITE_NOMEM;
  }

................................................................................

  db = pParse->db;
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));










#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop;
................................................................................
        return 1;
      }
    }
    return multiSelect(pParse, p, eDest, iParm, aff);
  }
#endif

  pOrderBy = p->pOrderBy;
  if( IgnorableOrderby(eDest) ){
    p->pOrderBy = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;

  /* Make local copies of the parameters for this query.
  */
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isAgg = p->isAgg;







|







 







<

>
|
|
|
|
|

|
|
>
>
>
>
>
>
>
>

|
<
<
<
<
>
>
>
>
>
>

<
<
<
<
>
>
>

<
<
>
>
>
>
>
>
>
>
>
>
>
>
|


>
>
>
>
>
>
>
>
>
|
<
<
<
>
>
>
>
|
<
<
<
<
<
<
<
|
<
<
<
<
<
<
>
|
<

>
>
|
|
|
|
|
|
|
<
<
<
>
>
>
>
>
>
>
>
>
>
|
|
<
>
>
>
|
<
<
>
>
|
<
>
|
|
|
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
|
>
>
>
|
>
>
>
>

>
>
>
>
|
<
<
<
<
<
<
>
>
>
>
>
>
>
>
>
|
>
>
>
|
|
<
>
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
|
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







|







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|
>
|
|
>
|







 







>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1408
1409
1410
1411
1412
1413
1414

1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434




1435
1436
1437
1438
1439
1440
1441




1442
1443
1444
1445


1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470



1471
1472
1473
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
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552






1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567

1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602

1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
....
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
....
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
....
2697
2698
2699
2700
2701
2702
2703



















































2704
2705
2706
2707
2708
2709
2710
....
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
....
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
....
3043
3044
3045
3046
3047
3048
3049









3050
3051
3052
3053
3054
3055
3056
**    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.366 2007/12/13 02:45:31 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM;
  }
  return rc;
}


/*
** pE is a pointer to an expression which is a single term in
** ORDER BY or GROUP BY clause.
**
** If pE evaluates to an integer constant i, then return i.
** This is an indication to the caller that it should sort
** by the i-th column of the result set.
**
** If pE is a well-formed expression and the SELECT statement
** is not compound, then return 0.  This indicates to the
** caller that it should sort by the value of the ORDER BY
** expression.
**
** If the SELECT is compound, then attempt to match pE against
** result set columns in the left-most SELECT statement.  Return
** the index i of the matching column, as an indication to the 
** caller that it should sort by the i-th column.  If there is
** no match, return -1 and leave an error message in pParse.
*/
static int matchOrderByTermToExprList(




  Parse *pParse,     /* Parsing context for error messages */
  Select *pSelect,   /* The SELECT statement with the ORDER BY clause */
  Expr *pE,          /* The specific ORDER BY term */
  int idx,           /* When ORDER BY term is this */
  int isCompound,    /* True if this is a compound SELECT */
  u8 *pHasAgg        /* True if expression contains aggregate functions */
){




  int i;             /* Loop counter */
  ExprList *pEList;  /* The columns of the result set */
  NameContext nc;    /* Name context for resolving pE */




  /* If the term is an integer constant, return the value of that
  ** constant */
  pEList = pSelect->pEList;
  if( sqlite3ExprIsInteger(pE, &i) ){
    if( i<=0 ){
      /* If i is too small, make it too big.  That way the calling
      ** function still sees a value that is out of range, but does
      ** not confuse the column number with 0 or -1 result code.
      */
      i = pEList->nExpr+1;
    }
    return i;
  }

  /* If the term is a simple identifier that try to match that identifier
  ** against a column name in the result set.
  */
  if( pE->op==TK_ID || (pE->op==TK_STRING && pE->token.z[0]!='\'') ){
    sqlite3 *db = pParse->db;
    char *zCol = sqlite3NameFromToken(db, &pE->token);
    if( db->mallocFailed ){
      return -1;
    }
    for(i=0; i<pEList->nExpr; i++){



      char *zAs = pEList->a[i].zName;
      if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
        sqlite3_free(zCol);
        return i+1;
      }







    }






    sqlite3_free(zCol);
  }


  /* Resolve all names in the ORDER BY term expression
  */
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList;
  nc.allowAgg = 1;
  nc.nErr = 0;
  if( sqlite3ExprResolveNames(&nc, pE) ){



    return -1;
  }
  if( nc.hasAgg && pHasAgg ){
    *pHasAgg = 1;
  }

  /* For a compound SELECT, we need to try to match the ORDER BY
  ** expression against an expression in the result set
  */
  if( isCompound ){
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprCompare(pEList->a[i].pExpr, pE) ){

        return i+1;
      }
    }
    sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any "


       "column in the result set of the left-most SELECT", idx);
    return -1;
  }else{

    return 0;
  }
}



/*
** Analyze and ORDER BY or GROUP BY clause in a simple SELECT statement.
** Return the number of errors seen.
**
** Every term of the ORDER BY or GROUP BY clause needs to be an
** expression.  If any expression is an integer constant, then
** that expression is replaced by the corresponding 
** expression from the result set.
*/
static int processOrderGroupBy(
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the clause */
  ExprList *pOrderBy,   /* The ORDER BY or GROUP BY clause to be processed */
  int isOrder,          /* 1 for ORDER BY.  0 for GROUP BY */
  u8 *pHasAgg           /* Set to TRUE if any term contains an aggregate */
){
  int i;
  sqlite3 *db = pParse->db;
  ExprList *pEList;

  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    const char *zType = isOrder ? "ORDER" : "GROUP";
    sqlite3ErrorMsg(pParse, "too many terms in %s BY clause", zType);
    return 1;
  }
  pEList = pSelect->pEList;
  if( pEList==0 ){
    return 0;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    int iCol;
    Expr *pE = pOrderBy->a[i].pExpr;
    iCol = matchOrderByTermToExprList(pParse, pSelect, pE, i+1, 0, pHasAgg);
    if( iCol<0 ){
      return 1;
    }
    if( iCol>pEList->nExpr ){
      const char *zType = isOrder ? "ORDER" : "GROUP";
      sqlite3ErrorMsg(pParse, 






         "%r %s BY term out of range - should be "
         "between 1 and %d", i+1, zType, pEList->nExpr);
      return 1;
    }
    if( iCol>0 ){
      CollSeq *pColl = pE->pColl;
      int flags = pE->flags & EP_ExpCollate;
      sqlite3ExprDelete(pE);
      pE = sqlite3ExprDup(db, pEList->a[iCol-1].pExpr);
      pOrderBy->a[i].pExpr = pE;
      if( pColl && flags ){
        pE->pColl = pColl;
        pE->flags |= flags;
      }
    }

  }
  return 0;
}

/*
** Analyze and ORDER BY or GROUP BY clause in a SELECT statement.  Return
** the number of errors seen.
**
** The processing depends on whether the SELECT is simple or compound.
** For a simple SELECT statement, evry term of the ORDER BY or GROUP BY
** clause needs to be an expression.  If any expression is an integer
** constant, then that expression is replaced by the corresponding 
** expression from the result set.
**
** For compound SELECT statements, every expression needs to be of
** type TK_COLUMN with a iTable value as given in the 4th parameter.
** If any expression is an integer, that becomes the column number.
** Otherwise, match the expression against result set columns from
** the left-most SELECT.
*/
static int processCompoundOrderBy(
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the ORDER BY */
  int iTable            /* Output table for compound SELECT statements */
){
  int i;
  ExprList *pOrderBy;
  ExprList *pEList;

  pOrderBy = pSelect->pOrderBy;
  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    sqlite3ErrorMsg(pParse, "too many terms in ORDER BY clause");
    return 1;
  }

  while( pSelect->pPrior ){
    pSelect = pSelect->pPrior;
  }
  pEList = pSelect->pEList;
  if( pEList==0 ){
    return 1;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    int iCol;
    Expr *pE = pOrderBy->a[i].pExpr;
    iCol = matchOrderByTermToExprList(pParse, pSelect, pE, i+1, 1, 0);
    if( iCol<0 ){
      return 1;
    }
    if( iCol>pEList->nExpr ){
      sqlite3ErrorMsg(pParse, 
         "%r ORDER BY term out of range - should be "
         "between 1 and %d", i+1, pEList->nExpr);
      return 1;
    }
    pE->op = TK_COLUMN;
    pE->iTable = iTable;
    pE->iAgg = -1;
    pE->iColumn = iCol-1;
    pE->pTab = 0;
  }
  return 0;
}

/*
** Get a VDBE for the given parser context.  Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
*/
Vdbe *sqlite3GetVdbe(Parse *pParse){
  Vdbe *v = pParse->pVdbe;
................................................................................
        */
        unionTab = iParm;
      }else{
        /* We will need to create our own temporary table to hold the
        ** intermediate results.
        */
        unionTab = pParse->nTab++;
        if( processCompoundOrderBy(pParse, p, unionTab) ){
          rc = 1;
          goto multi_select_end;
        }
        addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, unionTab, 0);
        if( priorOp==SRT_Table ){
          assert( nSetP2<sizeof(aSetP2)/sizeof(aSetP2[0]) );
          aSetP2[nSetP2++] = addr;
................................................................................

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
      tab2 = pParse->nTab++;
      if( processCompoundOrderBy(pParse, p, tab1) ){
        rc = 1;
        goto multi_select_end;
      }
      createSortingIndex(pParse, p, pOrderBy);

      addr = sqlite3VdbeAddOp(v, OP_OpenEphemeral, tab1, 0);
      assert( p->addrOpenEphm[0] == -1 );
................................................................................
  selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, brk, brk, 0);
  sqlite3VdbeResolveLabel(v, brk);
  sqlite3VdbeAddOp(v, OP_Close, base, 0);
  
  return 1;
}




















































/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
** is a sub-select, then pOuterNC is a pointer to the NameContext 
** of the parent SELECT.
*/
int sqlite3SelectResolve(
................................................................................
  ** re-evaluated for each reference to it.
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 ){
    if( processOrderGroupBy(pParse, p, p->pOrderBy, 0, &sNC.hasAgg) ){
      return SQLITE_ERROR;
    }
  }
  if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){
    return SQLITE_ERROR;
  }

  if( pParse->db->mallocFailed ){
    return SQLITE_NOMEM;
  }

................................................................................

  db = pParse->db;
  if( p==0 || db->mallocFailed || pParse->nErr ){
    return 1;
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

  pOrderBy = p->pOrderBy;
  if( IgnorableOrderby(eDest) ){
    p->pOrderBy = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop;
................................................................................
        return 1;
      }
    }
    return multiSelect(pParse, p, eDest, iParm, aff);
  }
#endif










  /* Make local copies of the parameters for this query.
  */
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isAgg = p->isAgg;

Changes to test/null.test.

149
150
151
152
153
154
155
156
157
158
159
160















161
162
163
164
165
166
167
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct
#
ifcapable compound {
do_test null-6.1 {
  execsql {
    select b from t1 union select c from t1 order by c;
  }
} {{} 0 1}















} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    execsql {







|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct
#
ifcapable compound {
  do_test null-6.1 {
    execsql {
      select b from t1 union select c from t1 order by b;
    }
  } {{} 0 1}
  do_test null-6.2 {
    execsql {
      select b from t1 union select c from t1 order by 1;
    }
  } {{} 0 1}
  do_test null-6.3 {
    execsql {
      select b from t1 union select c from t1 order by t1.b;
    }
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    execsql {