/ Check-in [ed96e14a]
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:Pervent the likelihood() function from moving ON clause terms of a LEFT JOIN into the WHERE clause. Fix for ticket [5f60b11fc8e23490e2]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ed96e14adce4a4f94cc6838c46bc97937c4cb72a
User & Date: drh 2015-06-18 14:32:51
Context
2015-06-18
15:26
Further #ifdef changes in speedtest1.c in order to support SQLite back to version 3.3.9 and perhaps even earlier. check-in: 9246eca5 user: drh tags: trunk
14:32
Pervent the likelihood() function from moving ON clause terms of a LEFT JOIN into the WHERE clause. Fix for ticket [5f60b11fc8e23490e2] check-in: ed96e14a user: drh tags: trunk
14:09
Show the values of Expr.flag in the AST of ".selecttrace" check-in: a84d1004 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

362
363
364
365
366
367
368






369
370
371
372
373
374
375
*/
static void setJoinExpr(Expr *p, int iTable){
  while( p ){
    ExprSetProperty(p, EP_FromJoin);
    assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
    ExprSetVVAProperty(p, EP_NoReduce);
    p->iRightJoinTable = (i16)iTable;






    setJoinExpr(p->pLeft, iTable);
    p = p->pRight;
  } 
}

/*
** This routine processes the join information for a SELECT statement.







>
>
>
>
>
>







362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
*/
static void setJoinExpr(Expr *p, int iTable){
  while( p ){
    ExprSetProperty(p, EP_FromJoin);
    assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
    ExprSetVVAProperty(p, EP_NoReduce);
    p->iRightJoinTable = (i16)iTable;
    if( p->op==TK_FUNCTION && p->x.pList ){
      int i;
      for(i=0; i<p->x.pList->nExpr; i++){
        setJoinExpr(p->x.pList->a[i].pExpr, iTable);
      }
    }
    setJoinExpr(p->pLeft, iTable);
    p = p->pRight;
  } 
}

/*
** This routine processes the join information for a SELECT statement.

Changes to test/whereG.test.

225
226
227
228
229
230
231
232















233
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
do_eqp_test 5.3.2 {
  SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
} {0 0 0 {SCAN TABLE t1}}
do_eqp_test 5.3.3 {
  SELECT * FROM t1 WHERE likely(a=?)
} {0 0 0 {SCAN TABLE t1}}
















finish_test








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

225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
do_eqp_test 5.3.2 {
  SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
} {0 0 0 {SCAN TABLE t1}}
do_eqp_test 5.3.3 {
  SELECT * FROM t1 WHERE likely(a=?)
} {0 0 0 {SCAN TABLE t1}}

# 2015-06-18
# Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70]
#
do_execsql_test 6.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(i int, x, y, z);
  INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(i int, bool char);
  INSERT INTO t2 VALUES(1,'T'), (2,'F');
  SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T';
  SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T';
} {4 4}


finish_test