/ Check-in [ab09ef4271]
Login

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

Overview
Comment:Do not use HIDDEN columns for NATURAL joins. Fix for [7c0e06b16].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ab09ef427181130be09a087b7e572ad4cfb6b3e1b459769ee5ebf046b3ead682
User & Date: dan 2019-12-30 14:32:27
Context
2019-12-30
20:42
Fix a typo in a comment. No changes to code. (check-in: d4813a8d81 user: drh tags: trunk)
14:32
Do not use HIDDEN columns for NATURAL joins. Fix for [7c0e06b16]. (check-in: ab09ef4271 user: dan tags: trunk)
06:55
In ALTER TABLE, rename columns and tables in expressions that are optimized out by the "AND 0" optimization. Doing this also fixes an otherwise harmless assert() failure. (check-in: a9e0354c99 user: dan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts5/test/fts5misc.test.

291
292
293
294
295
296
297
298



























299
300
  CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '37');
  INSERT INTO vt0(c0, c1) VALUES (0.66077, 1957391816);
}
do_execsql_test 11.1 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}




























finish_test









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


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
  CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '37');
  INSERT INTO vt0(c0, c1) VALUES (0.66077, 1957391816);
}
do_execsql_test 11.1 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
# Ticket [7c0e06b16] 
#
do_execsql_test 12.0 {
  CREATE TABLE t1(a, b, rank);
  INSERT INTO t1 VALUES('a', 'hello', '');
  INSERT INTO t1 VALUES('b', 'world', '');

  CREATE VIRTUAL TABLE ft USING fts5(a);
  INSERT INTO ft VALUES('b');
  INSERT INTO ft VALUES('y');

  CREATE TABLE t2(x, y, ft);
  INSERT INTO t2 VALUES(1, 2, 'x');
  INSERT INTO t2 VALUES(3, 4, 'b');
}

do_execsql_test 12.1 {
  SELECT * FROM t1 NATURAL JOIN ft WHERE ft MATCH('b')
} {b world {}}
do_execsql_test 12.2 {
  SELECT * FROM ft NATURAL JOIN t1 WHERE ft MATCH('b')
} {b world {}}
do_execsql_test 12.3 {
  SELECT * FROM t2 JOIN ft USING (ft)
} {3 4 b b}

finish_test

Changes to src/select.c.

309
310
311
312
313
314
315
316

317
318
319
320
321
322
323
324


325
326
327
328
329
330
331
...
482
483
484
485
486
487
488

489
490
491
492
493
494
495
496
497
498
499
...
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
....
5083
5084
5085
5086
5087
5088
5089
5090
5091
5092
5093
5094
5095
5096
5097
** If not found, return FALSE.
*/
static int tableAndColumnIndex(
  SrcList *pSrc,       /* Array of tables to search */
  int N,               /* Number of tables in pSrc->a[] to search */
  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 i;               /* For looping over tables in pSrc */
  int iCol;            /* Index of column matching zCol */

  assert( (piTab==0)==(piCol==0) );  /* Both or neither are NULL */
  for(i=0; i<N; i++){
    iCol = columnIndex(pSrc->a[i].pTab, zCol);
    if( iCol>=0 ){


      if( piTab ){
        *piTab = i;
        *piCol = iCol;
      }
      return 1;
    }
  }
................................................................................
        return 1;
      }
      for(j=0; j<pRightTab->nCol; j++){
        char *zName;   /* Name of column in the right table */
        int iLeft;     /* Matching left table */
        int iLeftCol;  /* Matching column in the left table */


        zName = pRightTab->aCol[j].zName;
        if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol) ){
          addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
                       isOuter, &p->pWhere);
        }
      }
    }

    /* Disallow both ON and USING clauses in the same join
    */
    if( pRight->pOn && pRight->pUsing ){
................................................................................
        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 */

        zName = pList->a[j].zName;
        iRightCol = columnIndex(pRightTab, zName);
        if( iRightCol<0
         || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol)
        ){
          sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
            "not present in both tables", zName);
          return 1;
        }
        addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
                     isOuter, &p->pWhere);
................................................................................
            ){
              continue;
            }
            tableSeen = 1;

            if( i>0 && zTName==0 ){
              if( (pFrom->fg.jointype & JT_NATURAL)!=0
                && tableAndColumnIndex(pTabList, i, zName, 0, 0)
              ){
                /* In a NATURAL join, omit the join columns from the 
                ** table to the right of the join */
                continue;
              }
              if( sqlite3IdListIndex(pFrom->pUsing, zName)>=0 ){
                /* In a join with a USING clause, omit columns in the







|
>







|
>
>







 







>

|

|







 







|







 







|







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
...
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
...
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
....
5087
5088
5089
5090
5091
5092
5093
5094
5095
5096
5097
5098
5099
5100
5101
** If not found, return FALSE.
*/
static int tableAndColumnIndex(
  SrcList *pSrc,       /* Array of tables to search */
  int N,               /* Number of tables in pSrc->a[] to search */
  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    /* True to ignore hidden columns */
){
  int i;               /* For looping over tables in pSrc */
  int iCol;            /* Index of column matching zCol */

  assert( (piTab==0)==(piCol==0) );  /* Both or neither are NULL */
  for(i=0; i<N; i++){
    iCol = columnIndex(pSrc->a[i].pTab, zCol);
    if( iCol>=0 
     && (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0)
    ){
      if( piTab ){
        *piTab = i;
        *piCol = iCol;
      }
      return 1;
    }
  }
................................................................................
        return 1;
      }
      for(j=0; j<pRightTab->nCol; j++){
        char *zName;   /* Name of column in the right table */
        int iLeft;     /* Matching left table */
        int iLeftCol;  /* Matching column in the left table */

        if( IsHiddenColumn(&pRightTab->aCol[j]) ) continue;
        zName = pRightTab->aCol[j].zName;
        if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 1) ){
          addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
                isOuter, &p->pWhere);
        }
      }
    }

    /* Disallow both ON and USING clauses in the same join
    */
    if( pRight->pOn && pRight->pUsing ){
................................................................................
        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 */

        zName = pList->a[j].zName;
        iRightCol = columnIndex(pRightTab, zName);
        if( iRightCol<0
         || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 0)
        ){
          sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
            "not present in both tables", zName);
          return 1;
        }
        addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
                     isOuter, &p->pWhere);
................................................................................
            ){
              continue;
            }
            tableSeen = 1;

            if( i>0 && zTName==0 ){
              if( (pFrom->fg.jointype & JT_NATURAL)!=0
                && tableAndColumnIndex(pTabList, i, zName, 0, 0, 1)
              ){
                /* In a NATURAL join, omit the join columns from the 
                ** table to the right of the join */
                continue;
              }
              if( sqlite3IdListIndex(pFrom->pUsing, zName)>=0 ){
                /* In a join with a USING clause, omit columns in the