SQLite

Check-in [87b3816633]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 87b381663398f029f52cb5598aadd069b03034b6f833ce80ba762b406075a44e
User & Date: dan 2017-06-27 16:39:01.302
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: b82efd2a24 user: drh tags: trunk)
16:48
Merge latest trunk changes with this branch. (check-in: 2b0954060f 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: 87b3816633 user: dan tags: trunk)
2017-06-26
21:08
Add the -withoutnulls option to the "db eval" method in the TCL interface. (check-in: 18f0616e15 user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Added ext/fts5/test/fts5leftjoin.test. 














































1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

# 2014 June 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the FTS5 module.
#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5leftjoin

# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE vt USING fts5(x);
  INSERT INTO vt VALUES('abc');
  INSERT INTO vt VALUES('xyz');

  CREATE TABLE t1(a INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(1), (2);
}

do_execsql_test 1.1 {
  SELECT * FROM t1 LEFT JOIN (
    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
  ) ON t1.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 1.2 {
  SELECT * FROM t1 LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}

finish_test








Changes to src/whereexpr.c.






︙


1174
1175
1176
1177
1178
1179
1180



1181
1182
1183
1184
1185
1186
1187

1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190








+
+
+








    pLeft = pExpr->x.pList->a[1].pExpr;
    prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
    prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){
      Expr *pNewExpr;
      pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
                              0, sqlite3ExprDup(db, pRight, 0));
      if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
        ExprSetProperty(pNewExpr, EP_FromJoin);
      }
      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
      testcase( idxNew==0 );
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->u.leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;




︙





Changes to test/fts3join.test.






︙


56
57
58
59
60
61
62




















63
64

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








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+



do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}
do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}
do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc}

do_catchsql_test 2.5 { 
  SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; 
} {1 {unable to use function MATCH in the requested context}}

do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE vt USING fts3(x);
  INSERT INTO vt VALUES('abc');
  INSERT INTO vt VALUES('xyz');

  CREATE TABLE tt(a INTEGER PRIMARY KEY);
  INSERT INTO tt VALUES(1), (2);
}

do_execsql_test 3.1 {
  SELECT * FROM tt LEFT JOIN (
    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'
  ) ON tt.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 3.2 {
  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}


finish_test