-
Notifications
You must be signed in to change notification settings - Fork 1.4k
Expand file tree
/
Copy pathselect.c
More file actions
8341 lines (7924 loc) · 302 KB
/
select.c
File metadata and controls
8341 lines (7924 loc) · 302 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
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
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
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
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
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
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
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
757
758
759
760
761
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
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
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
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
** 2001 September 15
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** 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.
*/
#include "sqliteInt.h"
/*
** An instance of the following object is used to record information about
** how to process the DISTINCT keyword, to simplify passing that information
** into the selectInnerLoop() routine.
*/
typedef struct DistinctCtx DistinctCtx;
struct DistinctCtx {
u8 isTnct; /* 0: Not distinct. 1: DISTICT 2: DISTINCT and ORDER BY */
u8 eTnctType; /* One of the WHERE_DISTINCT_* operators */
int tabTnct; /* Ephemeral table used for DISTINCT processing */
int addrTnct; /* Address of OP_OpenEphemeral opcode for tabTnct */
};
/*
** An instance of the following object is used to record information about
** the ORDER BY (or GROUP BY) clause of query is being coded.
**
** The aDefer[] array is used by the sorter-references optimization. For
** example, assuming there is no index that can be used for the ORDER BY,
** for the query:
**
** SELECT a, bigblob FROM t1 ORDER BY a LIMIT 10;
**
** it may be more efficient to add just the "a" values to the sorter, and
** retrieve the associated "bigblob" values directly from table t1 as the
** 10 smallest "a" values are extracted from the sorter.
**
** When the sorter-reference optimization is used, there is one entry in the
** aDefer[] array for each database table that may be read as values are
** extracted from the sorter.
*/
typedef struct SortCtx SortCtx;
struct SortCtx {
ExprList *pOrderBy; /* The ORDER BY (or GROUP BY clause) */
int nOBSat; /* Number of ORDER BY terms satisfied by indices */
int iECursor; /* Cursor number for the sorter */
int regReturn; /* Register holding block-output return address */
int labelBkOut; /* Start label for the block-output subroutine */
int addrSortIndex; /* Address of the OP_SorterOpen or OP_OpenEphemeral */
int labelDone; /* Jump here when done, ex: LIMIT reached */
int labelOBLopt; /* Jump here when sorter is full */
u8 sortFlags; /* Zero or more SORTFLAG_* bits */
#ifdef SQLITE_ENABLE_SORTER_REFERENCES
u8 nDefer; /* Number of valid entries in aDefer[] */
struct DeferredCsr {
Table *pTab; /* Table definition */
int iCsr; /* Cursor number for table */
int nKey; /* Number of PK columns for table pTab (>=1) */
} aDefer[4];
#endif
struct RowLoadInfo *pDeferredRowLoad; /* Deferred row loading info or NULL */
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
int addrPush; /* First instruction to push data into sorter */
int addrPushEnd; /* Last instruction that pushes data into sorter */
#endif
};
#define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */
/*
** Delete all the content of a Select structure. Deallocate the structure
** itself depending on the value of bFree
**
** If bFree==1, call sqlite3DbFree() on the p object.
** If bFree==0, Leave the first Select object unfreed
*/
static void clearSelect(sqlite3 *db, Select *p, int bFree){
assert( db!=0 );
while( p ){
Select *pPrior = p->pPrior;
sqlite3ExprListDelete(db, p->pEList);
sqlite3SrcListDelete(db, p->pSrc);
sqlite3ExprDelete(db, p->pWhere);
sqlite3ExprListDelete(db, p->pGroupBy);
sqlite3ExprDelete(db, p->pHaving);
sqlite3ExprListDelete(db, p->pOrderBy);
sqlite3ExprDelete(db, p->pLimit);
if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
#ifndef SQLITE_OMIT_WINDOWFUNC
if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
sqlite3WindowListDelete(db, p->pWinDefn);
}
while( p->pWin ){
assert( p->pWin->ppThis==&p->pWin );
sqlite3WindowUnlinkFromSelect(p->pWin);
}
#endif
if( bFree ) sqlite3DbNNFreeNN(db, p);
p = pPrior;
bFree = 1;
}
}
/*
** Initialize a SelectDest structure.
*/
void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
pDest->eDest = (u8)eDest;
pDest->iSDParm = iParm;
pDest->iSDParm2 = 0;
pDest->zAffSdst = 0;
pDest->iSdst = 0;
pDest->nSdst = 0;
}
/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
Select *sqlite3SelectNew(
Parse *pParse, /* Parsing context */
ExprList *pEList, /* which columns to include in the result */
SrcList *pSrc, /* the FROM clause -- which tables to scan */
Expr *pWhere, /* the WHERE clause */
ExprList *pGroupBy, /* the GROUP BY clause */
Expr *pHaving, /* the HAVING clause */
ExprList *pOrderBy, /* the ORDER BY clause */
u32 selFlags, /* Flag parameters, such as SF_Distinct */
Expr *pLimit /* LIMIT value. NULL means not used */
){
Select *pNew, *pAllocated;
Select standin;
pAllocated = pNew = sqlite3DbMallocRawNN(pParse->db, sizeof(*pNew) );
if( pNew==0 ){
assert( pParse->db->mallocFailed );
pNew = &standin;
}
if( pEList==0 ){
pEList = sqlite3ExprListAppend(pParse, 0,
sqlite3Expr(pParse->db,TK_ASTERISK,0));
}
pNew->pEList = pEList;
pNew->op = TK_SELECT;
pNew->selFlags = selFlags;
pNew->iLimit = 0;
pNew->iOffset = 0;
pNew->selId = ++pParse->nSelect;
pNew->addrOpenEphm[0] = -1;
pNew->addrOpenEphm[1] = -1;
pNew->nSelectRow = 0;
if( pSrc==0 ) pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*pSrc));
pNew->pSrc = pSrc;
pNew->pWhere = pWhere;
pNew->pGroupBy = pGroupBy;
pNew->pHaving = pHaving;
pNew->pOrderBy = pOrderBy;
pNew->pPrior = 0;
pNew->pNext = 0;
pNew->pLimit = pLimit;
pNew->pWith = 0;
#ifndef SQLITE_OMIT_WINDOWFUNC
pNew->pWin = 0;
pNew->pWinDefn = 0;
#endif
if( pParse->db->mallocFailed ) {
clearSelect(pParse->db, pNew, pNew!=&standin);
pAllocated = 0;
}else{
assert( pNew->pSrc!=0 || pParse->nErr>0 );
}
return pAllocated;
}
/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(sqlite3 *db, Select *p){
if( OK_IF_ALWAYS_TRUE(p) ) clearSelect(db, p, 1);
}
/*
** Return a pointer to the right-most SELECT statement in a compound.
*/
static Select *findRightmost(Select *p){
while( p->pNext ) p = p->pNext;
return p;
}
/*
** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
** type of join. Return an integer constant that expresses that type
** in terms of the following bit values:
**
** JT_INNER
** JT_CROSS
** JT_OUTER
** JT_NATURAL
** JT_LEFT
** JT_RIGHT
**
** A full outer join is the combination of JT_LEFT and JT_RIGHT.
**
** If an illegal or unsupported join type is seen, then still return
** a join type, but put an error in the pParse structure.
**
** These are the valid join types:
**
**
** pA pB pC Return Value
** ------- ----- ----- ------------
** CROSS - - JT_CROSS
** INNER - - JT_INNER
** LEFT - - JT_LEFT|JT_OUTER
** LEFT OUTER - JT_LEFT|JT_OUTER
** RIGHT - - JT_RIGHT|JT_OUTER
** RIGHT OUTER - JT_RIGHT|JT_OUTER
** FULL - - JT_LEFT|JT_RIGHT|JT_OUTER
** FULL OUTER - JT_LEFT|JT_RIGHT|JT_OUTER
** NATURAL INNER - JT_NATURAL|JT_INNER
** NATURAL LEFT - JT_NATURAL|JT_LEFT|JT_OUTER
** NATURAL LEFT OUTER JT_NATURAL|JT_LEFT|JT_OUTER
** NATURAL RIGHT - JT_NATURAL|JT_RIGHT|JT_OUTER
** NATURAL RIGHT OUTER JT_NATURAL|JT_RIGHT|JT_OUTER
** NATURAL FULL - JT_NATURAL|JT_LEFT|JT_RIGHT
** NATURAL FULL OUTER JT_NATRUAL|JT_LEFT|JT_RIGHT
**
** To preserve historical compatibly, SQLite also accepts a variety
** of other non-standard and in many cases non-sensical join types.
** This routine makes as much sense at it can from the nonsense join
** type and returns a result. Examples of accepted nonsense join types
** include but are not limited to:
**
** INNER CROSS JOIN -> same as JOIN
** NATURAL CROSS JOIN -> same as NATURAL JOIN
** OUTER LEFT JOIN -> same as LEFT JOIN
** LEFT NATURAL JOIN -> same as NATURAL LEFT JOIN
** LEFT RIGHT JOIN -> same as FULL JOIN
** RIGHT OUTER FULL JOIN -> same as FULL JOIN
** CROSS CROSS CROSS JOIN -> same as JOIN
**
** The only restrictions on the join type name are:
**
** * "INNER" cannot appear together with "OUTER", "LEFT", "RIGHT",
** or "FULL".
**
** * "CROSS" cannot appear together with "OUTER", "LEFT", "RIGHT,
** or "FULL".
**
** * If "OUTER" is present then there must also be one of
** "LEFT", "RIGHT", or "FULL"
*/
int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
int jointype = 0;
Token *apAll[3];
Token *p;
/* 0123456789 123456789 123456789 123 */
static const char zKeyText[] = "naturaleftouterightfullinnercross";
static const struct {
u8 i; /* Beginning of keyword text in zKeyText[] */
u8 nChar; /* Length of the keyword in characters */
u8 code; /* Join type mask */
} aKeyword[] = {
/* (0) natural */ { 0, 7, JT_NATURAL },
/* (1) left */ { 6, 4, JT_LEFT|JT_OUTER },
/* (2) outer */ { 10, 5, JT_OUTER },
/* (3) right */ { 14, 5, JT_RIGHT|JT_OUTER },
/* (4) full */ { 19, 4, JT_LEFT|JT_RIGHT|JT_OUTER },
/* (5) inner */ { 23, 5, JT_INNER },
/* (6) cross */ { 28, 5, JT_INNER|JT_CROSS },
};
int i, j;
apAll[0] = pA;
apAll[1] = pB;
apAll[2] = pC;
for(i=0; i<3 && apAll[i]; i++){
p = apAll[i];
for(j=0; j<ArraySize(aKeyword); j++){
if( p->n==aKeyword[j].nChar
&& sqlite3StrNICmp((char*)p->z, &zKeyText[aKeyword[j].i], p->n)==0 ){
jointype |= aKeyword[j].code;
break;
}
}
testcase( j==0 || j==1 || j==2 || j==3 || j==4 || j==5 || j==6 );
if( j>=ArraySize(aKeyword) ){
jointype |= JT_ERROR;
break;
}
}
if(
(jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
(jointype & JT_ERROR)!=0 ||
(jointype & (JT_OUTER|JT_LEFT|JT_RIGHT))==JT_OUTER
){
const char *zSp1 = " ";
const char *zSp2 = " ";
if( pB==0 ){ zSp1++; }
if( pC==0 ){ zSp2++; }
sqlite3ErrorMsg(pParse, "unknown join type: "
"%T%s%T%s%T", pA, zSp1, pB, zSp2, pC);
jointype = JT_INNER;
}
return jointype;
}
/*
** Return the index of a column in a table. Return -1 if the column
** is not contained in the table.
*/
int sqlite3ColumnIndex(Table *pTab, const char *zCol){
int i;
u8 h = sqlite3StrIHash(zCol);
Column *pCol;
for(pCol=pTab->aCol, i=0; i<pTab->nCol; pCol++, i++){
if( pCol->hName==h && sqlite3StrICmp(pCol->zCnName, zCol)==0 ) return i;
}
return -1;
}
/*
** Mark a subquery result column as having been used.
*/
void sqlite3SrcItemColumnUsed(SrcItem *pItem, int iCol){
assert( pItem!=0 );
assert( (int)pItem->fg.isNestedFrom == IsNestedFrom(pItem->pSelect) );
if( pItem->fg.isNestedFrom ){
ExprList *pResults;
assert( pItem->pSelect!=0 );
pResults = pItem->pSelect->pEList;
assert( pResults!=0 );
assert( iCol>=0 && iCol<pResults->nExpr );
pResults->a[iCol].fg.bUsed = 1;
}
}
/*
** Search the tables iStart..iEnd (inclusive) in pSrc, looking for a
** table that has a column named zCol. The search is left-to-right.
** The first match found is returned.
**
** When found, set *piTab and *piCol to the table index and column index
** of the matching column and return TRUE.
**
** If not found, return FALSE.
*/
static int tableAndColumnIndex(
SrcList *pSrc, /* Array of tables to search */
int iStart, /* First member of pSrc->a[] to check */
int iEnd, /* Last member of pSrc->a[] to check */
const char *zCol, /* Name of the column we are looking for */
int *piTab, /* Write index of pSrc->a[] here */
int *piCol, /* Write index of pSrc->a[*piTab].pTab->aCol[] here */
int bIgnoreHidden /* Ignore hidden columns */
){
int i; /* For looping over tables in pSrc */
int iCol; /* Index of column matching zCol */
assert( iEnd<pSrc->nSrc );
assert( iStart>=0 );
assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */
for(i=iStart; i<=iEnd; i++){
iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol);
if( iCol>=0
&& (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0)
){
if( piTab ){
sqlite3SrcItemColumnUsed(&pSrc->a[i], iCol);
*piTab = i;
*piCol = iCol;
}
return 1;
}
}
return 0;
}
/*
** Set the EP_OuterON property on all terms of the given expression.
** And set the Expr.w.iJoin to iTable for every term in the
** expression.
**
** The EP_OuterON property is used on terms of an expression to tell
** the OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause. These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
** originated in the ON or USING clause.
**
** The Expr.w.iJoin tells the WHERE clause processing that the
** expression depends on table w.iJoin even if that table is not
** explicitly mentioned in the expression. That information is needed
** for cases like this:
**
** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
**
** The where clause needs to defer the handling of the t1.x=5
** term until after the t2 loop of the join. In that way, a
** NULL t2 row will be inserted whenever t1.x!=5. If we do not
** defer the handling of t1.x=5, it will be processed immediately
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
void sqlite3SetJoinExpr(Expr *p, int iTable, u32 joinFlag){
assert( joinFlag==EP_OuterON || joinFlag==EP_InnerON );
while( p ){
ExprSetProperty(p, joinFlag);
assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(p, EP_NoReduce);
p->w.iJoin = iTable;
if( p->op==TK_FUNCTION ){
assert( ExprUseXList(p) );
if( p->x.pList ){
int i;
for(i=0; i<p->x.pList->nExpr; i++){
sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable, joinFlag);
}
}
}
sqlite3SetJoinExpr(p->pLeft, iTable, joinFlag);
p = p->pRight;
}
}
/* Undo the work of sqlite3SetJoinExpr(). This is used when a LEFT JOIN
** is simplified into an ordinary JOIN, and when an ON expression is
** "pushed down" into the WHERE clause of a subquery.
**
** Convert every term that is marked with EP_OuterON and w.iJoin==iTable into
** an ordinary term that omits the EP_OuterON mark. Or if iTable<0, then
** just clear every EP_OuterON and EP_InnerON mark from the expression tree.
**
** If nullable is true, that means that Expr p might evaluate to NULL even
** if it is a reference to a NOT NULL column. This can happen, for example,
** if the table that p references is on the left side of a RIGHT JOIN.
** If nullable is true, then take care to not remove the EP_CanBeNull bit.
** See forum thread https://sqlite.org/forum/forumpost/b40696f50145d21c
*/
static void unsetJoinExpr(Expr *p, int iTable, int nullable){
while( p ){
if( iTable<0 || (ExprHasProperty(p, EP_OuterON) && p->w.iJoin==iTable) ){
ExprClearProperty(p, EP_OuterON|EP_InnerON);
if( iTable>=0 ) ExprSetProperty(p, EP_InnerON);
}
if( p->op==TK_COLUMN && p->iTable==iTable && !nullable ){
ExprClearProperty(p, EP_CanBeNull);
}
if( p->op==TK_FUNCTION ){
assert( ExprUseXList(p) );
if( p->x.pList ){
int i;
for(i=0; i<p->x.pList->nExpr; i++){
unsetJoinExpr(p->x.pList->a[i].pExpr, iTable, nullable);
}
}
}
unsetJoinExpr(p->pLeft, iTable, nullable);
p = p->pRight;
}
}
/*
** This routine processes the join information for a SELECT statement.
**
** * A NATURAL join is converted into a USING join. After that, we
** do not need to be concerned with NATURAL joins and we only have
** think about USING joins.
**
** * ON and USING clauses result in extra terms being added to the
** WHERE clause to enforce the specified constraints. The extra
** WHERE clause terms will be tagged with EP_OuterON or
** EP_InnerON so that we know that they originated in ON/USING.
**
** The terms of a FROM clause are contained in the Select.pSrc structure.
** The left most table is the first entry in Select.pSrc. The right-most
** table is the last entry. The join operator is held in the entry to
** the right. Thus entry 1 contains the join operator for the join between
** entries 0 and 1. Any ON or USING clauses associated with the join are
** also attached to the right entry.
**
** This routine returns the number of errors encountered.
*/
static int sqlite3ProcessJoin(Parse *pParse, Select *p){
SrcList *pSrc; /* All tables in the FROM clause */
int i, j; /* Loop counters */
SrcItem *pLeft; /* Left table being joined */
SrcItem *pRight; /* Right table being joined */
pSrc = p->pSrc;
pLeft = &pSrc->a[0];
pRight = &pLeft[1];
for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
Table *pRightTab = pRight->pTab;
u32 joinType;
if( NEVER(pLeft->pTab==0 || pRightTab==0) ) continue;
joinType = (pRight->fg.jointype & JT_OUTER)!=0 ? EP_OuterON : EP_InnerON;
/* If this is a NATURAL join, synthesize an approprate USING clause
** to specify which columns should be joined.
*/
if( pRight->fg.jointype & JT_NATURAL ){
IdList *pUsing = 0;
if( pRight->fg.isUsing || pRight->u3.pOn ){
sqlite3ErrorMsg(pParse, "a NATURAL join may not have "
"an ON or USING clause", 0);
return 1;
}
for(j=0; j<pRightTab->nCol; j++){
char *zName; /* Name of column in the right table */
if( IsHiddenColumn(&pRightTab->aCol[j]) ) continue;
zName = pRightTab->aCol[j].zCnName;
if( tableAndColumnIndex(pSrc, 0, i, zName, 0, 0, 1) ){
pUsing = sqlite3IdListAppend(pParse, pUsing, 0);
if( pUsing ){
assert( pUsing->nId>0 );
assert( pUsing->a[pUsing->nId-1].zName==0 );
pUsing->a[pUsing->nId-1].zName = sqlite3DbStrDup(pParse->db, zName);
}
}
}
if( pUsing ){
pRight->fg.isUsing = 1;
pRight->fg.isSynthUsing = 1;
pRight->u3.pUsing = pUsing;
}
if( pParse->nErr ) return 1;
}
/* Create extra terms on the WHERE clause for each column named
** in the USING clause. Example: If the two tables to be joined are
** A and B and the USING clause names X, Y, and Z, then add this
** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
** Report an error if any column mentioned in the USING clause is
** not contained in both tables to be joined.
*/
if( pRight->fg.isUsing ){
IdList *pList = pRight->u3.pUsing;
sqlite3 *db = pParse->db;
assert( pList!=0 );
for(j=0; j<pList->nId; j++){
char *zName; /* Name of the term in the USING clause */
int iLeft; /* Table on the left with matching column name */
int iLeftCol; /* Column number of matching column on the left */
int iRightCol; /* Column number of matching column on the right */
Expr *pE1; /* Reference to the column on the LEFT of the join */
Expr *pE2; /* Reference to the column on the RIGHT of the join */
Expr *pEq; /* Equality constraint. pE1 == pE2 */
zName = pList->a[j].zName;
iRightCol = sqlite3ColumnIndex(pRightTab, zName);
if( iRightCol<0
|| tableAndColumnIndex(pSrc, 0, i, zName, &iLeft, &iLeftCol,
pRight->fg.isSynthUsing)==0
){
sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
"not present in both tables", zName);
return 1;
}
pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
if( (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
/* This branch runs if the query contains one or more RIGHT or FULL
** JOINs. If only a single table on the left side of this join
** contains the zName column, then this branch is a no-op.
** But if there are two or more tables on the left side
** of the join, construct a coalesce() function that gathers all
** such tables. Raise an error if more than one of those references
** to zName is not also within a prior USING clause.
**
** We really ought to raise an error if there are two or more
** non-USING references to zName on the left of an INNER or LEFT
** JOIN. But older versions of SQLite do not do that, so we avoid
** adding a new error so as to not break legacy applications.
*/
ExprList *pFuncArgs = 0; /* Arguments to the coalesce() */
static const Token tkCoalesce = { "coalesce", 8 };
while( tableAndColumnIndex(pSrc, iLeft+1, i, zName, &iLeft, &iLeftCol,
pRight->fg.isSynthUsing)!=0 ){
if( pSrc->a[iLeft].fg.isUsing==0
|| sqlite3IdListIndex(pSrc->a[iLeft].u3.pUsing, zName)<0
){
sqlite3ErrorMsg(pParse, "ambiguous reference to %s in USING()",
zName);
break;
}
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
}
if( pFuncArgs ){
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
}
}
pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
sqlite3SrcItemColumnUsed(pRight, iRightCol);
pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2);
assert( pE2!=0 || pEq==0 );
if( pEq ){
ExprSetProperty(pEq, joinType);
assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(pEq, EP_NoReduce);
pEq->w.iJoin = pE2->iTable;
}
p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pEq);
}
}
/* Add the ON clause to the end of the WHERE clause, connected by
** an AND operator.
*/
else if( pRight->u3.pOn ){
sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor, joinType);
p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->u3.pOn);
pRight->u3.pOn = 0;
pRight->fg.isOn = 1;
}
}
return 0;
}
/*
** An instance of this object holds information (beyond pParse and pSelect)
** needed to load the next result row that is to be added to the sorter.
*/
typedef struct RowLoadInfo RowLoadInfo;
struct RowLoadInfo {
int regResult; /* Store results in array of registers here */
u8 ecelFlags; /* Flag argument to ExprCodeExprList() */
#ifdef SQLITE_ENABLE_SORTER_REFERENCES
ExprList *pExtra; /* Extra columns needed by sorter refs */
int regExtraResult; /* Where to load the extra columns */
#endif
};
/*
** This routine does the work of loading query data into an array of
** registers so that it can be added to the sorter.
*/
static void innerLoopLoadRow(
Parse *pParse, /* Statement under construction */
Select *pSelect, /* The query being coded */
RowLoadInfo *pInfo /* Info needed to complete the row load */
){
sqlite3ExprCodeExprList(pParse, pSelect->pEList, pInfo->regResult,
0, pInfo->ecelFlags);
#ifdef SQLITE_ENABLE_SORTER_REFERENCES
if( pInfo->pExtra ){
sqlite3ExprCodeExprList(pParse, pInfo->pExtra, pInfo->regExtraResult, 0, 0);
sqlite3ExprListDelete(pParse->db, pInfo->pExtra);
}
#endif
}
/*
** Code the OP_MakeRecord instruction that generates the entry to be
** added into the sorter.
**
** Return the register in which the result is stored.
*/
static int makeSorterRecord(
Parse *pParse,
SortCtx *pSort,
Select *pSelect,
int regBase,
int nBase
){
int nOBSat = pSort->nOBSat;
Vdbe *v = pParse->pVdbe;
int regOut = ++pParse->nMem;
if( pSort->pDeferredRowLoad ){
innerLoopLoadRow(pParse, pSelect, pSort->pDeferredRowLoad);
}
sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regOut);
return regOut;
}
/*
** Generate code that will push the record in registers regData
** through regData+nData-1 onto the sorter.
*/
static void pushOntoSorter(
Parse *pParse, /* Parser context */
SortCtx *pSort, /* Information about the ORDER BY clause */
Select *pSelect, /* The whole SELECT statement */
int regData, /* First register holding data to be sorted */
int regOrigData, /* First register holding data before packing */
int nData, /* Number of elements in the regData data array */
int nPrefixReg /* No. of reg prior to regData available for use */
){
Vdbe *v = pParse->pVdbe; /* Stmt under construction */
int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0);
int nExpr = pSort->pOrderBy->nExpr; /* No. of ORDER BY terms */
int nBase = nExpr + bSeq + nData; /* Fields in sorter record */
int regBase; /* Regs for sorter record */
int regRecord = 0; /* Assembled sorter record */
int nOBSat = pSort->nOBSat; /* ORDER BY terms to skip */
int op; /* Opcode to add sorter record to sorter */
int iLimit; /* LIMIT counter */
int iSkip = 0; /* End of the sorter insert loop */
assert( bSeq==0 || bSeq==1 );
/* Three cases:
** (1) The data to be sorted has already been packed into a Record
** by a prior OP_MakeRecord. In this case nData==1 and regData
** will be completely unrelated to regOrigData.
** (2) All output columns are included in the sort record. In that
** case regData==regOrigData.
** (3) Some output columns are omitted from the sort record due to
** the SQLITE_ENABLE_SORTER_REFERENCES optimization, or due to the
** SQLITE_ECEL_OMITREF optimization, or due to the
** SortCtx.pDeferredRowLoad optimiation. In any of these cases
** regOrigData is 0 to prevent this routine from trying to copy
** values that might not yet exist.
*/
assert( nData==1 || regData==regOrigData || regOrigData==0 );
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
pSort->addrPush = sqlite3VdbeCurrentAddr(v);
#endif
if( nPrefixReg ){
assert( nPrefixReg==nExpr+bSeq );
regBase = regData - nPrefixReg;
}else{
regBase = pParse->nMem + 1;
pParse->nMem += nBase;
}
assert( pSelect->iOffset==0 || pSelect->iLimit!=0 );
iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit;
pSort->labelDone = sqlite3VdbeMakeLabel(pParse);
sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, regOrigData,
SQLITE_ECEL_DUP | (regOrigData? SQLITE_ECEL_REF : 0));
if( bSeq ){
sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
}
if( nPrefixReg==0 && nData>0 ){
sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
}
if( nOBSat>0 ){
int regPrevKey; /* The first nOBSat columns of the previous row */
int addrFirst; /* Address of the OP_IfNot opcode */
int addrJmp; /* Address of the OP_Jump opcode */
VdbeOp *pOp; /* Opcode that opens the sorter */
int nKey; /* Number of sorting key columns, including OP_Sequence */
KeyInfo *pKI; /* Original KeyInfo on the sorter table */
regRecord = makeSorterRecord(pParse, pSort, pSelect, regBase, nBase);
regPrevKey = pParse->nMem+1;
pParse->nMem += pSort->nOBSat;
nKey = nExpr - pSort->nOBSat + bSeq;
if( bSeq ){
addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr);
}else{
addrFirst = sqlite3VdbeAddOp1(v, OP_SequenceTest, pSort->iECursor);
}
VdbeCoverage(v);
sqlite3VdbeAddOp3(v, OP_Compare, regPrevKey, regBase, pSort->nOBSat);
pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex);
if( pParse->db->mallocFailed ) return;
pOp->p2 = nKey + nData;
pKI = pOp->p4.pKeyInfo;
memset(pKI->aSortFlags, 0, pKI->nKeyField); /* Makes OP_Jump testable */
sqlite3VdbeChangeP4(v, -1, (char*)pKI, P4_KEYINFO);
testcase( pKI->nAllField > pKI->nKeyField+2 );
pOp->p4.pKeyInfo = sqlite3KeyInfoFromExprList(pParse,pSort->pOrderBy,nOBSat,
pKI->nAllField-pKI->nKeyField-1);
pOp = 0; /* Ensure pOp not used after sqltie3VdbeAddOp3() */
addrJmp = sqlite3VdbeCurrentAddr(v);
sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v);
pSort->labelBkOut = sqlite3VdbeMakeLabel(pParse);
pSort->regReturn = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor);
if( iLimit ){
sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, pSort->labelDone);
VdbeCoverage(v);
}
sqlite3VdbeJumpHere(v, addrFirst);
sqlite3ExprCodeMove(pParse, regBase, regPrevKey, pSort->nOBSat);
sqlite3VdbeJumpHere(v, addrJmp);
}
if( iLimit ){
/* At this point the values for the new sorter entry are stored
** in an array of registers. They need to be composed into a record
** and inserted into the sorter if either (a) there are currently
** less than LIMIT+OFFSET items or (b) the new record is smaller than
** the largest record currently in the sorter. If (b) is true and there
** are already LIMIT+OFFSET items in the sorter, delete the largest
** entry before inserting the new one. This way there are never more
** than LIMIT+OFFSET items in the sorter.
**
** If the new record does not need to be inserted into the sorter,
** jump to the next iteration of the loop. If the pSort->labelOBLopt
** value is not zero, then it is a label of where to jump. Otherwise,
** just bypass the row insert logic. See the header comment on the
** sqlite3WhereOrderByLimitOptLabel() function for additional info.
*/
int iCsr = pSort->iECursor;
sqlite3VdbeAddOp2(v, OP_IfNotZero, iLimit, sqlite3VdbeCurrentAddr(v)+4);
VdbeCoverage(v);
sqlite3VdbeAddOp2(v, OP_Last, iCsr, 0);
iSkip = sqlite3VdbeAddOp4Int(v, OP_IdxLE,
iCsr, 0, regBase+nOBSat, nExpr-nOBSat);
VdbeCoverage(v);
sqlite3VdbeAddOp1(v, OP_Delete, iCsr);
}
if( regRecord==0 ){
regRecord = makeSorterRecord(pParse, pSort, pSelect, regBase, nBase);
}
if( pSort->sortFlags & SORTFLAG_UseSorter ){
op = OP_SorterInsert;
}else{
op = OP_IdxInsert;
}
sqlite3VdbeAddOp4Int(v, op, pSort->iECursor, regRecord,
regBase+nOBSat, nBase-nOBSat);
if( iSkip ){
sqlite3VdbeChangeP2(v, iSkip,
pSort->labelOBLopt ? pSort->labelOBLopt : sqlite3VdbeCurrentAddr(v));
}
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
pSort->addrPushEnd = sqlite3VdbeCurrentAddr(v)-1;
#endif
}
/*
** Add code to implement the OFFSET
*/
static void codeOffset(
Vdbe *v, /* Generate code into this VM */
int iOffset, /* Register holding the offset counter */
int iContinue /* Jump here to skip the current record */
){
if( iOffset>0 ){
sqlite3VdbeAddOp3(v, OP_IfPos, iOffset, iContinue, 1); VdbeCoverage(v);
VdbeComment((v, "OFFSET"));
}
}
/*
** Add code that will check to make sure the array of registers starting at
** iMem form a distinct entry. This is used by both "SELECT DISTINCT ..." and
** distinct aggregates ("SELECT count(DISTINCT <expr>) ..."). Three strategies
** are available. Which is used depends on the value of parameter eTnctType,
** as follows:
**
** WHERE_DISTINCT_UNORDERED/WHERE_DISTINCT_NOOP:
** Build an ephemeral table that contains all entries seen before and
** skip entries which have been seen before.
**
** Parameter iTab is the cursor number of an ephemeral table that must
** be opened before the VM code generated by this routine is executed.
** The ephemeral cursor table is queried for a record identical to the
** record formed by the current array of registers. If one is found,
** jump to VM address addrRepeat. Otherwise, insert a new record into
** the ephemeral cursor and proceed.
**
** The returned value in this case is a copy of parameter iTab.
**
** WHERE_DISTINCT_ORDERED:
** In this case rows are being delivered sorted order. The ephermal
** table is not required. Instead, the current set of values
** is compared against previous row. If they match, the new row
** is not distinct and control jumps to VM address addrRepeat. Otherwise,
** the VM program proceeds with processing the new row.
**
** The returned value in this case is the register number of the first
** in an array of registers used to store the previous result row so that
** it can be compared to the next. The caller must ensure that this
** register is initialized to NULL. (The fixDistinctOpenEph() routine
** will take care of this initialization.)
**
** WHERE_DISTINCT_UNIQUE:
** In this case it has already been determined that the rows are distinct.
** No special action is required. The return value is zero.
**
** Parameter pEList is the list of expressions used to generated the
** contents of each row. It is used by this routine to determine (a)
** how many elements there are in the array of registers and (b) the
** collation sequences that should be used for the comparisons if
** eTnctType is WHERE_DISTINCT_ORDERED.
*/
static int codeDistinct(
Parse *pParse, /* Parsing and code generating context */
int eTnctType, /* WHERE_DISTINCT_* value */
int iTab, /* A sorting index used to test for distinctness */
int addrRepeat, /* Jump to here if not distinct */
ExprList *pEList, /* Expression for each element */
int regElem /* First element */
){
int iRet = 0;
int nResultCol = pEList->nExpr;
Vdbe *v = pParse->pVdbe;
switch( eTnctType ){
case WHERE_DISTINCT_ORDERED: {
int i;
int iJump; /* Jump destination */
int regPrev; /* Previous row content */
/* Allocate space for the previous row */
iRet = regPrev = pParse->nMem+1;
pParse->nMem += nResultCol;
iJump = sqlite3VdbeCurrentAddr(v) + nResultCol;
for(i=0; i<nResultCol; i++){
CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[i].pExpr);
if( i<nResultCol-1 ){
sqlite3VdbeAddOp3(v, OP_Ne, regElem+i, iJump, regPrev+i);
VdbeCoverage(v);
}else{
sqlite3VdbeAddOp3(v, OP_Eq, regElem+i, addrRepeat, regPrev+i);
VdbeCoverage(v);
}
sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
}
assert( sqlite3VdbeCurrentAddr(v)==iJump || pParse->db->mallocFailed );
sqlite3VdbeAddOp3(v, OP_Copy, regElem, regPrev, nResultCol-1);
break;
}
case WHERE_DISTINCT_UNIQUE: {
/* nothing to do */
break;
}
default: {
int r1 = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, regElem, nResultCol);
VdbeCoverage(v);
sqlite3VdbeAddOp3(v, OP_MakeRecord, regElem, nResultCol, r1);
sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r1, regElem, nResultCol);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
sqlite3ReleaseTempReg(pParse, r1);
iRet = iTab;
break;
}
}
return iRet;
}
/*
** This routine runs after codeDistinct(). It makes necessary
** adjustments to the OP_OpenEphemeral opcode that the codeDistinct()
** routine made use of. This processing must be done separately since
** sometimes codeDistinct is called before the OP_OpenEphemeral is actually
** laid down.
**
** WHERE_DISTINCT_NOOP:
** WHERE_DISTINCT_UNORDERED:
**
** No adjustments necessary. This function is a no-op.
**
** WHERE_DISTINCT_UNIQUE:
**
** The ephemeral table is not needed. So change the
** OP_OpenEphemeral opcode into an OP_Noop.
**
** WHERE_DISTINCT_ORDERED:
**
** The ephemeral table is not needed. But we do need register
** iVal to be initialized to NULL. So change the OP_OpenEphemeral
** into an OP_Null on the iVal register.
*/
static void fixDistinctOpenEph(
Parse *pParse, /* Parsing and code generating context */
int eTnctType, /* WHERE_DISTINCT_* value */
int iVal, /* Value returned by codeDistinct() */
int iOpenEphAddr /* Address of OP_OpenEphemeral instruction for iTab */
){
if( pParse->nErr==0
&& (eTnctType==WHERE_DISTINCT_UNIQUE || eTnctType==WHERE_DISTINCT_ORDERED)
){
Vdbe *v = pParse->pVdbe;
sqlite3VdbeChangeToNoop(v, iOpenEphAddr);
if( sqlite3VdbeGetOp(v, iOpenEphAddr+1)->opcode==OP_Explain ){
sqlite3VdbeChangeToNoop(v, iOpenEphAddr+1);
}
if( eTnctType==WHERE_DISTINCT_ORDERED ){
/* Change the OP_OpenEphemeral to an OP_Null that sets the MEM_Cleared
** bit on the first register of the previous value. This will cause the
** OP_Ne added in codeDistinct() to always fail on the first iteration of
** the loop even if the first row is all NULLs. */
VdbeOp *pOp = sqlite3VdbeGetOp(v, iOpenEphAddr);
pOp->opcode = OP_Null;
pOp->p1 = 1;
pOp->p2 = iVal;