SQLite

Changes On Branch flatten-left-join
Login
Patch Unified Diff

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

Changes In Branch flatten-left-join Excluding Merge-Ins

This is equivalent to a diff from 217fc3ebd4 to 2909f36bfb

2022-07-26
18:43
Fix another problem related to aggregate query LEFT JOIN flattening. (check-in: 8e12c6f59b user: dan tags: trunk)
15:41
Omit a unused variable initialization in order to suppress a harmless compiler warning. (check-in: 2d703c5e94 user: drh tags: trunk)
15:39
Add test cases related to fix [e717e029]. (Closed-Leaf check-in: 2909f36bfb user: dan tags: flatten-left-join)
15:32
Make sure IF_NULL_ROW expressions receive a separate slot in the sorter used to implement GROUP BY. (check-in: 2bda4fca06 user: drh tags: flatten-left-join)
14:37
Improved AggInfo.aCol debugging output, intended to debug a problem with LEFT JOIN flattening into an aggregate query with GROUP BY. (check-in: e717e029bd user: drh tags: flatten-left-join)
10:16
Add a OOM term to a single assert() statement. (check-in: 217fc3ebd4 user: drh tags: trunk)
01:20
Performance optimization: avoid unnecessary calls to computeLimitRegisters(). (check-in: f48bd8f85d user: drh tags: trunk)

Changes to src/expr.c.
︙︙
4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
4094
4095
4096
4097
4098

        return pCol->iMem;
      }else if( pAggInfo->useSortingIdx ){
        Table *pTab = pCol->pTab;
        sqlite3VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
                              pCol->iSorterColumn, target);
        if( pCol->iColumn<0 ){
          VdbeComment((v,"%s.rowid",pTab->zName));
        }else{
          VdbeComment((v,"%s.%s", 
              pTab->zName, pTab->aCol[pCol->iColumn].zCnName));
          if( pTab->aCol[pCol->iColumn].affinity==SQLITE_AFF_REAL ){
            sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
          }
        }
        return target;








|








4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
4094
4095
4096
4097
4098

        return pCol->iMem;
      }else if( pAggInfo->useSortingIdx ){
        Table *pTab = pCol->pTab;
        sqlite3VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
                              pCol->iSorterColumn, target);
        if( pCol->iColumn<0 ){
          VdbeComment((v,"%s.rowid",pTab->zName));
        }else if( ALWAYS(pTab!=0) ){
          VdbeComment((v,"%s.%s", 
              pTab->zName, pTab->aCol[pCol->iColumn].zCnName));
          if( pTab->aCol[pCol->iColumn].affinity==SQLITE_AFF_REAL ){
            sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
          }
        }
        return target;
︙︙
6223
6224
6225
6226
6227
6228
6229
6230
6231


6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243
6244
6245
6246
6247
6248
6249
6250
6251
6252
6253

6254

