Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure USING and NATURAL work on joins even if the columns are renamed using an AS phrase. Ticket #523. (CVS 2233) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
c06add57bf0ce17503181b584ea4197b |
User & Date: | drh 2005-01-18 17:40:04.000 |
Context
2005-01-19
| ||
03:47 | A few test cases were failing on windows due to mandatory locking. Omit them on that platform. (CVS 2234) (check-in: 441316f1af user: danielk1977 tags: trunk) | |
2005-01-18
| ||
17:40 | Make sure USING and NATURAL work on joins even if the columns are renamed using an AS phrase. Ticket #523. (CVS 2233) (check-in: c06add57bf user: drh tags: trunk) | |
17:20 | CREATE TABLE ... AS ... uses short names for columns. Ticket #1036. (CVS 2232) (check-in: b1d4c42d2b user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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.228 2005/01/18 17:40:04 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | /* ** Add a term to the WHERE expression in *ppExpr that requires the ** zCol column to be equal in the two tables pTab1 and pTab2. */ static void addWhereTerm( const char *zCol, /* Name of the column */ const Table *pTab1, /* First table */ const Table *pTab2, /* Second table */ Expr **ppExpr /* Add the equality term to this expression */ ){ Token dummy; Expr *pE1a, *pE1b, *pE1c; Expr *pE2a, *pE2b, *pE2c; Expr *pE; setToken(&dummy, zCol); pE1a = sqlite3Expr(TK_ID, 0, 0, &dummy); pE2a = sqlite3Expr(TK_ID, 0, 0, &dummy); | > > > > > | > > > | | 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 | /* ** Add a term to the WHERE expression in *ppExpr that requires the ** zCol column to be equal in the two tables pTab1 and pTab2. */ static void addWhereTerm( const char *zCol, /* Name of the column */ const Table *pTab1, /* First table */ const char *zAlias1, /* Alias for first table. May be NULL */ const Table *pTab2, /* Second table */ const char *zAlias2, /* Alias for second table. May be NULL */ Expr **ppExpr /* Add the equality term to this expression */ ){ Token dummy; Expr *pE1a, *pE1b, *pE1c; Expr *pE2a, *pE2b, *pE2c; Expr *pE; setToken(&dummy, zCol); pE1a = sqlite3Expr(TK_ID, 0, 0, &dummy); pE2a = sqlite3Expr(TK_ID, 0, 0, &dummy); if( zAlias1==0 ){ zAlias1 = pTab1->zName; } setToken(&dummy, zAlias1); pE1b = sqlite3Expr(TK_ID, 0, 0, &dummy); if( zAlias2==0 ){ zAlias2 = pTab2->zName; } setToken(&dummy, zAlias2); pE2b = sqlite3Expr(TK_ID, 0, 0, &dummy); pE1c = sqlite3Expr(TK_DOT, pE1b, pE1a, 0); pE2c = sqlite3Expr(TK_DOT, pE2b, pE2a, 0); pE = sqlite3Expr(TK_EQ, pE1c, pE2c, 0); ExprSetProperty(pE, EP_FromJoin); *ppExpr = sqlite3ExprAnd(*ppExpr, pE); } |
︙ | ︙ | |||
237 238 239 240 241 242 243 | sqlite3ErrorMsg(pParse, "a NATURAL join may not have " "an ON or USING clause", 0); return 1; } for(j=0; j<pLeftTab->nCol; j++){ char *zName = pLeftTab->aCol[j].zName; if( columnIndex(pRightTab, zName)>=0 ){ | | > | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 | sqlite3ErrorMsg(pParse, "a NATURAL join may not have " "an ON or USING clause", 0); return 1; } for(j=0; j<pLeftTab->nCol; j++){ char *zName = pLeftTab->aCol[j].zName; if( columnIndex(pRightTab, zName)>=0 ){ addWhereTerm(zName, pLeftTab, pLeft->zAlias, pRightTab, pRight->zAlias, &p->pWhere); } } } /* Disallow both ON and USING clauses in the same join */ if( pLeft->pOn && pLeft->pUsing ){ |
︙ | ︙ | |||
275 276 277 278 279 280 281 | for(j=0; j<pList->nId; j++){ char *zName = pList->a[j].zName; if( columnIndex(pLeftTab, zName)<0 || columnIndex(pRightTab, zName)<0 ){ sqlite3ErrorMsg(pParse, "cannot join using column %s - column " "not present in both tables", zName); return 1; } | | > | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 | for(j=0; j<pList->nId; j++){ char *zName = pList->a[j].zName; if( columnIndex(pLeftTab, zName)<0 || columnIndex(pRightTab, zName)<0 ){ sqlite3ErrorMsg(pParse, "cannot join using column %s - column " "not present in both tables", zName); return 1; } addWhereTerm(zName, pLeftTab, pLeft->zAlias, pRightTab, pRight->zAlias, &p->pWhere); } } } return 0; } /* |
︙ | ︙ |
Changes to test/join.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for joins, including outer joins. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for joins, including outer joins. # # $Id: join.test,v 1.15 2005/01/18 17:40:04 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join-1.1 { execsql { CREATE TABLE t1(a,b,c); |
︙ | ︙ | |||
42 43 44 45 46 47 48 | } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.3.1 { execsql2 { SELECT * FROM t2 NATURAL JOIN t1; } } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} | | > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.3.1 { execsql2 { SELECT * FROM t2 NATURAL JOIN t1; } } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} do_test join-1.3.2 { execsql2 { SELECT * FROM t2 AS x NATURAL JOIN t1; } } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} do_test join-1.3.3 { execsql2 { SELECT * FROM t2 NATURAL JOIN t1 AS y; } } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} do_test join-1.4.1 { execsql2 { SELECT * FROM t1 INNER JOIN t2 USING(b,c); } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.4.2 { execsql2 { SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c); } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.4.3 { execsql2 { SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c); } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.4.4 { execsql2 { SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c); } } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} do_test join-1.5 { execsql2 { SELECT * FROM t1 INNER JOIN t2 USING(b); } } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5} do_test join-1.6 { |
︙ | ︙ |