/ Check-in [3d49c593]
Login

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

Overview
Comment:Add a query planner test case submitted by Elan Feingold and based on the Plex project.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3d49c593dc12d72323ca525372a15e58c591940b
User & Date: drh 2013-07-01 20:02:31
Context
2013-07-02
00:06
Fix harmless typos in comments of two extensions. check-in: 1c3ed47b user: drh tags: trunk
2013-07-01
20:02
Add a query planner test case submitted by Elan Feingold and based on the Plex project. check-in: 3d49c593 user: drh tags: trunk
17:27
Add a missing test that prevented double LEFT JOINs with transitive constraints from working correctly. Fix for ticket [868145d012]. check-in: 72919ec3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/wild001.test.

            1  +# 2013-07-01
            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  +# 
           12  +# This is a test case from content taken "from the wild".  In this
           13  +# particular instance, the query was provided with permission by
           14  +# Elan Feingold on 2013-06-27.  His message on the SQLite mailing list
           15  +# on that date reads:
           16  +#
           17  +#------------------------------------------------------------------------------
           18  +# > Can you send (1) the schema (2) the query that is giving problems, and (3)
           19  +# > the content of the sqlite_stat1 table after you have run ANALYZE?   If you
           20  +# > can combine all of the above into a script, that would be great!
           21  +# >
           22  +# > If you send (1..3) above and you give us written permission to include the
           23  +# > query in our test suite, that would be off-the-chain terrific.
           24  +#
           25  +# Please find items 1..3 in this file: http://www.plexapp.com/elan/sqlite_bug.txt
           26  +# 
           27  +# You have our permission to include the query in your test suite.
           28  +# 
           29  +# Thanks for an amazing product.
           30  +#-----------------------------------------------------------------------------
           31  +#
           32  +# This test case merely creates the schema and populates SQLITE_STAT1 and
           33  +# SQLITE_STAT3 then runs an EXPLAIN QUERY PLAN to ensure that the right plan
           34  +# is discovered.  This test case may need to be adjusted for future revisions
           35  +# of the query planner manage to select a better query plan.  The query plan
           36  +# shown here is known to be very fast with the original data.
           37  +#
           38  +# This test should work the same with and without SQLITE_ENABLE_STAT3
           39  +#
           40  +###############################################################################
           41  +
           42  +set testdir [file dirname $argv0]
           43  +source $testdir/tester.tcl
           44  +
           45  +do_execsql_test wild001.01 {
           46  +  CREATE TABLE "items" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "secid" integer, "parent_id" integer, "metadata_type" integer, "guid" varchar(255), "media_item_count" integer, "title" varchar(255), "title_sort" varchar(255) COLLATE NOCASE, "original_title" varchar(255), "studio" varchar(255), "rating" float, "rating_count" integer, "tagline" varchar(255), "summary" text, "trivia" text, "quotes" text, "content_rating" varchar(255), "content_rating_age" integer, "index" integer, "absolute_index" integer, "duration" integer, "user_thumb_url" varchar(255), "user_art_url" varchar(255), "user_banner_url" varchar(255), "user_music_url" varchar(255), "user_fields" varchar(255), "tags_genre" varchar(255), "tags_collection" varchar(255), "tags_director" varchar(255), "tags_writer" varchar(255), "tags_star" varchar(255), "originally_available_at" datetime, "available_at" datetime, "expires_at" datetime, "refreshed_at" datetime, "year" integer, "added_at" datetime, "created_at" datetime, "updated_at" datetime, "deleted_at" datetime, "tags_country" varchar(255), "extra_data" varchar(255), "hash" varchar(255));
           47  +  CREATE INDEX "i_secid" ON "items" ("secid" );
           48  +  CREATE INDEX "i_parent_id" ON "items" ("parent_id" );
           49  +  CREATE INDEX "i_created_at" ON "items" ("created_at" );
           50  +  CREATE INDEX "i_index" ON "items" ("index" );
           51  +  CREATE INDEX "i_title" ON "items" ("title" );
           52  +  CREATE INDEX "i_title_sort" ON "items" ("title_sort" );
           53  +  CREATE INDEX "i_guid" ON "items" ("guid" );
           54  +  CREATE INDEX "i_metadata_type" ON "items" ("metadata_type" );
           55  +  CREATE INDEX "i_deleted_at" ON "items" ("deleted_at" );
           56  +  CREATE INDEX "i_secid_ex1" ON "items" ("secid", "metadata_type", "added_at" );
           57  +  CREATE INDEX "i_hash" ON "items" ("hash" );
           58  +  CREATE TABLE "settings" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "account_id" integer, "guid" varchar(255), "rating" float, "view_offset" integer, "view_count" integer, "last_viewed_at" datetime, "created_at" datetime, "updated_at" datetime);
           59  +  CREATE INDEX "s_account_id" ON "settings" ("account_id" );
           60  +  CREATE INDEX "s_guid" ON "settings" ("guid" );
           61  +  ANALYZE;
           62  +  INSERT INTO sqlite_stat1 VALUES('settings','s_guid','4740 1');
           63  +  INSERT INTO sqlite_stat1 VALUES('settings','s_account_id','4740 4740');
           64  +  INSERT INTO sqlite_stat1 VALUES('items','i_hash','27316 2');
           65  +  INSERT INTO sqlite_stat1 VALUES('items','i_secid_ex1','27316 6829 4553 3');
           66  +  INSERT INTO sqlite_stat1 VALUES('items','i_deleted_at','27316 27316');
           67  +  INSERT INTO sqlite_stat1 VALUES('items','i_metadata_type','27316 6829');
           68  +  INSERT INTO sqlite_stat1 VALUES('items','i_guid','27316 2');
           69  +  INSERT INTO sqlite_stat1 VALUES('items','i_title_sort','27316 2');
           70  +  INSERT INTO sqlite_stat1 VALUES('items','i_title','27316 2');
           71  +  INSERT INTO sqlite_stat1 VALUES('items','i_index','27316 144');
           72  +  INSERT INTO sqlite_stat1 VALUES('items','i_created_at','27316 2');
           73  +  INSERT INTO sqlite_stat1 VALUES('items','i_parent_id','27316 15');
           74  +  INSERT INTO sqlite_stat1 VALUES('items','i_secid','27316 6829');
           75  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,150,150,'com.plexapp.agents.thetvdb://153021/2/9?lang=en');
           76  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,198,198,'com.plexapp.agents.thetvdb://194031/1/10?lang=en');
           77  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,526,526,'com.plexapp.agents.thetvdb://71256/12/92?lang=en');
           78  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,923,923,'com.plexapp.agents.thetvdb://71256/15/16?lang=en');
           79  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1008,1008,'com.plexapp.agents.thetvdb://71256/15/93?lang=en');
           80  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1053,1053,'com.plexapp.agents.thetvdb://71256/16/21?lang=en');
           81  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1068,1068,'com.plexapp.agents.thetvdb://71256/16/35?lang=en');
           82  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1235,1235,'com.plexapp.agents.thetvdb://71256/17/44?lang=en');
           83  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1255,1255,'com.plexapp.agents.thetvdb://71256/17/62?lang=en');
           84  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1573,1573,'com.plexapp.agents.thetvdb://71663/20/9?lang=en');
           85  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1580,1580,'com.plexapp.agents.thetvdb://71663/21/16?lang=en');
           86  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2000,2000,'com.plexapp.agents.thetvdb://73141/9/8?lang=en');
           87  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2107,2107,'com.plexapp.agents.thetvdb://73244/6/17?lang=en');
           88  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2256,2256,'com.plexapp.agents.thetvdb://74845/4/7?lang=en');
           89  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2408,2408,'com.plexapp.agents.thetvdb://75978/2/21?lang=en');
           90  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2634,2634,'com.plexapp.agents.thetvdb://79126/1/1?lang=en');
           91  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2962,2962,'com.plexapp.agents.thetvdb://79274/3/94?lang=en');
           92  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3160,3160,'com.plexapp.agents.thetvdb://79274/5/129?lang=en');
           93  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3161,3161,'com.plexapp.agents.thetvdb://79274/5/12?lang=en');
           94  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3688,3688,'com.plexapp.agents.thetvdb://79274/8/62?lang=en');
           95  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3714,3714,'com.plexapp.agents.thetvdb://79274/8/86?lang=en');
           96  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4002,4002,'com.plexapp.agents.thetvdb://79590/13/17?lang=en');
           97  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4215,4215,'com.plexapp.agents.thetvdb://80727/3/6?lang=en');
           98  +  INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4381,4381,'com.plexapp.agents.thetvdb://83462/3/24?lang=en');
           99  +  INSERT INTO sqlite_stat3 VALUES('settings','s_account_id',4740,0,0,1);
          100  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,1879,1879,'1113f632ccd52ec8b8d7ca3d6d56da4701e48018');
          101  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,2721,2721,'1936154b97bb5567163edaebc2806830ae419ccf');
          102  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,3035,3035,'1c122331d4b7bfa0dc2c003ab5fb4f7152b9987a');
          103  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,3393,3393,'1f81bdbc9acc3321dc592b1a109ca075731b549a');
          104  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,6071,6070,'393cf7713efb4519c7a3d1d5403f0d945d15a16a');
          105  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,7462,7461,'4677dd37011f8bd9ae7fbbdd3af6dcd8a5b4ab2d');
          106  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,8435,8434,'4ffa339485334e81a5e12e03a63b6508d76401cf');
          107  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,8716,8714,'52a093852e6599dd5004857b7ff5b5b82c7cdb25');
          108  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,9107,9104,'561183e39f866d97ec728e9ff16ac4ad01466111');
          109  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,10942,10939,'66e99b72e29610f49499ae09ee04a376210d1f08');
          110  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,12143,12139,'71f0602427e173dc2c551535f73fdb6885fe4302');
          111  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,14962,14958,'8ca8e4dfba696019830c19ab8a32c7ece9d8534b');
          112  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,15179,15174,'8ebf1a5cf33f8ada1fc5853ac06ac4d7e074f825');
          113  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,15375,15370,'908bc211bebdf21c79d2d2b54ebaa442ac1f5cae');
          114  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18215,18210,'ab29e4e18ec5a14fef95aa713d69e31c045a22c1');
          115  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18615,18610,'ae84c008cc0c338bf4f28d798a88575746452f6d');
          116  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18649,18644,'aec7c901353e115aa5307e94018ba7507bec3a45');
          117  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,19517,19512,'b75025fbf2e9c504e3c1197ff1b69250402a31f8');
          118  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,21251,21245,'c7d32f0e3a8f3a0a3dbd00833833d2ccee62f0fd');
          119  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,23616,23610,'dd5ff61479a9bd4100de802515d9dcf72d46f07a');
          120  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,24287,24280,'e3db00034301b7555419d4ef6f64769298d5845e');
          121  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,24949,24942,'ea336abd197ecd7013854a25a4f4eb9dea7927c6');
          122  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,25574,25567,'f018ea5182ec3f32768ca1c3cefbf3ad160ec20b');
          123  +  INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,26139,26132,'f53709a8d81c12cb0f4f8d58004a25dd063de67c');
          124  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',25167,0,0,2);
          125  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',736,25167,1,3);
          126  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',15,25903,2,4);
          127  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',1398,25918,3,5);
          128  +  INSERT INTO sqlite_stat3 VALUES('items','i_deleted_at',27316,0,0,NULL);
          129  +  INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',2149,0,0,1);
          130  +  INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',411,2149,1,2);
          131  +  INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',1440,2560,2,3);
          132  +  INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',23316,4000,3,4);
          133  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,215,215,'com.plexapp.agents.imdb://tt0065702?lang=en');
          134  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,711,711,'com.plexapp.agents.imdb://tt0198781?lang=en');
          135  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,987,986,'com.plexapp.agents.imdb://tt0454876?lang=en');
          136  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1004,1002,'com.plexapp.agents.imdb://tt0464154?lang=en');
          137  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1056,1053,'com.plexapp.agents.imdb://tt0499549?lang=en');
          138  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1120,1116,'com.plexapp.agents.imdb://tt0903624?lang=en');
          139  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1250,1245,'com.plexapp.agents.imdb://tt1268799?lang=en');
          140  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1270,1264,'com.plexapp.agents.imdb://tt1320261?lang=en');
          141  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1376,1369,'com.plexapp.agents.imdb://tt1772341?lang=en');
          142  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,3035,3027,'com.plexapp.agents.thetvdb://153021/3/14?lang=en');
          143  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,6071,6063,'com.plexapp.agents.thetvdb://71173/1/18?lang=en');
          144  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,6342,6334,'com.plexapp.agents.thetvdb://71256/13/4?lang=en');
          145  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,9107,9099,'com.plexapp.agents.thetvdb://72389/2/19?lang=en');
          146  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,11740,11732,'com.plexapp.agents.thetvdb://73893/2/13?lang=en');
          147  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,12143,12135,'com.plexapp.agents.thetvdb://73976/4/23?lang=en');
          148  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,15179,15171,'com.plexapp.agents.thetvdb://75897/16/12?lang=en');
          149  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,17408,17400,'com.plexapp.agents.thetvdb://76808/2/16?lang=en');
          150  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,17984,17976,'com.plexapp.agents.thetvdb://77068/1/16?lang=en');
          151  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,18215,18207,'com.plexapp.agents.thetvdb://77259/1/1?lang=en');
          152  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,21251,21243,'com.plexapp.agents.thetvdb://78957/8/2?lang=en');
          153  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,24287,24279,'com.plexapp.agents.thetvdb://80337/5/8?lang=en');
          154  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,25513,25505,'com.plexapp.agents.thetvdb://82226/6?lang=en');
          155  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,25548,25540,'com.plexapp.agents.thetvdb://82339/2/10?lang=en');
          156  +  INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,26770,26762,'com.plexapp.agents.thetvdb://86901/1/3?lang=en');
          157  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1524,0,0,'');
          158  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',2,3034,1391,'Attack of the Giant Squid');
          159  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',51,4742,2895,'Brad Sherwood');
          160  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',11,4912,2996,'Brian Williams');
          161  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',39,5847,3857,'Chip Esten');
          162  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,6071,4015,'Chuck Versus the DeLorean');
          163  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',12,7625,5436,'Denny Siegel');
          164  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',30,8924,6618,'Episode 1');
          165  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',29,9015,6629,'Episode 2');
          166  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',32,9082,6643,'Episode 3');
          167  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',28,9135,6654,'Episode 4');
          168  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',26,9183,6665,'Episode 5');
          169  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',27,9229,6677,'Episode 6');
          170  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',22,9266,6688,'Episode 7');
          171  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',20,9298,6699,'Episode 8');
          172  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',55,11750,8817,'Greg Proops');
          173  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,12143,9120,'Hardware Jungle');
          174  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',33,14712,11435,'Kathy Greenwood');
          175  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',3,15179,11840,'Last Call');
          176  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,18215,14601,'Nature or Nurture?');
          177  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',12,18241,14623,'Neil DeGrasse Tyson');
          178  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',68,19918,16144,'Pilot');
          179  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',7,21251,17298,'Reza Aslan');
          180  +  INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,24287,20035,'Technoviking');
          181  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1524,0,0,'');
          182  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,3035,1429,'Anderson Can''t Dance');
          183  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',51,4782,2991,'Brad Sherwood');
          184  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',11,4936,3079,'Brian Williams');
          185  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',39,5694,3783,'Chip Esten');
          186  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,6071,4100,'Clive Warren');
          187  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',12,7144,5078,'Denny Siegel');
          188  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',30,8249,6097,'Episode 1');
          189  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',29,8340,6108,'Episode 2');
          190  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',32,8407,6122,'Episode 3');
          191  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',28,8460,6133,'Episode 4');
          192  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',26,8508,6144,'Episode 5');
          193  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',27,8554,6156,'Episode 6');
          194  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',22,8591,6167,'Episode 7');
          195  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',20,8623,6178,'Episode 8');
          196  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,9107,6537,'Fat Albert and the Cosby Kids');
          197  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',55,10539,7843,'Greg Proops');
          198  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,12143,9276,'Iron Age Remains');
          199  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',33,13118,10143,'Kathy Greenwood');
          200  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,15179,11972,'Mink');
          201  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',68,17411,14035,'Pilot');
          202  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',2,18214,14727,'Reflections');
          203  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',4,21250,17481,'The Apartment');
          204  +  INSERT INTO sqlite_stat3 VALUES('items','i_title',1,24287,20283,'The Simpsons Already Did It');
          205  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',4315,95,2,1);
          206  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1553,4410,3,2);
          207  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1485,5963,4,3);
          208  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1414,7448,5,4);
          209  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1367,8862,6,5);
          210  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1328,10229,7,6);
          211  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1161,11557,8,7);
          212  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1108,12718,9,8);
          213  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1033,13826,10,9);
          214  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',1014,14859,11,10);
          215  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',929,15873,12,11);
          216  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',906,16802,13,12);
          217  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',844,17708,14,13);
          218  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',690,18552,15,14);
          219  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',655,19242,16,15);
          220  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',625,19897,17,16);
          221  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',579,20522,18,17);
          222  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',555,21101,19,18);
          223  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',526,21656,20,19);
          224  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',501,22182,21,20);
          225  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',459,22683,22,21);
          226  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',439,23142,23,22);
          227  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',315,23581,24,23);
          228  +  INSERT INTO sqlite_stat3 VALUES('items','i_index',192,24177,26,25);
          229  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1851,0,0,NULL);
          230  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',373,1857,2,'2011-10-22 14:54:39');
          231  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',595,2230,3,'2011-10-22 14:54:41');
          232  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',337,2825,4,'2011-10-22 14:54:43');
          233  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',361,3378,8,'2011-10-22 14:54:54');
          234  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',160,3739,9,'2011-10-22 14:54:56');
          235  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',315,4000,11,'2011-10-22 14:54:59');
          236  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',321,4334,13,'2011-10-22 14:55:02');
          237  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1292,4723,16,'2011-10-22 14:55:06');
          238  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',161,6015,17,'2011-10-22 14:55:07');
          239  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,9107,2677,'2012-09-04 18:07:50');
          240  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',313,9717,3270,'2012-10-18 16:50:21');
          241  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',450,10030,3271,'2012-10-18 16:50:22');
          242  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',389,10668,3275,'2012-10-18 16:50:26');
          243  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',796,11057,3276,'2012-10-18 16:51:06');
          244  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',161,12041,3280,'2012-10-19 19:52:37');
          245  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',135,13281,4186,'2013-02-19 00:56:10');
          246  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1063,13416,4187,'2013-02-19 00:56:11');
          247  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',797,14479,4188,'2013-02-19 00:56:13');
          248  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',147,15276,4189,'2013-02-19 00:56:15');
          249  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',346,15423,4190,'2013-02-19 00:56:16');
          250  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,18215,6436,'2013-05-05 14:09:54');
          251  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',2,21251,8122,'2013-05-24 15:25:45');
          252  +  INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,24287,11116,'2013-05-26 14:17:39');
          253  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',2560,0,0,NULL);
          254  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',18,3022,31,2350);
          255  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',10,6068,285,8150);
          256  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',158,6346,315,8949);
          257  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',34,9094,562,18831);
          258  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',20,12139,794,22838);
          259  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',134,14033,886,24739);
          260  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',159,14167,887,24740);
          261  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,14326,888,24741);
          262  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,14487,889,24742);
          263  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',124,14648,890,24743);
          264  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',157,14772,891,24744);
          265  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',126,15043,894,24747);
          266  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',40,15169,895,24748);
          267  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15243,898,24753);
          268  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',138,15404,899,24754);
          269  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',160,15542,900,24755);
          270  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15702,901,24756);
          271  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15863,902,24757);
          272  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',124,16024,903,24758);
          273  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',155,16148,904,24759);
          274  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',26,18208,1043,29704);
          275  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',2,21251,1282,32952);
          276  +  INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',13,24279,1583,36068);
          277  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid',25167,0,0,2);
          278  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid',736,25167,1,3);
          279  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid',15,25903,2,4);
          280  +  INSERT INTO sqlite_stat3 VALUES('items','i_secid',1398,25918,3,5);
          281  +  ANALYZE sqlite_master;
          282  +  
          283  +  explain query plan
          284  +  select items.title
          285  +    from items
          286  +         join items as child on child.parent_id=items.id
          287  +         join items as grandchild on grandchild.parent_id=child.id
          288  +         join settings
          289  +                    on settings.guid=grandchild.guid
          290  +                   and settings.account_id=1
          291  +   where items.metadata_type=2
          292  +     and items.secid=2
          293  +     and settings.last_viewed_at is not null
          294  +   group by items.id
          295  +   order by settings.last_viewed_at desc
          296  +   limit 10;
          297  +} [list \
          298  + 0 0 3 {SEARCH TABLE settings USING INDEX s_account_id (account_id=?)} \
          299  + 0 1 2 {SEARCH TABLE items AS grandchild USING INDEX i_guid (guid=?)} \
          300  + 0 2 1 {SEARCH TABLE items AS child USING INTEGER PRIMARY KEY (rowid=?)} \
          301  + 0 3 0 {SEARCH TABLE items USING INTEGER PRIMARY KEY (rowid=?)} \
          302  + 0 0 0 {USE TEMP B-TREE FOR GROUP BY} \
          303  + 0 0 0 {USE TEMP B-TREE FOR ORDER BY}]
          304  +
          305  +
          306  +finish_test