6255
6256
6257
6258
6259
6260
6261

            **
            ** Make an entry for the column in pAggInfo->aCol[] if there
            ** is not an entry there already.
            */
            int k;
            pCol = pAggInfo->aCol;
            for(k=0; k<pAggInfo->nColumn; k++, pCol++){
              if( pCol->iTable==pExpr->iTable &&
                  pCol->iColumn==pExpr->iColumn ){


                break;
              }
            }
            if( (k>=pAggInfo->nColumn)
             && (k = addAggInfoColumn(pParse->db, pAggInfo))>=0 
            ){
              pCol = &pAggInfo->aCol[k];
              assert( ExprUseYTab(pExpr) );
              pCol->pTab = pExpr->y.pTab;
              pCol->iTable = pExpr->iTable;
              pCol->iColumn = pExpr->iColumn;
              pCol->iMem = ++pParse->nMem;
              pCol->iSorterColumn = -1;
              pCol->pCExpr = pExpr;
              if( pAggInfo->pGroupBy ){
                int j, n;
                ExprList *pGB = pAggInfo->pGroupBy;
                struct ExprList_item *pTerm = pGB->a;
                n = pGB->nExpr;
                for(j=0; j<n; j++, pTerm++){
                  Expr *pE = pTerm->pExpr;
                  if( pE->op==TK_COLUMN && pE->iTable==pExpr->iTable &&

                      pE->iColumn==pExpr->iColumn ){

                    pCol->iSorterColumn = j;
                    break;
                  }
                }
              }
              if( pCol->iSorterColumn<0 ){
                pCol->iSorterColumn = pAggInfo->nSortingColumn++;








|
|
>
>














|






|
>
|
>








6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243
6244
6245
6246
6247
6248
6249
6250
6251
6252
6253
6254
6255
6256
6257
6258
6259
6260
6261
6262
6263
6264
6265

            **
            ** Make an entry for the column in pAggInfo->aCol[] if there
            ** is not an entry there already.
            */
            int k;
            pCol = pAggInfo->aCol;
            for(k=0; k<pAggInfo->nColumn; k++, pCol++){
              if( pCol->iTable==pExpr->iTable
               && pCol->iColumn==pExpr->iColumn
               && pExpr->op!=TK_IF_NULL_ROW
              ){
                break;
              }
            }
            if( (k>=pAggInfo->nColumn)
             && (k = addAggInfoColumn(pParse->db, pAggInfo))>=0 
            ){
              pCol = &pAggInfo->aCol[k];
              assert( ExprUseYTab(pExpr) );
              pCol->pTab = pExpr->y.pTab;
              pCol->iTable = pExpr->iTable;
              pCol->iColumn = pExpr->iColumn;
              pCol->iMem = ++pParse->nMem;
              pCol->iSorterColumn = -1;
              pCol->pCExpr = pExpr;
              if( pAggInfo->pGroupBy && pExpr->op!=TK_IF_NULL_ROW ){
                int j, n;
                ExprList *pGB = pAggInfo->pGroupBy;
                struct ExprList_item *pTerm = pGB->a;
                n = pGB->nExpr;
                for(j=0; j<n; j++, pTerm++){
                  Expr *pE = pTerm->pExpr;
                  if( pE->op==TK_COLUMN
                   && pE->iTable==pExpr->iTable
                   && pE->iColumn==pExpr->iColumn
                  ){
                    pCol->iSorterColumn = j;
                    break;
                  }
                }
              }
              if( pCol->iSorterColumn<0 ){
                pCol->iSorterColumn = pAggInfo->nSortingColumn++;
︙︙
Changes to src/select.c.
︙︙
3794
3795
3796
3797
3798
3799
3800

3801
3802
3803
3804
3805
3806
3807

      }else{
        sqlite3 *db = pSubst->pParse->db;
        if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;

          ifNullRow.flags = EP_IfNullRow;
          pCopy = &ifNullRow;
        }
        testcase( ExprHasProperty(pCopy, EP_Subquery) );
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( db->mallocFailed ){
          sqlite3ExprDelete(db, pNew);








>








3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808

      }else{
        sqlite3 *db = pSubst->pParse->db;
        if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;
          ifNullRow.iColumn = -99;
          ifNullRow.flags = EP_IfNullRow;
          pCopy = &ifNullRow;
        }
        testcase( ExprHasProperty(pCopy, EP_Subquery) );
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( db->mallocFailed ){
          sqlite3ExprDelete(db, pNew);
︙︙
7414
7415
7416
7417
7418
7419
7420

7421


7422


7423
7424
7425
7426
7427
7428
7429

      SELECTTRACE(0x400,pParse,p,("After aggregate analysis %p:\n", pAggInfo));
      sqlite3TreeViewSelect(0, p, 0);
      if( minMaxFlag ){
        sqlite3DebugPrintf("MIN/MAX Optimization (0x%02x) adds:\n", minMaxFlag);
        sqlite3TreeViewExprList(0, pMinMaxOrderBy, 0, "ORDERBY");
      }
      for(ii=0; ii<pAggInfo->nColumn; ii++){

        sqlite3DebugPrintf("agg-column[%d] iMem=%d\n",


            ii, pAggInfo->aCol[ii].iMem);


        sqlite3TreeViewExpr(0, pAggInfo->aCol[ii].pCExpr, 0);
      }
      for(ii=0; ii<pAggInfo->nFunc; ii++){
        sqlite3DebugPrintf("agg-func[%d]: iMem=%d\n",
            ii, pAggInfo->aFunc[ii].iMem);
        sqlite3TreeViewExpr(0, pAggInfo->aFunc[ii].pFExpr, 0);
      }








>
|
>
>
|
>
>








7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
7429
7430
7431
7432
7433
7434
7435

      SELECTTRACE(0x400,pParse,p,("After aggregate analysis %p:\n", pAggInfo));
      sqlite3TreeViewSelect(0, p, 0);
      if( minMaxFlag ){
        sqlite3DebugPrintf("MIN/MAX Optimization (0x%02x) adds:\n", minMaxFlag);
        sqlite3TreeViewExprList(0, pMinMaxOrderBy, 0, "ORDERBY");
      }
      for(ii=0; ii<pAggInfo->nColumn; ii++){
        struct AggInfo_col *pCol = &pAggInfo->aCol[ii];
        sqlite3DebugPrintf(
           "agg-column[%d] pTab=%s iTable=%d iColumn=%d iMem=%d"
           " iSorterColumn=%d\n",
           ii, pCol->pTab ? pCol->pTab->zName : "NULL", 
           pCol->iTable, pCol->iColumn, pCol->iMem,
           pCol->iSorterColumn);
        sqlite3TreeViewExpr(0, pAggInfo->aCol[ii].pCExpr, 0);
      }
      for(ii=0; ii<pAggInfo->nFunc; ii++){
        sqlite3DebugPrintf("agg-func[%d]: iMem=%d\n",
            ii, pAggInfo->aFunc[ii].iMem);
        sqlite3TreeViewExpr(0, pAggInfo->aFunc[ii].pFExpr, 0);
      }
︙︙
Changes to test/select3.test.
︙︙
370
371
372
373
374
375
376




377






















































378


    INSERT INTO t2 VALUES(1234);
    SELECT max(a), val FROM t1 LEFT JOIN (
        SELECT 'constant' AS val FROM t2 WHERE x=1234
    )
  } {abc constant}
}




























































finish_test









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

>

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

    INSERT INTO t2 VALUES(1234);
    SELECT max(a), val FROM t1 LEFT JOIN (
        SELECT 'constant' AS val FROM t2 WHERE x=1234
    )
  } {abc constant}
}

