1# 2014-04-25 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing ORDER BY optimizations on joins 13# that involve virtual tables. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix orderby7 20 21ifcapable !fts3 { 22 finish_test 23 return 24} 25 26do_execsql_test 1.0 { 27 CREATE VIRTUAL TABLE fts USING fts3(content TEXT); 28 INSERT INTO fts(rowid,content) 29 VALUES(1,'this is a test of the fts3 virtual'), 30 (2,'table used as part of a join together'), 31 (3,'with the DISTINCT keyword. There was'), 32 (4,'a bug at one time (2013-06 through 2014-04)'), 33 (5,'that prevented this from working correctly.'), 34 (11,'a row that occurs twice'), 35 (12,'a row that occurs twice'); 36 37 CREATE TABLE t1(x TEXT PRIMARY KEY, y); 38 INSERT OR IGNORE INTO t1 SELECT content, rowid+100 FROM fts; 39} {} 40do_execsql_test 1.1 { 41 SELECT DISTINCT fts.rowid, t1.y 42 FROM fts, t1 43 WHERE fts MATCH 'that twice' 44 AND content=x 45 ORDER BY y; 46} {11 111 12 111} 47do_execsql_test 1.2 { 48 SELECT DISTINCT fts.rowid, t1.x 49 FROM fts, t1 50 WHERE fts MATCH 'that twice' 51 AND content=x 52 ORDER BY 1; 53} {11 {a row that occurs twice} 12 {a row that occurs twice}} 54do_execsql_test 1.3 { 55 SELECT DISTINCT t1.x 56 FROM fts, t1 57 WHERE fts MATCH 'that twice' 58 AND content=x 59 ORDER BY 1; 60} {{a row that occurs twice}} 61do_execsql_test 1.4 { 62 SELECT t1.x 63 FROM fts, t1 64 WHERE fts MATCH 'that twice' 65 AND content=x 66 ORDER BY 1; 67} {{a row that occurs twice} {a row that occurs twice}} 68do_execsql_test 1.5 { 69 SELECT DISTINCT t1.x 70 FROM fts, t1 71 WHERE fts MATCH 'that twice' 72 AND content=x; 73} {{a row that occurs twice}} 74do_execsql_test 1.6 { 75 SELECT t1.x 76 FROM fts, t1 77 WHERE fts MATCH 'that twice' 78 AND content=x; 79} {{a row that occurs twice} {a row that occurs twice}} 80 81do_execsql_test 2.1 { 82 SELECT DISTINCT t1.x 83 FROM fts, t1 84 WHERE fts.rowid=11 85 AND content=x 86 ORDER BY fts.rowid; 87} {{a row that occurs twice}} 88do_execsql_test 2.2 { 89 SELECT DISTINCT t1.* 90 FROM fts, t1 91 WHERE fts.rowid=11 92 AND content=x 93 ORDER BY fts.rowid; 94} {{a row that occurs twice} 111} 95do_execsql_test 2.3 { 96 SELECT DISTINCT t1.* 97 FROM fts, t1 98 WHERE fts.rowid=11 99 AND content=x 100 ORDER BY t1.y 101} {{a row that occurs twice} 111} 102 103 104 105 106finish_test 107