/ Check-in [87b38166]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix a virtual table problem that can occur when the vtab is on the RHS of a LEFT JOIN and there is a MATCH constraint in the ON clause, or when the vtab is in a sub-query that is the RHS of a LEFT JOIN and there is a MATCH constraint in the WHERE clause of the sub-query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 87b381663398f029f52cb5598aadd069b03034b6f833ce80ba762b406075a44e
User & Date: dan 2017-06-27 16:39:01
Context
2017-06-27
23:36
Add SQLITE_DBCONFIG_ENABLE_QPSG that forces the query planner stability guarantee. This is the fix for ticket [b9f010107724c] check-in: b82efd2a user: drh tags: trunk
16:48
Merge latest trunk changes with this branch. check-in: 2b095406 user: dan tags: apple-osx
16:39
Fix a virtual table problem that can occur when the vtab is on the RHS of a LEFT JOIN and there is a MATCH constraint in the ON clause, or when the vtab is in a sub-query that is the RHS of a LEFT JOIN and there is a MATCH constraint in the WHERE clause of the sub-query. check-in: 87b38166 user: dan tags: trunk
2017-06-26
21:08
Add the -withoutnulls option to the "db eval" method in the TCL interface. check-in: 18f0616e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/fts5/test/fts5leftjoin.test.

            1  +# 2014 June 17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this script is testing the FTS5 module.
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] fts5_common.tcl]
           16  +set testprefix fts5leftjoin
           17  +
           18  +# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
           19  +ifcapable !fts5 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE VIRTUAL TABLE vt USING fts5(x);
           26  +  INSERT INTO vt VALUES('abc');
           27  +  INSERT INTO vt VALUES('xyz');
           28  +
           29  +  CREATE TABLE t1(a INTEGER PRIMARY KEY);
           30  +  INSERT INTO t1 VALUES(1), (2);
           31  +}
           32  +
           33  +do_execsql_test 1.1 {
           34  +  SELECT * FROM t1 LEFT JOIN (
           35  +    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
           36  +  ) ON t1.a = rrr
           37  +} {1 1 abc 2 {} {}}
           38  +
           39  +do_execsql_test 1.2 {
           40  +  SELECT * FROM t1 LEFT JOIN vt ON (vt MATCH 'abc')
           41  +} {1 abc 2 abc}
           42  +
           43  +finish_test
           44  +
           45  +

Changes to src/whereexpr.c.

  1174   1174       pLeft = pExpr->x.pList->a[1].pExpr;
  1175   1175       prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
  1176   1176       prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
  1177   1177       if( (prereqExpr & prereqColumn)==0 ){
  1178   1178         Expr *pNewExpr;
  1179   1179         pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
  1180   1180                                 0, sqlite3ExprDup(db, pRight, 0));
         1181  +      if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
         1182  +        ExprSetProperty(pNewExpr, EP_FromJoin);
         1183  +      }
  1181   1184         idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
  1182   1185         testcase( idxNew==0 );
  1183   1186         pNewTerm = &pWC->a[idxNew];
  1184   1187         pNewTerm->prereqRight = prereqExpr;
  1185   1188         pNewTerm->leftCursor = pLeft->iTable;
  1186   1189         pNewTerm->u.leftColumn = pLeft->iColumn;
  1187   1190         pNewTerm->eOperator = WO_MATCH;

Changes to test/fts3join.test.

    56     56   do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}
    57     57   do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}
    58     58   do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc}
    59     59   
    60     60   do_catchsql_test 2.5 { 
    61     61     SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; 
    62     62   } {1 {unable to use function MATCH in the requested context}}
           63  +
           64  +do_execsql_test 3.0 {
           65  +  CREATE VIRTUAL TABLE vt USING fts3(x);
           66  +  INSERT INTO vt VALUES('abc');
           67  +  INSERT INTO vt VALUES('xyz');
           68  +
           69  +  CREATE TABLE tt(a INTEGER PRIMARY KEY);
           70  +  INSERT INTO tt VALUES(1), (2);
           71  +}
           72  +
           73  +do_execsql_test 3.1 {
           74  +  SELECT * FROM tt LEFT JOIN (
           75  +    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
           76  +  ) ON tt.a = rrr
           77  +} {1 1 abc 2 {} {}}
           78  +
           79  +do_execsql_test 3.2 {
           80  +  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')
           81  +} {1 abc 2 abc}
           82  +
    63     83   
    64     84   finish_test