reset_db
do_execsql_test 12.0 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(x);
}
do_execsql_test 12.1 {
  SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
}
do_execsql_test 12.2 {
  INSERT INTO t1 VALUES(1), (1), (2), (3);
  SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
} {
  0 {}
  0 {}
  0 {}
}
do_execsql_test 12.3 {
  INSERT INTO t2 VALUES(45);
  SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
} {
  2 59
  1 59
  1 59
}
do_execsql_test 12.4 {
  INSERT INTO t2 VALUES(210);
  SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
} {
  4 59
  2 59
  2 59
}
do_execsql_test 12.5 {
  INSERT INTO t2 VALUES(NULL);
  SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
} {
  4 59
  2 59
  2 59
}
do_execsql_test 12.6 {
  DELETE FROM t2;
  DELETE FROM t1;
  INSERT INTO t1 VALUES('value');
  INSERT INTO t2 VALUES('hello');
} {}
do_execsql_test 12.7 {
  SELECT group_concat(x), m FROM t1 
    LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
} {
  hello 59
}
do_execsql_test 12.8 {
  SELECT group_concat(x), m, n FROM t1 
    LEFT JOIN (SELECT x, 59 AS m, 60 AS n FROM t2) GROUP BY a;
} {
  hello 59 60
}

finish_test