SQLite

Check-in [d6a8b1ea5c]
Login

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

Overview
Comment:Improve the accuracy of affinity and collating sequence analysis for NATURAL JOINs to the left of RIGHT JOINs where source tables are views or subqueries.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | branch-3.50
Files: files | file ages | folders
SHA3-256: d6a8b1ea5ca7e0d4e640144a4b3ada516486564bb5922af688328399c4cc4427
User & Date: drh 2025-06-02 18:42:49.982
Context
2025-06-02
23:41
Fix JSONB edit so that when it is trying to reduce the size of an element it understands 0xf0 (8-byte) sizes. (check-in: cee2711227 user: drh tags: branch-3.50)
18:42
Improve the accuracy of affinity and collating sequence analysis for NATURAL JOINs to the left of RIGHT JOINs where source tables are views or subqueries. (check-in: d6a8b1ea5c user: drh tags: branch-3.50)
18:34
Improve the accuracy of affinity and collating sequence analysis for NATURAL JOINs to the left of RIGHT JOINs where source tables are views or subqueries. Initial problem report in forum post 829306db47. (check-in: f184d1d236 user: drh tags: trunk)
2025-06-01
21:55
Update the version number to 3.50.1 (check-in: 86f0e4eabe user: drh tags: branch-3.50)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
69
70
71
72
73
74
75
76


77
78
79
80
81
82
83

      assert( pExpr->iColumn < pExpr->iTable );
      assert( pExpr->iColumn >= 0 );
      assert( pExpr->iTable==pExpr->pLeft->x.pSelect->pEList->nExpr );
      return sqlite3ExprAffinity(
          pExpr->pLeft->x.pSelect->pEList->a[pExpr->iColumn].pExpr
      );
    }
    if( op==TK_VECTOR ){


      assert( ExprUseXList(pExpr) );
      return sqlite3ExprAffinity(pExpr->x.pList->a[0].pExpr);
    }
    if( ExprHasProperty(pExpr, EP_Skip|EP_IfNullRow) ){
      assert( pExpr->op==TK_COLLATE
           || pExpr->op==TK_IF_NULL_ROW
           || (pExpr->op==TK_REGISTER && pExpr->op2==TK_IF_NULL_ROW) );








|
>
>








69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

      assert( pExpr->iColumn < pExpr->iTable );
      assert( pExpr->iColumn >= 0 );
      assert( pExpr->iTable==pExpr->pLeft->x.pSelect->pEList->nExpr );
      return sqlite3ExprAffinity(
          pExpr->pLeft->x.pSelect->pEList->a[pExpr->iColumn].pExpr
      );
    }
    if( op==TK_VECTOR
     || (op==TK_FUNCTION && pExpr->affExpr==SQLITE_AFF_DEFER)
    ){
      assert( ExprUseXList(pExpr) );
      return sqlite3ExprAffinity(pExpr->x.pList->a[0].pExpr);
    }
    if( ExprHasProperty(pExpr, EP_Skip|EP_IfNullRow) ){
      assert( pExpr->op==TK_COLLATE
           || pExpr->op==TK_IF_NULL_ROW
           || (pExpr->op==TK_REGISTER && pExpr->op2==TK_IF_NULL_ROW) );
︙︙
262
263
264
265
266
267
268
269


270
271
272
273
274
275
276

      }
      break;
    }
    if( op==TK_CAST || op==TK_UPLUS ){
      p = p->pLeft;
      continue;
    }
    if( op==TK_VECTOR ){


      assert( ExprUseXList(p) );
      p = p->x.pList->a[0].pExpr;
      continue;
    }
    if( op==TK_COLLATE ){
      assert( !ExprHasProperty(p, EP_IntValue) );
      pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);








|
>
>








264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280

      }
      break;
    }
    if( op==TK_CAST || op==TK_UPLUS ){
      p = p->pLeft;
      continue;
    }
    if( op==TK_VECTOR
     || (op==TK_FUNCTION && p->affExpr==SQLITE_AFF_DEFER)
    ){
      assert( ExprUseXList(p) );
      p = p->x.pList->a[0].pExpr;
      continue;
    }
    if( op==TK_COLLATE ){
      assert( !ExprHasProperty(p, EP_IntValue) );
      pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);
