Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch group-by-name-resolution Excluding Merge-Ins
This is equivalent to a diff from 6484fb5a25 to 401a0ca3dd
2014-06-12
| ||
15:46 | Simplify the estLog() routine. (check-in: 6c68d758bc user: drh tags: trunk) | |
2014-06-10
| ||
20:18 | Proposed change to the fix for ticket [1c69be2dafc28b] such that legacy applications that were exploiting the older buggy behavior in SQLite continue to work. (Leaf check-in: 401a0ca3dd user: drh tags: group-by-name-resolution) | |
2014-06-09
| ||
20:39 | Avoid an unnecessary initialization of the szFile field of unixFile in the unix VFS. (check-in: 6484fb5a25 user: drh tags: trunk) | |
20:24 | Enhance the unix VFS so that it keeps track of the size of unlinked files internally and thus avoids the need to call fstat() on those files, since fstat() does not work reliably on unlinked files on some implementations of FuseFS. (check-in: 10707d3578 user: drh tags: trunk) | |
Changes to src/resolve.c.
︙ | ︙ | |||
793 794 795 796 797 798 799 800 801 802 803 804 | ** This routine checks to see if pE is a simple identifier which corresponds ** to the AS-name of one of the terms of the expression list. If it is, ** this routine return an integer between 1 and N where N is the number of ** elements in pEList, corresponding to the matching entry. If there is ** no match, or if pE is not a simple identifier, then this routine ** return 0. ** ** pEList has been resolved. pE has not. */ static int resolveAsName( Parse *pParse, /* Parsing context for error messages */ ExprList *pEList, /* List of expressions to scan */ | > > > > > > > > > > | > > | < > > > > > > > | 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 | ** This routine checks to see if pE is a simple identifier which corresponds ** to the AS-name of one of the terms of the expression list. If it is, ** this routine return an integer between 1 and N where N is the number of ** elements in pEList, corresponding to the matching entry. If there is ** no match, or if pE is not a simple identifier, then this routine ** return 0. ** ** The sameNameOnly flag is set if pE comes from a GROUP BY clause. The ** arguments of a GROUP BY clause are not supposed to be able to match ** against AS names in SQL. But early versions of SQLite allowed this ** behavior by mistake. To provide backwards compatibility, a GROUP BY ** term will match as AS alias only if the corresponding result set expression ** refers to a table column by the same name. In other words: ** ** SELECT t1.x AS x, t2.x AS y FROM t1,t2 GROUP BY x; -- match ** SELECT t1.y AS x, t2.y AS y FROM t1,t2 GROUP BY x; -- no match ** ** pEList has been resolved. pE has not. */ static int resolveAsName( Parse *pParse, /* Parsing context for error messages */ ExprList *pEList, /* List of expressions to scan */ Expr *pE, /* Expression we are trying to match */ int sameNameOnly /* Only resolve if the alias matches the column name */ ){ int i; /* Loop counter */ UNUSED_PARAMETER(pParse); if( pE->op==TK_ID ){ char *zCol = pE->u.zToken; for(i=0; i<pEList->nExpr; i++){ char *zAs = pEList->a[i].zName; if( zAs==0 ) continue; if( sqlite3StrICmp(zAs, zCol)!=0 ) continue; if( sameNameOnly ){ Expr *p = pEList->a[i].pExpr; Table *pTab; if( p->op!=TK_COLUMN ) continue; pTab = p->pTab; if( sqlite3StrICmp(pTab->aCol[p->iColumn].zName, zAs)!=0 ) continue; } return i+1; } } return 0; } /* ** pE is a pointer to an expression which is a single term in the |
︙ | ︙ | |||
950 951 952 953 954 955 956 | pE = sqlite3ExprSkipCollate(pItem->pExpr); if( sqlite3ExprIsInteger(pE, &iCol) ){ if( iCol<=0 || iCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr); return 1; } }else{ | | | 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 | pE = sqlite3ExprSkipCollate(pItem->pExpr); if( sqlite3ExprIsInteger(pE, &iCol) ){ if( iCol<=0 || iCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr); return 1; } }else{ iCol = resolveAsName(pParse, pEList, pE, 0); if( iCol==0 ){ pDup = sqlite3ExprDup(db, pE, 0); if( !db->mallocFailed ){ assert(pDup); iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup); } sqlite3ExprDelete(db, pDup); |
︙ | ︙ | |||
1071 1072 1073 1074 1075 1076 1077 | if( pOrderBy==0 ) return 0; nResult = pSelect->pEList->nExpr; pParse = pNC->pParse; for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ Expr *pE = pItem->pExpr; Expr *pE2 = sqlite3ExprSkipCollate(pE); | < | | | | | | | | < | 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 | if( pOrderBy==0 ) return 0; nResult = pSelect->pEList->nExpr; pParse = pNC->pParse; for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ Expr *pE = pItem->pExpr; Expr *pE2 = sqlite3ExprSkipCollate(pE); iCol = resolveAsName(pParse, pSelect->pEList, pE2, zType[0]=='G'); if( iCol>0 ){ /* If an AS-name match is found, mark this ORDER BY column as being ** a copy of the iCol-th result-set column. The subsequent call to ** sqlite3ResolveOrderGroupBy() will convert the expression to a ** copy of the iCol-th result-set expression. */ pItem->u.x.iOrderByCol = (u16)iCol; continue; } if( sqlite3ExprIsInteger(pE2, &iCol) ){ /* The ORDER BY term is an integer constant. Again, set the column ** number so that sqlite3ResolveOrderGroupBy() will convert the ** order-by term to a copy of the result-set expression */ if( iCol<1 || iCol>0xffff ){ resolveOutOfRangeError(pParse, zType, i+1, nResult); |
︙ | ︙ |
Changes to test/resolver01.test.
︙ | ︙ | |||
153 154 155 156 157 158 159 | # Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds # more tightly to the input tables in all cases. # # This first case case has been wrong in SQLite for time out of mind. # For SQLite version 3.7.17 the answer was two rows, which is wrong. # do_execsql_test resolver01-5.1 { | | | | | | | > > > > | > > > > > > > > > > > > > | > | 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 | # Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds # more tightly to the input tables in all cases. # # This first case case has been wrong in SQLite for time out of mind. # For SQLite version 3.7.17 the answer was two rows, which is wrong. # do_execsql_test resolver01-5.1 { CREATE TABLE t5(m CHAR(2), n); INSERT INTO t5 VALUES('ax',1); INSERT INTO t5 VALUES('bx',2); INSERT INTO t5 VALUES('cy',2); SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2; } {1 x 1 x 1 y} # This case is unambiguous and has always been correct. # do_execsql_test resolver01-5.2 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2; } {1 x 1 x 1 y} # This case is not allowed in standard SQL, but SQLite allows and does # the sensible thing. # do_execsql_test resolver01-5.3 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2; } {1 y 2 x} do_execsql_test resolver01-5.4 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY substr(m,2,1) ORDER BY 1, 2; } {1 y 2 x} # These test cases were provided in the 2013-08-14 email from Rob Golsteijn # that originally reported the problem of ticket [1c69be2dafc28]. # do_execsql_test resolver01-6.1 { CREATE TABLE t61(name); SELECT min(name) FROM t61 GROUP BY lower(name); } {} do_execsql_test resolver01-6.2 { SELECT min(name) AS name FROM t61 GROUP BY lower(name); } {} do_execsql_test resolver01-6.3 { CREATE TABLE t63(name); INSERT INTO t63 VALUES (NULL); INSERT INTO t63 VALUES ('abc'); SELECT count(), NULLIF(name,'abc') AS name FROM t63 GROUP BY lower(name); } {1 {} 1 {}} # (2014-06-10) The fix to GROUP BY name binding has resulted in errors # in some legacy Android applications. To work around this, GROUP BY terms # can be bound to AS aliases as long as the corresponding expression is # a column by the same name as the alias. Verify that this exception # case works. Continuation of ticket [1c69be2dafc28]. # do_execsql_test resolver01-7.1 { CREATE TABLE t7(m, x); INSERT INTO t7 VALUES('bx',1),('ax',2),('dx',3); SELECT count(*), t5.m AS m FROM t5, t7 GROUP BY m ORDER BY 2; } {3 ax 3 bx 3 cy} do_execsql_test resolver01-7.2 { SELECT count(*), t5.m AS n FROM t5, t7 GROUP BY n ORDER BY 2; } {3 ax 6 cy} do_test resolver01-7.3 { catchsql { CREATE TABLE t7b(m, n); INSERT INTO t7b SELECT * FROM t7; SELECT count(*), t5.m AS n FROM t5, t7b GROUP BY n ORDER BY 2; } } {1 {ambiguous column name: n}} finish_test |