third_party/perfetto/buildtools/sqlite_src/test/whereG.test - cobalt - Git at Google

 # 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 likely(), unlikely(), and
 # likelihood() functions.

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

 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);
   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|album.*composer).*/}
 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|album.*composer).*/}
 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
        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}}

 # Commuting a term of the WHERE clause should not change the query plan
 #
 do_execsql_test whereG-3.0 {
   CREATE TABLE a(a1 PRIMARY KEY, a2);
   CREATE TABLE b(b1 PRIMARY KEY, b2);
 } {}
 do_eqp_test whereG-3.1 {
   SELECT * FROM a, b WHERE b1=a1 AND a2=5;
 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 do_eqp_test whereG-3.2 {
   SELECT * FROM a, b WHERE a1=b1 AND a2=5;
 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 do_eqp_test whereG-3.3 {
   SELECT * FROM a, b WHERE a2=5 AND b1=a1;
 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 do_eqp_test whereG-3.4 {
   SELECT * FROM a, b WHERE a2=5 AND a1=b1;
 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}

 # Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]:
 # Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy
 # where an OP_Copy was needed.
 #
 do_execsql_test whereG-4.0 {
   CREATE TABLE t4(x);
   INSERT INTO t4 VALUES('right'),('wrong');
   SELECT DISTINCT x
    FROM (SELECT x FROM t4 GROUP BY x)
    WHERE x='right'
    ORDER BY x;
 } {right}

 #-------------------------------------------------------------------------
 # Test that likelihood() specifications on indexed terms are taken into
 # account by various forms of loops.
 #
 #   5.1.*: open ended range scans
 #   5.2.*: skip-scans
 #
 reset_db

 do_execsql_test 5.1 {
   CREATE TABLE t1(a, b, c);
   CREATE INDEX i1 ON t1(a, b);
 }
 do_eqp_test 5.1.2 {
   SELECT * FROM t1 WHERE a>?
 } {SEARCH t1 USING INDEX i1 (a>?)}
 do_eqp_test 5.1.3 {
   SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
 } {SCAN t1}
 do_eqp_test 5.1.4 {
   SELECT * FROM t1 WHERE likely(a>?)
 } {SCAN t1}

 do_test 5.2 {
   for {set i 0} {$i < 100} {incr i} {
     execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
   }
   execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
   execsql { ANALYZE }
 } {}
 do_eqp_test 5.2.2 {
   SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
 } {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)}
 do_eqp_test 5.2.3 {
   SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
 } {SCAN t1}
 do_eqp_test 5.2.4 {
   SELECT * FROM t1 WHERE likely(b>?)
 } {SCAN t1}

 ifcapable stat4 {
   do_eqp_test 5.3.1.stat4 {
     SELECT * FROM t1 WHERE a=?
   } {SCAN t1}
 } else {
   do_eqp_test 5.3.1 {
     SELECT * FROM t1 WHERE a=?
   } {SEARCH t1 USING INDEX i1}
 }
 do_eqp_test 5.3.2 {
   SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
 } {SCAN t1}
 do_eqp_test 5.3.3 {
   SELECT * FROM t1 WHERE likely(a=?)
 } {SCAN 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}

 # 2015-06-20
 # Crash discovered by AFL
 #
 do_execsql_test 7.0 {
   DROP TABLE IF EXISTS t1;
   CREATE TABLE t1(a, b, PRIMARY KEY(a,b));
   INSERT INTO t1 VALUES(9,1),(1,2);
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t2(x, y, PRIMARY KEY(x,y));
   INSERT INTO t2 VALUES(3,3),(4,4);
   SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2;
 } {1 3 1 4 9 3 9 4}
 do_execsql_test 7.1 {
   SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2;
 } {1 3 1 4 9 3 9 4}
 do_execsql_test 7.2 {
   SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2;
 } {1 3 1 4 9 3 9 4}
 do_execsql_test 7.3 {
   SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2;
 } {1 3 1 4 9 3 9 4}

 # 2019-08-22
 # Ticket https://www.sqlite.org/src/info/7e07a3dbf5a8cd26
 #
 do_execsql_test 8.1 {
   DROP TABLE IF EXISTS t0;
   CREATE TABLE t0 (c0);
   INSERT INTO t0(c0) VALUES ('a');
   SELECT LIKELY(t0.rowid) <= '0' FROM t0;
 } {1}
 do_execsql_test 8.2 {
   SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0';
 } {a}
 do_execsql_test 8.3 {
   SELECT (t0.rowid) <= '0' FROM t0;
 } {0}
 do_execsql_test 8.4 {
   SELECT * FROM t0 WHERE (t0.rowid) <= '0';
 } {}
 do_execsql_test 8.5 {
   SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0;
 } {1 1}
 do_execsql_test 8.6 {
   SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0';
 } {a}
 do_execsql_test 8.7 {
   SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0';
 } {a}
 do_execsql_test 8.8 {
   SELECT unlikely(t0.rowid <= '0'),
          likely(t0.rowid <= '0'),
          likelihood(t0.rowid <= '0',0.5)
     FROM t0;
 } {0 0 0}
 do_execsql_test 8.9 {
   SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0');
 } {}
 do_execsql_test 8.10 {
   SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
 } {}

 # 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
 # Harmless memIsValid() due to the code generators failure to
 # release the registers used by OP_ResultRow.
 #
 do_execsql_test 9.10 {
   DROP TABLE IF EXISTS t1;
   CREATE TABLE t1(a, b FLOAT);
   INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
   SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
 } {NULL '' 'X'}

 # 2020-06-14: assert() changed back into testcase()
 # ticket 9fb26d37cefaba40
 #
 reset_db
 do_execsql_test 10.1 {
   CREATE TABLE a(b TEXT);  INSERT INTO a VALUES(0),(4),(9);
   CREATE TABLE c(d NUM);
   CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
   SELECT g = g FROM f GROUP BY h;
 } {1}

 reset_db
 do_execsql_test 11.0 {
   CREATE TABLE t1(x PRIMARY KEY, y);
   INSERT INTO t1 VALUES('AAA', 'BBB');

   CREATE TABLE t2(z);
   INSERT INTO t2 VALUES('t2');

   CREATE TABLE t3(x PRIMARY KEY, y);
   INSERT INTO t3 VALUES('AAA', 'AAA');
 }

 do_execsql_test 11.1.1 {
   SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA'
 }
 do_execsql_test 11.1.2 {
   SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA'
 }
 do_execsql_test 11.1.3 {
   SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA'
 }

 do_execsql_test 11.2.1 {
   SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA'
 } {AAA AAA t2}
 do_execsql_test 11.2.2 {
   SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
 } {AAA AAA t2}
 do_execsql_test 11.2.3 {
   SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
 } {AAA AAA t2}

 # 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
 # Affinity problem when a likely() function is used as a column in
 # an index.
 #
 reset_db
 do_execsql_test 12.0 {
   CREATE TABLE t1(a REAL);
   INSERT INTO t1(a) VALUES(123);
   CREATE INDEX t1x1 ON t1(likely(a));
   SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
   SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
 } {real real}
 do_execsql_test 12.1 {
   CREATE INDEX t1x2 ON t1(abs(a));
   SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
   SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
 } {real real}


 finish_test
	# 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 likely(), unlikely(), and
	# likelihood() functions.

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

	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);
	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;
	} {composertrackalbum}
	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\|album.composer).*/}
	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\|album.composer).*/}
	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
	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}}

	# Commuting a term of the WHERE clause should not change the query plan
	#
	do_execsql_test whereG-3.0 {
	CREATE TABLE a(a1 PRIMARY KEY, a2);
	CREATE TABLE b(b1 PRIMARY KEY, b2);
	} {}
	do_eqp_test whereG-3.1 {
	SELECT * FROM a, b WHERE b1=a1 AND a2=5;
	} {/.SCAN a.SEARCH b USING INDEX .b_1 .b1=../}
	do_eqp_test whereG-3.2 {
	SELECT * FROM a, b WHERE a1=b1 AND a2=5;
	} {/.SCAN a.SEARCH b USING INDEX .b_1 .b1=../}
	do_eqp_test whereG-3.3 {
	SELECT * FROM a, b WHERE a2=5 AND b1=a1;
	} {/.SCAN a.SEARCH b USING INDEX .b_1 .b1=../}
	do_eqp_test whereG-3.4 {
	SELECT * FROM a, b WHERE a2=5 AND a1=b1;
	} {/.SCAN a.SEARCH b USING INDEX .b_1 .b1=../}

	# Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]:
	# Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy
	# where an OP_Copy was needed.
	#
	do_execsql_test whereG-4.0 {
	CREATE TABLE t4(x);
	INSERT INTO t4 VALUES('right'),('wrong');
	SELECT DISTINCT x
	FROM (SELECT x FROM t4 GROUP BY x)
	WHERE x='right'
	ORDER BY x;
	} {right}

	#-------------------------------------------------------------------------
	# Test that likelihood() specifications on indexed terms are taken into
	# account by various forms of loops.
	#
	# 5.1.*: open ended range scans
	# 5.2.*: skip-scans
	#
	reset_db

	do_execsql_test 5.1 {
	CREATE TABLE t1(a, b, c);
	CREATE INDEX i1 ON t1(a, b);
	}
	do_eqp_test 5.1.2 {
	SELECT * FROM t1 WHERE a>?
	} {SEARCH t1 USING INDEX i1 (a>?)}
	do_eqp_test 5.1.3 {
	SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
	} {SCAN t1}
	do_eqp_test 5.1.4 {
	SELECT * FROM t1 WHERE likely(a>?)
	} {SCAN t1}

	do_test 5.2 {
	for {set i 0} {$i < 100} {incr i} {
	execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
	}
	execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
	execsql { ANALYZE }
	} {}
	do_eqp_test 5.2.2 {
	SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
	} {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)}
	do_eqp_test 5.2.3 {
	SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
	} {SCAN t1}
	do_eqp_test 5.2.4 {
	SELECT * FROM t1 WHERE likely(b>?)
	} {SCAN t1}

	ifcapable stat4 {
	do_eqp_test 5.3.1.stat4 {
	SELECT * FROM t1 WHERE a=?
	} {SCAN t1}
	} else {
	do_eqp_test 5.3.1 {
	SELECT * FROM t1 WHERE a=?
	} {SEARCH t1 USING INDEX i1}
	}
	do_eqp_test 5.3.2 {
	SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
	} {SCAN t1}
	do_eqp_test 5.3.3 {
	SELECT * FROM t1 WHERE likely(a=?)
	} {SCAN 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}

	# 2015-06-20
	# Crash discovered by AFL
	#
	do_execsql_test 7.0 {
	DROP TABLE IF EXISTS t1;
	CREATE TABLE t1(a, b, PRIMARY KEY(a,b));
	INSERT INTO t1 VALUES(9,1),(1,2);
	DROP TABLE IF EXISTS t2;
	CREATE TABLE t2(x, y, PRIMARY KEY(x,y));
	INSERT INTO t2 VALUES(3,3),(4,4);
	SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2;
	} {1 3 1 4 9 3 9 4}
	do_execsql_test 7.1 {
	SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2;
	} {1 3 1 4 9 3 9 4}
	do_execsql_test 7.2 {
	SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2;
	} {1 3 1 4 9 3 9 4}
	do_execsql_test 7.3 {
	SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2;
	} {1 3 1 4 9 3 9 4}

	# 2019-08-22
	# Ticket https://www.sqlite.org/src/info/7e07a3dbf5a8cd26
	#
	do_execsql_test 8.1 {
	DROP TABLE IF EXISTS t0;
	CREATE TABLE t0 (c0);
	INSERT INTO t0(c0) VALUES ('a');
	SELECT LIKELY(t0.rowid) <= '0' FROM t0;
	} {1}
	do_execsql_test 8.2 {
	SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0';
	} {a}
	do_execsql_test 8.3 {
	SELECT (t0.rowid) <= '0' FROM t0;
	} {0}
	do_execsql_test 8.4 {
	SELECT * FROM t0 WHERE (t0.rowid) <= '0';
	} {}
	do_execsql_test 8.5 {
	SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0;
	} {1 1}
	do_execsql_test 8.6 {
	SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0';
	} {a}
	do_execsql_test 8.7 {
	SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0';
	} {a}
	do_execsql_test 8.8 {
	SELECT unlikely(t0.rowid <= '0'),
	likely(t0.rowid <= '0'),
	likelihood(t0.rowid <= '0',0.5)
	FROM t0;
	} {0 0 0}
	do_execsql_test 8.9 {
	SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0');
	} {}
	do_execsql_test 8.10 {
	SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
	} {}

	# 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
	# Harmless memIsValid() due to the code generators failure to
	# release the registers used by OP_ResultRow.
	#
	do_execsql_test 9.10 {
	DROP TABLE IF EXISTS t1;
	CREATE TABLE t1(a, b FLOAT);
	INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
	SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
	} {NULL '' 'X'}

	# 2020-06-14: assert() changed back into testcase()
	# ticket 9fb26d37cefaba40
	#
	reset_db
	do_execsql_test 10.1 {
	CREATE TABLE a(b TEXT); INSERT INTO a VALUES(0),(4),(9);
	CREATE TABLE c(d NUM);
	CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
	SELECT g = g FROM f GROUP BY h;
	} {1}

	reset_db
	do_execsql_test 11.0 {
	CREATE TABLE t1(x PRIMARY KEY, y);
	INSERT INTO t1 VALUES('AAA', 'BBB');

	CREATE TABLE t2(z);
	INSERT INTO t2 VALUES('t2');

	CREATE TABLE t3(x PRIMARY KEY, y);
	INSERT INTO t3 VALUES('AAA', 'AAA');
	}

	do_execsql_test 11.1.1 {
	SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA'
	}
	do_execsql_test 11.1.2 {
	SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA'
	}
	do_execsql_test 11.1.3 {
	SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA'
	}

	do_execsql_test 11.2.1 {
	SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA'
	} {AAA AAA t2}
	do_execsql_test 11.2.2 {
	SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
	} {AAA AAA t2}
	do_execsql_test 11.2.3 {
	SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
	} {AAA AAA t2}

	# 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
	# Affinity problem when a likely() function is used as a column in
	# an index.
	#
	reset_db
	do_execsql_test 12.0 {
	CREATE TABLE t1(a REAL);
	INSERT INTO t1(a) VALUES(123);
	CREATE INDEX t1x1 ON t1(likely(a));
	SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
	SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
	} {real real}
	do_execsql_test 12.1 {
	CREATE INDEX t1x2 ON t1(abs(a));
	SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
	SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
	} {real real}


	finish_test