︙︙
Changes to src/select.c.
︙︙
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641

            pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
            sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
          }
          if( pFuncArgs ){
            pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
            pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
            if( pE1 ){
              pE1->affExpr = sqlite3ExprAffinity(pFuncArgs->a[0].pExpr);
            }
          }
        }else if( (pSrc->a[i+1].fg.jointype & JT_LEFT)!=0 && pParse->nErr==0 ){
          assert( pE1!=0 );
          ExprSetProperty(pE1, EP_CanBeNull);
        }
        pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);








|








627
628
629
630
631
632
633
634
635
636
637
638
639
640
641

            pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
            sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
          }
          if( pFuncArgs ){
            pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
            pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
            if( pE1 ){
              pE1->affExpr = SQLITE_AFF_DEFER;
            }
          }
        }else if( (pSrc->a[i+1].fg.jointype & JT_LEFT)!=0 && pParse->nErr==0 ){
          assert( pE1!=0 );
          ExprSetProperty(pE1, EP_CanBeNull);
        }
        pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
︙︙
Changes to src/sqliteInt.h.
︙︙
2325
2326
2327
2328
2329
2330
2331

2332
2333
2334
2335
2336
2337
2338

#define SQLITE_AFF_NONE     0x40  /* '@' */
#define SQLITE_AFF_BLOB     0x41  /* 'A' */
#define SQLITE_AFF_TEXT     0x42  /* 'B' */
#define SQLITE_AFF_NUMERIC  0x43  /* 'C' */
#define SQLITE_AFF_INTEGER  0x44  /* 'D' */
#define SQLITE_AFF_REAL     0x45  /* 'E' */
#define SQLITE_AFF_FLEXNUM  0x46  /* 'F' */


#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)

/*
** The SQLITE_AFF_MASK values masks off the significant bits of an
** affinity value.
*/








>








2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339

#define SQLITE_AFF_NONE     0x40  /* '@' */
#define SQLITE_AFF_BLOB     0x41  /* 'A' */
#define SQLITE_AFF_TEXT     0x42  /* 'B' */
#define SQLITE_AFF_NUMERIC  0x43  /* 'C' */
#define SQLITE_AFF_INTEGER  0x44  /* 'D' */
#define SQLITE_AFF_REAL     0x45  /* 'E' */
#define SQLITE_AFF_FLEXNUM  0x46  /* 'F' */
#define SQLITE_AFF_DEFER    0x58  /* 'X'  - defer computation until later */

#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)

/*
** The SQLITE_AFF_MASK values masks off the significant bits of an
** affinity value.
*/
︙︙
Changes to test/joinH.test.
︙︙
385
386
387
388
389
390
391





















392
393

  SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true;
} {0 {}}

do_execsql_test 14.2.4 {
  SELECT * 
  FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true;
} {0 {}}






















finish_test








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



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

  SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true;
} {0 {}}

do_execsql_test 14.2.4 {
  SELECT * 
  FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true;
} {0 {}}

# 2025-06-01 
#
reset_db
do_execsql_test 15.1 {
  CREATE TABLE t0(c0);
  CREATE TABLE t1(c0);
  CREATE TABLE t2(c0);
  INSERT INTO t0 VALUES ('1.0');
  INSERT INTO t2(c0) VALUES (9);
  SELECT t0.c0,t2.c0 FROM (SELECT CAST(t0.c0 as REAL) AS c0 FROM t0) as subquery NATURAL LEFT JOIN t1  NATURAL JOIN t0  RIGHT JOIN t2 ON 1;
} {1.0 9}
do_execsql_test 15.2 {
  CREATE TABLE x1(x COLLATE nocase);
  CREATE TABLE x2(x);
  CREATE TABLE x3(x);
  CREATE TABLE t4(y);
  INSERT INTO x1 VALUES('ABC');
  INSERT INTO x3 VALUES('abc');
  SELECT lower(x), quote(y) FROM x1 LEFT JOIN x2 USING (x) JOIN x3 USING (x) FULL JOIN t4;
} {abc NULL}

finish_test