/ Check-in [29a359b8]
Login

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

Overview
Comment:Change the name of the two-argument unlikely() function to likelihood(). Add test cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | unlikely-func
Files: files | file ages | folders
SHA1:29a359b8d7f90e6fa2b28ce2a112284fd3870494
User & Date: drh 2013-09-11 11:38:58
Context
2013-09-11
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
03:53
Tweaks to the index selection logic. check-in: 52d52688 user: drh tags: unlikely-func
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/func.c.

1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
    FUNCTION(lower,              1, 0, 0, lowerFunc        ),
    FUNCTION(coalesce,           1, 0, 0, 0                ),
    FUNCTION(coalesce,           0, 0, 0, 0                ),
    FUNCTION2(coalesce,         -1, 0, 0, noopFunc,  SQLITE_FUNC_COALESCE),
    FUNCTION(hex,                1, 0, 0, hexFunc          ),
    FUNCTION2(ifnull,            2, 0, 0, noopFunc,  SQLITE_FUNC_COALESCE),
    FUNCTION2(unlikely,          1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
    FUNCTION2(unlikely,          2, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
    FUNCTION(random,             0, 0, 0, randomFunc       ),
    FUNCTION(randomblob,         1, 0, 0, randomBlob       ),
    FUNCTION(nullif,             2, 0, 1, nullifFunc       ),
    FUNCTION(sqlite_version,     0, 0, 0, versionFunc      ),
    FUNCTION(sqlite_source_id,   0, 0, 0, sourceidFunc     ),
    FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),
#ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS







|







1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
    FUNCTION(lower,              1, 0, 0, lowerFunc        ),
    FUNCTION(coalesce,           1, 0, 0, 0                ),
    FUNCTION(coalesce,           0, 0, 0, 0                ),
    FUNCTION2(coalesce,         -1, 0, 0, noopFunc,  SQLITE_FUNC_COALESCE),
    FUNCTION(hex,                1, 0, 0, hexFunc          ),
    FUNCTION2(ifnull,            2, 0, 0, noopFunc,  SQLITE_FUNC_COALESCE),
    FUNCTION2(unlikely,          1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
    FUNCTION2(likelihood,        2, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
    FUNCTION(random,             0, 0, 0, randomFunc       ),
    FUNCTION(randomblob,         1, 0, 0, randomBlob       ),
    FUNCTION(nullif,             2, 0, 1, nullifFunc       ),
    FUNCTION(sqlite_version,     0, 0, 0, versionFunc      ),
    FUNCTION(sqlite_source_id,   0, 0, 0, sourceidFunc     ),
    FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),
#ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS

Changes to src/resolve.c.

696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
      }else{
        is_agg = pDef->xFunc==0;
        if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){
          ExprSetProperty(pExpr, EP_Hint);
          if( n==2 ){
            pExpr->iTable = exprProbability(pList->a[1].pExpr);
            if( pExpr->iTable<0 ){
              sqlite3ErrorMsg(pParse, "second parameter to unlikely() must be "
                                      "between 0.0 and 1.0");
              pNC->nErr++;
            }
          }else{
            pExpr->iTable = 62;  /* TUNING:  Default 2nd arg to unlikely() is 0.0625 */
          }             
        }
      }







|
|







696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
      }else{
        is_agg = pDef->xFunc==0;
        if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){
          ExprSetProperty(pExpr, EP_Hint);
          if( n==2 ){
            pExpr->iTable = exprProbability(pList->a[1].pExpr);
            if( pExpr->iTable<0 ){
              sqlite3ErrorMsg(pParse, "second argument to likelihood() must be a "
                                      "constant between 0.0 and 1.0");
              pNC->nErr++;
            }
          }else{
            pExpr->iTable = 62;  /* TUNING:  Default 2nd arg to unlikely() is 0.0625 */
          }             
        }
      }

Added test/whereG.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
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
# 2013-09-05
#
# 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.
#
#***********************************************************************
# 
# Test cases for query planning decisions and the unlikely() and
# likelihood() functions.

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test whereG-1.0 {
  CREATE TABLE composer(
    cid INTEGER PRIMARY KEY,
    cname TEXT
  );
  CREATE TABLE album(
    aid INTEGER PRIMARY KEY,
    aname TEXT
  );
  CREATE TABLE track(
    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
       AND album.aid=track.aid;
  }
} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
do_test whereG-2.2 {
  catchsql {
    SELECT DISTINCT aname
      FROM album, composer, track
     WHERE likelihood(cname LIKE '%bach%', 1.01)
       AND composer.cid=track.cid
       AND album.aid=track.aid;
  }
} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
do_test whereG-2.3 {
  catchsql {
    SELECT DISTINCT aname
      FROM album, composer, track
     WHERE likelihood(cname LIKE '%bach%', track.cid)
       AND composer.cid=track.cid
       AND album.aid=track.aid;
  }
} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}

finish_test