/ Check-in [5d00cce7]
Login

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

Overview
Comment:Additional unlikely() test cases. Logic tweaks to support test coverage.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | unlikely-func
Files: files | file ages | folders
SHA1: 5d00cce74a7aefaf30022ae971ab1e0451e0ad6e
User & Date: drh 2013-09-11 14:34:58
Context
2013-09-11
17:39
Improvements to likelihood processing so that commuting an unindexed term in the WHERE clause does not change the query plan. check-in: 6e6bded0 user: drh tags: unlikely-func
14:34
Additional unlikely() test cases. Logic tweaks to support test coverage. check-in: 5d00cce7 user: drh tags: unlikely-func
11:38
Change the name of the two-argument unlikely() function to likelihood(). Add test cases. check-in: 29a359b8 user: drh tags: unlikely-func
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

575
576
577
578
579
580
581

582
583
584
585
586
587
588
589
** Return 1024 times this value.  Or return -1 if p is not a floating point
** value between 1.0 and 0.0.
*/
static int exprProbability(Expr *p){
  double r = -1.0;
  if( p->op!=TK_FLOAT ) return -1;
  sqlite3AtoF(p->u.zToken, &r, sqlite3Strlen30(p->u.zToken), SQLITE_UTF8);

  if( r<0.0 || r>1.0 ) return -1;
  return (int)(r*1000.0);
}

/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current







>
|







575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
** Return 1024 times this value.  Or return -1 if p is not a floating point
** value between 1.0 and 0.0.
*/
static int exprProbability(Expr *p){
  double r = -1.0;
  if( p->op!=TK_FLOAT ) return -1;
  sqlite3AtoF(p->u.zToken, &r, sqlite3Strlen30(p->u.zToken), SQLITE_UTF8);
  assert( r>=0.0 );
  if( r>1.0 ) return -1;
  return (int)(r*1000.0);
}

/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current

Changes to src/where.c.

687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
      sqlite3DbFree(db, pOld);
    }
    pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]);
  }
  pTerm = &pWC->a[idx = pWC->nTerm++];
  if( wtFlags & TERM_VIRTUAL ){
    pTerm->truthProb = 0;
  }else if( p && ExprHasAnyProperty(p, EP_Hint) ){
    pTerm->truthProb = whereCost(p->iTable) - 99;
  }else{
    pTerm->truthProb = -1;
  }
  pTerm->pExpr = sqlite3ExprSkipCollate(p);
  pTerm->wtFlags = wtFlags;
  pTerm->pWC = pWC;







|







687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
      sqlite3DbFree(db, pOld);
    }
    pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]);
  }
  pTerm = &pWC->a[idx = pWC->nTerm++];
  if( wtFlags & TERM_VIRTUAL ){
    pTerm->truthProb = 0;
  }else if( ALWAYS(p) && ExprHasAnyProperty(p, EP_Hint) ){
    pTerm->truthProb = whereCost(p->iTable) - 99;
  }else{
    pTerm->truthProb = -1;
  }
  pTerm->pExpr = sqlite3ExprSkipCollate(p);
  pTerm->wtFlags = wtFlags;
  pTerm->pWC = pWC;

Changes to test/whereG.test.

28
29
30
31
32
33
34


























35
36
37
38
39
40
41
42








43
44
45
46
47
48
49








50
51
52
53
54
55
56








57
58
59
60
61
62
63







64
65
66
67
68
69
70
    tid INTEGER PRIMARY KEY,
    cid INTEGER REFERENCES composer,
    aid INTEGER REFERENCES album,
    title TEXT
  );
  CREATE INDEX track_i1 ON track(cid);
  CREATE INDEX track_i2 ON track(aid);


























} {}
do_eqp_test whereG-1.1 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE unlikely(cname LIKE '%bach%')
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*composer.*track.*album.*/}








do_eqp_test whereG-1.2 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE likelihood(cname LIKE '%bach%', 0.5)
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*track.*composer.*album.*/}








do_eqp_test whereG-1.3 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*track.*composer.*album.*/}








do_eqp_test whereG-1.4 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND unlikely(composer.cid=track.cid)
     AND unlikely(album.aid=track.aid);
} {/.*track.*composer.*album.*/}








do_test whereG-2.1 {
  catchsql {
    SELECT DISTINCT aname
      FROM album, composer, track
     WHERE likelihood(cname LIKE '%bach%', -0.01)
       AND composer.cid=track.cid







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








>
>
>
>
>
>
>
>
|






>
>
>
>
>
>
>
>
|






>
>
>
>
>
>
>
>
|






>
>
>
>
>
>
>







28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
    tid INTEGER PRIMARY KEY,
    cid INTEGER REFERENCES composer,
    aid INTEGER REFERENCES album,
    title TEXT
  );
  CREATE INDEX track_i1 ON track(cid);
  CREATE INDEX track_i2 ON track(aid);
  INSERT INTO composer VALUES(1, 'W. A. Mozart');
  INSERT INTO composer VALUES(2, 'Beethoven');
  INSERT INTO composer VALUES(3, 'Thomas Tallis');
  INSERT INTO composer VALUES(4, 'Joseph Hayden');
  INSERT INTO composer VALUES(5, 'Thomas Weelkes');
  INSERT INTO composer VALUES(6, 'J. S. Bach');
  INSERT INTO composer VALUES(7, 'Orlando Gibbons');
  INSERT INTO composer VALUES(8, 'Josquin des Prés');
  INSERT INTO composer VALUES(9, 'Byrd');
  INSERT INTO composer VALUES(10, 'Francis Poulenc');
  INSERT INTO composer VALUES(11, 'Mendelsshon');
  INSERT INTO composer VALUES(12, 'Zoltán Kodály');
  INSERT INTO composer VALUES(13, 'Handel');
  INSERT INTO album VALUES(100, 'Kodály: Missa Brevis');
  INSERT INTO album VALUES(101, 'Messiah');
  INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65');
  INSERT INTO album VALUES(103, 'The complete English anthems');
  INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232');
  INSERT INTO track VALUES(10005, 12, 100, 'Sanctus');
  INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei');
  INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs');
  INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death');
  INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei');
  INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me');
  INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus');
  INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis');
} {}
do_eqp_test whereG-1.1 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE unlikely(cname LIKE '%bach%')
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*composer.*track.*album.*/}
do_execsql_test whereG-1.2 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE unlikely(cname LIKE '%bach%')
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {{Mass in B Minor, BWV 232}}

do_eqp_test whereG-1.3 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE likelihood(cname LIKE '%bach%', 0.5)
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*track.*composer.*album.*/}
do_execsql_test whereG-1.4 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE likelihood(cname LIKE '%bach%', 0.5)
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {{Mass in B Minor, BWV 232}}

do_eqp_test whereG-1.5 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {/.*track.*composer.*album.*/}
do_execsql_test whereG-1.6 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND composer.cid=track.cid
     AND album.aid=track.aid;
} {{Mass in B Minor, BWV 232}}

do_eqp_test whereG-1.7 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND unlikely(composer.cid=track.cid)
     AND unlikely(album.aid=track.aid);
} {/.*track.*composer.*album.*/}
do_execsql_test whereG-1.8 {
  SELECT DISTINCT aname
    FROM album, composer, track
   WHERE cname LIKE '%bach%'
     AND unlikely(composer.cid=track.cid)
     AND unlikely(album.aid=track.aid);
} {{Mass in B Minor, BWV 232}}

do_test whereG-2.1 {
  catchsql {
    SELECT DISTINCT aname
      FROM album, composer, track
     WHERE likelihood(cname LIKE '%bach%', -0.01)
       AND composer.cid=track.cid