test/transitive1.test

   1 # 2013 April 17
   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 script is testing of transitive WHERE clause constraints
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 do_execsql_test transitive1-100 {
  19   CREATE TABLE t1(a TEXT, b TEXT, c TEXT COLLATE NOCASE);
  20   INSERT INTO t1 VALUES('abc','abc','Abc');
  21   INSERT INTO t1 VALUES('def','def','def');
  22   INSERT INTO t1 VALUES('ghi','ghi','GHI');
  23   CREATE INDEX t1a1 ON t1(a);
  24   CREATE INDEX t1a2 ON t1(a COLLATE nocase);
  25
  26   SELECT * FROM t1 WHERE a=b AND c=b AND c='DEF';
  27 } {def def def}
  28 do_execsql_test transitive1-110 {
  29   SELECT * FROM t1 WHERE a=b AND c=b AND c>='DEF' ORDER BY +a;
  30 } {def def def ghi ghi GHI}
  31 do_execsql_test transitive1-120 {
  32   SELECT * FROM t1 WHERE a=b AND c=b AND c<='DEF' ORDER BY +a;
  33 } {abc abc Abc def def def}
  34
  35 do_execsql_test transitive1-200 {
  36   CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
  37   INSERT INTO t2 VALUES(100,100,100);
  38   INSERT INTO t2 VALUES(20,20,20);
  39   INSERT INTO t2 VALUES(3,3,3);
  40
  41   SELECT * FROM t2 WHERE a=b AND c=b AND c=20;
  42 } {20 20 20}
  43 do_execsql_test transitive1-210 {
  44   SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
  45 } {3 3 3 20 20 20}
  46 do_execsql_test transitive1-220 {
  47   SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
  48 } {20 20 20 100 100 100}
  49
  50 # Test cases for ticket [[d805526eae253103] 2013-07-08
  51 # "Incorrect join result or assertion fault due to transitive constraints"
  52 #
  53 do_execsql_test transitive1-300 {
  54   CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
  55   CREATE TABLE t302(y INTEGER UNIQUE, z);
  56   INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
  57   INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
  58   SELECT *
  59     FROM t301 CROSS JOIN t302
  60    WHERE w=y AND y IS NOT NULL
  61    ORDER BY +w;
  62 } {1 2 1 3 3 4 3 6 5 6 5 7}
  63 do_execsql_test transitive1-301 {
  64   SELECT *
  65     FROM t301 CROSS JOIN t302
  66    WHERE w=y AND y IS NOT NULL
  67    ORDER BY w;
  68 } {1 2 1 3 3 4 3 6 5 6 5 7}
  69 do_execsql_test transitive1-302 {
  70   SELECT *
  71     FROM t301 CROSS JOIN t302
  72    WHERE w IS y AND y IS NOT NULL
  73    ORDER BY w;
  74 } {1 2 1 3 3 4 3 6 5 6 5 7}
  75 do_execsql_test transitive1-310 {
  76   SELECT *
  77     FROM t301 CROSS JOIN t302 ON w=y
  78    WHERE y>1
  79    ORDER BY +w
  80 } {3 4 3 6 5 6 5 7}
  81 do_execsql_test transitive1-311 {
  82   SELECT *
  83     FROM t301 CROSS JOIN t302 ON w=y
  84    WHERE y>1
  85    ORDER BY w
  86 } {3 4 3 6 5 6 5 7}
  87 do_execsql_test transitive1-312 {
  88   SELECT *
  89     FROM t301 CROSS JOIN t302 ON w=y
  90    WHERE y>1
  91    ORDER BY w DESC
  92 } {5 6 5 7 3 4 3 6}
  93 do_execsql_test transitive1-320 {
  94   SELECT *
  95     FROM t301 CROSS JOIN t302 ON w=y
  96    WHERE y BETWEEN 2 AND 4;
  97 } {3 4 3 6}
  98 do_execsql_test transitive1-331 {
  99   SELECT *
 100     FROM t301 CROSS JOIN t302 ON w=y
 101    WHERE y BETWEEN 1 AND 4
 102    ORDER BY w;
 103 } {1 2 1 3 3 4 3 6}
 104 do_execsql_test transitive1-332 {
 105   SELECT *
 106     FROM t301 CROSS JOIN t302 ON w=y
 107    WHERE y BETWEEN 1 AND 4
 108    ORDER BY w DESC;
 109 } {3 4 3 6 1 2 1 3}
 110
 111 # Ticket [c620261b5b5dc] circa 2013-10-28.
 112 # Make sure constraints are not used with LEFT JOINs.
 113 #
 114 # The next case is from the ticket report.  It outputs no rows in 3.8.1
 115 # prior to the bug-fix.
 116 #
 117 do_execsql_test transitive1-400 {
 118   CREATE TABLE t401(a);
 119   CREATE TABLE t402(b);
 120   CREATE TABLE t403(c INTEGER PRIMARY KEY);
 121   INSERT INTO t401 VALUES(1);
 122   INSERT INTO t403 VALUES(1);
 123   SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
 124 } {1-row}
 125 do_execsql_test transitive1-401 {
 126   SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
 127 } {1-row}
 128 do_execsql_test transitive1-402 {
 129   SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
 130 } {1-row}
 131 do_execsql_test transitive1-403 {
 132   SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
 133 } {1-row}
 134
 135
 136 # The following is a script distilled from the XBMC project where the
 137 # bug was originally encountered.  The correct answer is a single row
 138 # of output.  Before the bug was fixed, zero rows were generated.
 139 #
 140 do_execsql_test transitive1-410 {
 141   CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
 142   CREATE TABLE path ( idPath integer primary key, strPath text, strContent text, strScraper text, strHash text, scanRecursive integer, useFolderNames bool, strSettings text, noUpdate bool, exclude bool, dateAdded text);
 143   INSERT INTO "path" VALUES(1,'/tmp/tvshows/','tvshows','metadata.tvdb.com','989B1CE5680A14F5F86123F751169B49',0,0,'<settings><setting id="absolutenumber" value="false" /><setting id="dvdorder" value="false" /><setting id="fanart" value="true" /><setting id="language" value="en" /></settings>',0,0,NULL);
 144   INSERT INTO "path" VALUES(2,'/tmp/tvshows/The.Big.Bang.Theory/','','','85E1DAAB2F5FF6EAE8AEDF1B5C882D1E',NULL,NULL,NULL,NULL,NULL,'2013-10-23 18:58:43');
 145   CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename text, playCount integer, lastPlayed text, dateAdded text);
 146   INSERT INTO "files" VALUES(1,2,'The.Big.Bang.Theory.S01E01.WEB-DL.AAC2.0.H264.mkv',NULL,NULL,'2013-10-23 18:57:36');
 147   CREATE TABLE tvshow ( idShow integer primary key,c00 text,c01 text,c02 text,c03 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c12 text,c13 text,c14 text,c15 text,c16 text,c17 text,c18 text,c19 text,c20 text,c21 text,c22 text,c23 text);
 148   INSERT INTO "tvshow" VALUES(1,'The Big Bang Theory','Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited beauty named Penny moves in next door. Sheldon, Leonard''s roommate, is quite content spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leonard sees in Penny a whole new universe of possibilities... including love.','','','9.200000','2007-09-24','<thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g13.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g23.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g18.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g17.jpg</thumb><thumb aspect="banner">http://
 149   thetvdb.com/banners/graphical/80379-g6.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g5.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g2.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g11.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g12.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g19.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g3.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g4.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g15.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g22.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g7.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g10.jpg</thumb><thumb
 150   aspect="banner">http://thetvdb.com/banners/graphical/80379-g24.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g8.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g9.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g14.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g16.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g21.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/text/80379-4.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/text/80379-2.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/text/80379-3.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/text/80379-5.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-8.jpg</thumb><thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/seasons/80379-0-4.jpg</thumb><thumb aspect="poster" type="season"
 151   season="1">http://thetvdb.com/banners/seasons/80379-1-12.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-9.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-11.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-9.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-8.jpg</thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7-3.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-4.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-5.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-9.jpg</thumb><thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/seasons/80379-0-2.jpg</thumb><thumb aspect="
 152   poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-6.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-4.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-2.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-9.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-7.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-10.jpg</
 153   thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-5.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-5.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-4.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-3.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-6.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-
 154   1-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-3.jpg</thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7-2.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-2.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-5.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-3.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-5.jpg</thumb><thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/seasons/80379-0.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-5.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/
 155   seasons/80379-1-6.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-3.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-8.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-8.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-7.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-6.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-8.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-11.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-10.jpg</thumb><thumb aspect="poster" type="season" season="1">http://
 156   thetvdb.com/banners/seasons/80379-1-8.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-7.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-3.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-4.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-3.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-7.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-6.jpg</thumb><thumb aspect="poster" type="season"
 157   season="3">http://thetvdb.com/banners/seasons/80379-3-2.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-6.jpg</thumb><thumb aspect="banner" type="season" season="5">http://thetvdb.com/banners/seasonswide/80379-5.jpg</thumb><thumb aspect="banner" type="season" season="3">http://thetvdb.com/banners/seasonswide/80379-3-2.jpg</thumb><thumb aspect="banner" type="season" season="1">http://thetvdb.com/banners/seasonswide/80379-1-2.jpg</thumb><thumb aspect="banner" type="season" season="2">http://thetvdb.com/banners/seasonswide/80379-2-2.jpg</thumb><thumb aspect="banner" type="season" season="4">http://thetvdb.com/banners/seasonswide/80379-4-2.jpg</thumb><thumb aspect="banner" type="season" season="0">http://thetvdb.com/banners/seasonswide/80379-0.jpg</thumb><thumb aspect="banner" type="season" season="0">http://thetvdb.com/banners/seasonswide/80379-0-2.jpg</thumb><thumb aspect="banner" type="season" season="1">http://thetvdb.com/banners/seasonswide/80379-1.jpg</
 158   thumb><thumb aspect="banner" type="season" season="2">http://thetvdb.com/banners/seasonswide/80379-2.jpg</thumb><thumb aspect="banner" type="season" season="4">http://thetvdb.com/banners/seasonswide/80379-4.jpg</thumb><thumb aspect="banner" type="season" season="3">http://thetvdb.com/banners/seasonswide/80379-3.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-22.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-2.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-19.jpg</
 159   thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-8.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-4.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-20.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-23.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-7.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-21.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb
 160   aspect="poster">http://thetvdb.com/banners/posters/80379-5.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-22.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-19.jpg</thumb><thumb aspect="
 161   poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-8.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-20.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-23.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-7.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-21.jpg</
 162   thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-5.jpg</thumb>','','Comedy','','<episodeguide><url cache="80379-en.xml">http://thetvdb.com/api/1D62F2F90030C444/series/80379/all/en.zip</url></episodeguide>','<fanart url="http://thetvdb.com/banners/"><thumb dim="1920x1080" colors="|192,185,169|19,20,25|57,70,89|" preview="_cache/fanart/original/80379-2.jpg">fanart/original/80379-2.jpg</thumb><thumb dim="1920x1080" colors="|94,28,16|194,18,38|0,0,8|" preview="_cache/fanart/original/80379-34.jpg">fanart/original/80379-34.jpg</thumb><thumb dim="1280x720" colors="|254,157,210|11,12,7|191,152,111|" preview="_cache/fanart/original/80379-4.jpg">fanart/original/80379-
 163   4.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-42.jpg">fanart/original/80379-42.jpg</thumb><thumb dim="1920x1080" colors="|236,187,155|136,136,128|254,254,252|" preview="_cache/fanart/original/80379-37.jpg">fanart/original/80379-37.jpg</thumb><thumb dim="1920x1080" colors="|112,102,152|116,109,116|235,152,146|" preview="_cache/fanart/original/80379-14.jpg">fanart/original/80379-14.jpg</thumb><thumb dim="1920x1080" colors="|150,158,161|174,75,121|150,98,58|" preview="_cache/fanart/original/80379-16.jpg">fanart/original/80379-16.jpg</thumb><thumb dim="1280x720" colors="|224,200,176|11,1,28|164,96,0|" preview="_cache/fanart/original/80379-1.jpg">fanart/original/80379-1.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-35.jpg">fanart/original/80379-35.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-40.jpg">fanart/original/80379-40.jpg</thumb><thumb dim="1920x1080" colors="|255,255,255|30,19,13|155,112,70|"
 164   preview="_cache/fanart/original/80379-31.jpg">fanart/original/80379-31.jpg</thumb><thumb dim="1920x1080" colors="|241,195,172|84,54,106|254,221,206|" preview="_cache/fanart/original/80379-29.jpg">fanart/original/80379-29.jpg</thumb><thumb dim="1280x720" colors="|197,167,175|219,29,39|244,208,192|" preview="_cache/fanart/original/80379-11.jpg">fanart/original/80379-11.jpg</thumb><thumb dim="1280x720" colors="|195,129,97|244,192,168|219,148,118|" preview="_cache/fanart/original/80379-24.jpg">fanart/original/80379-24.jpg</thumb><thumb dim="1920x1080" colors="|14,10,11|255,255,255|175,167,164|" preview="_cache/fanart/original/80379-30.jpg">fanart/original/80379-30.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-19.jpg">fanart/original/80379-19.jpg</thumb><thumb dim="1920x1080" colors="|246,199,69|98,55,38|161,127,82|" preview="_cache/fanart/original/80379-9.jpg">fanart/original/80379-9.jpg</thumb><thumb dim="1280x720" colors="|129,22,14|48,50,39|223,182,64|" preview="_cache/
 165   fanart/original/80379-13.jpg">fanart/original/80379-13.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-45.jpg">fanart/original/80379-45.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-33.jpg">fanart/original/80379-33.jpg</thumb><thumb dim="1280x720" colors="|103,77,60|224,180,153|129,100,84|" preview="_cache/fanart/original/80379-10.jpg">fanart/original/80379-10.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-23.jpg">fanart/original/80379-23.jpg</thumb><thumb dim="1280x720" colors="|219,29,39|0,4,10|88,117,135|" preview="_cache/fanart/original/80379-12.jpg">fanart/original/80379-12.jpg</thumb><thumb dim="1920x1080" colors="|226,209,165|51,18,9|89,54,24|" preview="_cache/fanart/original/80379-5.jpg">fanart/original/80379-5.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-26.jpg">fanart/original/80379-26.jpg</thumb><thumb dim="1280x720" colors="|249,251,229|126,47,53|251,226,
 166   107|" preview="_cache/fanart/original/80379-27.jpg">fanart/original/80379-27.jpg</thumb><thumb dim="1920x1080" colors="|233,218,65|30,27,46|173,53,18|" preview="_cache/fanart/original/80379-32.jpg">fanart/original/80379-32.jpg</thumb><thumb dim="1280x720" colors="|248,248,248|64,54,78|188,193,196|" preview="_cache/fanart/original/80379-3.jpg">fanart/original/80379-3.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-25.jpg">fanart/original/80379-25.jpg</thumb><thumb dim="1280x720" colors="|159,150,133|59,39,32|168,147,104|" preview="_cache/fanart/original/80379-7.jpg">fanart/original/80379-7.jpg</thumb><thumb dim="1920x1080" colors="|221,191,157|11,7,6|237,146,102|" preview="_cache/fanart/original/80379-21.jpg">fanart/original/80379-21.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-28.jpg">fanart/original/80379-28.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-36.jpg">fanart/original/80379-36.jpg</thumb><thumb
 167   dim="1920x1080" colors="|253,237,186|33,25,22|245,144,38|" preview="_cache/fanart/original/80379-38.jpg">fanart/original/80379-38.jpg</thumb><thumb dim="1920x1080" colors="|174,111,68|243,115,50|252,226,45|" preview="_cache/fanart/original/80379-20.jpg">fanart/original/80379-20.jpg</thumb><thumb dim="1920x1080" colors="|63,56,123|87,59,47|63,56,123|" preview="_cache/fanart/original/80379-17.jpg">fanart/original/80379-17.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-43.jpg">fanart/original/80379-43.jpg</thumb><thumb dim="1280x720" colors="|69,68,161|142,118,142|222,191,137|" preview="_cache/fanart/original/80379-22.jpg">fanart/original/80379-22.jpg</thumb><thumb dim="1280x720" colors="|1,108,206|242,209,192|250,197,163|" preview="_cache/fanart/original/80379-15.jpg">fanart/original/80379-15.jpg</thumb><thumb dim="1280x720" colors="|239,229,237|0,0,0|167,136,115|" preview="_cache/fanart/original/80379-18.jpg">fanart/original/80379-18.jpg</thumb><thumb dim="1280x720" colors=""
 168   preview="_cache/fanart/original/80379-6.jpg">fanart/original/80379-6.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-8.jpg">fanart/original/80379-8.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-41.jpg">fanart/original/80379-41.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-44.jpg">fanart/original/80379-44.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-39.jpg">fanart/original/80379-39.jpg</thumb></fanart>','80379','TV-PG','CBS','','/tmp/tvshows/The.Big.Bang.Theory/','1',NULL,NULL,NULL,NULL,NULL,NULL);
 169   CREATE TABLE episode ( idEpisode integer primary key, idFile integer,c00 text,c01 text,c02 text,c03 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c12 varchar(24),c13 varchar(24),c14 text,c15 text,c16 text,c17 varchar(24),c18 text,c19 text,c20 text,c21 text,c22 text,c23 text, idShow integer);
 170   INSERT INTO "episode" VALUES(1,1,'Pilot','Brilliant physicist roommates Leonard and Sheldon meet their new neighbor Penny, who begins showing them that as much as they know about science, they know little about actual living.','','7.700000','Chuck Lorre / Bill Prady','2007-09-24','<thumb>http://thetvdb.com/banners/episodes/80379/332484.jpg</thumb>','',NULL,'1800','James Burrows','','1','1','','-1','-1','-1','/tmp/tvshows/The.Big.Bang.Theory/The.Big.Bang.Theory.S01E01.WEB-DL.AAC2.0.H264.mkv','2','332484',NULL,NULL,NULL,1);
 171   CREATE TABLE tvshowlinkpath (idShow integer, idPath integer);
 172   INSERT INTO "tvshowlinkpath" VALUES(1,2);
 173   CREATE TABLE seasons ( idSeason integer primary key, idShow integer, season integer);
 174   INSERT INTO "seasons" VALUES(1,1,-1);
 175   INSERT INTO "seasons" VALUES(2,1,0);
 176   INSERT INTO "seasons" VALUES(3,1,1);
 177   INSERT INTO "seasons" VALUES(4,1,2);
 178   INSERT INTO "seasons" VALUES(5,1,3);
 179   INSERT INTO "seasons" VALUES(6,1,4);
 180   INSERT INTO "seasons" VALUES(7,1,5);
 181   INSERT INTO "seasons" VALUES(8,1,6);
 182   INSERT INTO "seasons" VALUES(9,1,7);
 183   CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT);
 184   INSERT INTO "art" VALUES(1,1,'actor','thumb','http://thetvdb.com/banners/actors/73597.jpg');
 185   INSERT INTO "art" VALUES(2,2,'actor','thumb','http://thetvdb.com/banners/actors/73596.jpg');
 186   INSERT INTO "art" VALUES(3,3,'actor','thumb','http://thetvdb.com/banners/actors/73595.jpg');
 187   INSERT INTO "art" VALUES(4,4,'actor','thumb','http://thetvdb.com/banners/actors/73599.jpg');
 188   INSERT INTO "art" VALUES(5,5,'actor','thumb','http://thetvdb.com/banners/actors/73598.jpg');
 189   INSERT INTO "art" VALUES(6,6,'actor','thumb','http://thetvdb.com/banners/actors/283158.jpg');
 190   INSERT INTO "art" VALUES(7,7,'actor','thumb','http://thetvdb.com/banners/actors/283157.jpg');
 191   INSERT INTO "art" VALUES(8,8,'actor','thumb','http://thetvdb.com/banners/actors/91271.jpg');
 192   INSERT INTO "art" VALUES(9,9,'actor','thumb','http://thetvdb.com/banners/actors/294178.jpg');
 193   INSERT INTO "art" VALUES(10,10,'actor','thumb','http://thetvdb.com/banners/actors/283159.jpg');
 194   INSERT INTO "art" VALUES(11,1,'tvshow','banner','http://thetvdb.com/banners/graphical/80379-g13.jpg');
 195   INSERT INTO "art" VALUES(12,1,'tvshow','fanart','http://thetvdb.com/banners/fanart/original/80379-2.jpg');
 196   INSERT INTO "art" VALUES(13,1,'tvshow','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
 197   INSERT INTO "art" VALUES(14,1,'season','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
 198   INSERT INTO "art" VALUES(15,2,'season','banner','http://thetvdb.com/banners/seasonswide/80379-0.jpg');
 199   INSERT INTO "art" VALUES(16,2,'season','poster','http://thetvdb.com/banners/seasons/80379-0-4.jpg');
 200   INSERT INTO "art" VALUES(17,3,'season','banner','http://thetvdb.com/banners/seasonswide/80379-1-2.jpg');
 201   INSERT INTO "art" VALUES(18,3,'season','poster','http://thetvdb.com/banners/seasons/80379-1-12.jpg');
 202   INSERT INTO "art" VALUES(19,4,'season','banner','http://thetvdb.com/banners/seasonswide/80379-2-2.jpg');
 203   INSERT INTO "art" VALUES(20,4,'season','poster','http://thetvdb.com/banners/seasons/80379-2-11.jpg');
 204   INSERT INTO "art" VALUES(21,5,'season','banner','http://thetvdb.com/banners/seasonswide/80379-3-2.jpg');
 205   INSERT INTO "art" VALUES(22,5,'season','poster','http://thetvdb.com/banners/seasons/80379-3-9.jpg');
 206   INSERT INTO "art" VALUES(23,6,'season','banner','http://thetvdb.com/banners/seasonswide/80379-4-2.jpg');
 207   INSERT INTO "art" VALUES(24,6,'season','poster','http://thetvdb.com/banners/seasons/80379-4-8.jpg');
 208   INSERT INTO "art" VALUES(25,7,'season','banner','http://thetvdb.com/banners/seasonswide/80379-5.jpg');
 209   INSERT INTO "art" VALUES(26,7,'season','poster','http://thetvdb.com/banners/seasons/80379-5-9.jpg');
 210   INSERT INTO "art" VALUES(27,8,'season','poster','http://thetvdb.com/banners/seasons/80379-6-8.jpg');
 211   INSERT INTO "art" VALUES(28,9,'season','poster','http://thetvdb.com/banners/seasons/80379-7-3.jpg');
 212   INSERT INTO "art" VALUES(29,1,'episode','thumb','http://thetvdb.com/banners/episodes/80379/332484.jpg');
 213   CREATE INDEX ix_bookmark ON bookmark (idFile, type);
 214   CREATE INDEX ix_path ON path ( strPath );
 215   CREATE INDEX ix_files ON files ( idPath, strFilename );
 216   CREATE UNIQUE INDEX ix_episode_file_1 on episode (idEpisode, idFile);
 217   CREATE UNIQUE INDEX id_episode_file_2 on episode (idFile, idEpisode);
 218   CREATE INDEX ix_episode_season_episode on episode (c12, c13);
 219   CREATE INDEX ix_episode_bookmark on episode (c17);
 220   CREATE INDEX ix_episode_show1 on episode(idEpisode,idShow);
 221   CREATE INDEX ix_episode_show2 on episode(idShow,idEpisode);
 222   CREATE UNIQUE INDEX ix_tvshowlinkpath_1 ON tvshowlinkpath ( idShow, idPath );
 223   CREATE UNIQUE INDEX ix_tvshowlinkpath_2 ON tvshowlinkpath ( idPath, idShow );
 224   CREATE INDEX ixEpisodeBasePath ON episode ( c19 );
 225   CREATE INDEX ixTVShowBasePath on tvshow ( c17 );
 226   CREATE INDEX ix_seasons ON seasons (idShow, season);
 227   CREATE INDEX ix_art ON art(media_id, media_type, type);
 228   CREATE VIEW episodeview
 229   AS
 230     SELECT episode.*,
 231            files.strfilename           AS strFileName,
 232            path.strpath                AS strPath,
 233            files.playcount             AS playCount,
 234            files.lastplayed            AS lastPlayed,
 235            files.dateadded             AS dateAdded,
 236            tvshow.c00                  AS strTitle,
 237            tvshow.c14                  AS strStudio,
 238            tvshow.c05                  AS premiered,
 239            tvshow.c13                  AS mpaa,
 240            tvshow.c16                  AS strShowPath,
 241            bookmark.timeinseconds      AS resumeTimeInSeconds,
 242            bookmark.totaltimeinseconds AS totalTimeInSeconds,
 243            seasons.idseason            AS idSeason
 244     FROM   episode
 245            JOIN files
 246              ON files.idfile = episode.idfile
 247            JOIN tvshow
 248              ON tvshow.idshow = episode.idshow
 249            LEFT JOIN seasons
 250                   ON seasons.idshow = episode.idshow
 251                      AND seasons.season = episode.c12
 252            JOIN path
 253              ON files.idpath = path.idpath
 254            LEFT JOIN bookmark
 255                   ON bookmark.idfile = episode.idfile
 256                      AND bookmark.type = 1;
 257   CREATE VIEW tvshowview
 258   AS
 259     SELECT tvshow.*,
 260            path.strpath                              AS strPath,
 261            path.dateadded                            AS dateAdded,
 262            Max(files.lastplayed)                     AS lastPlayed,
 263            NULLIF(Count(episode.c12), 0)             AS totalCount,
 264            Count(files.playcount)                    AS watchedcount,
 265            NULLIF(Count(DISTINCT( episode.c12 )), 0) AS totalSeasons
 266     FROM   tvshow
 267            LEFT JOIN tvshowlinkpath
 268                   ON tvshowlinkpath.idshow = tvshow.idshow
 269            LEFT JOIN path
 270                   ON path.idpath = tvshowlinkpath.idpath
 271            LEFT JOIN episode
 272                   ON episode.idshow = tvshow.idshow
 273            LEFT JOIN files
 274                   ON files.idfile = episode.idfile
 275     GROUP  BY tvshow.idshow;
 276   SELECT
 277     episodeview.c12,
 278     path.strPath,
 279     tvshowview.c00,
 280     tvshowview.c01,
 281     tvshowview.c05,
 282     tvshowview.c08,
 283     tvshowview.c14,
 284     tvshowview.c13,
 285     seasons.idSeason,
 286     count(1),
 287     count(files.playCount)
 288   FROM episodeview
 289       JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
 290       JOIN seasons ON (seasons.idShow = tvshowview.idShow
 291                        AND seasons.season = episodeview.c12)
 292       JOIN files ON files.idFile = episodeview.idFile
 293       JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
 294       JOIN path ON path.idPath = tvshowlinkpath.idPath
 295   WHERE tvshowview.idShow = 1
 296   GROUP BY episodeview.c12;
 297 } {1 /tmp/tvshows/The.Big.Bang.Theory/ {The Big Bang Theory} {Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited beauty named Penny moves in next door. Sheldon, Leonard's roommate, is quite content spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leonard sees in Penny a whole new universe of possibilities... including love.} 2007-09-24 Comedy CBS TV-PG 3 1 0}
 298
 299 ##############################################################################
 300 # 2015-05-18.  Make sure transitive constraints are avoided when column
 301 # affinities and collating sequences get in the way.
 302 #
 303 db close
 304 forcedelete test.db
 305 sqlite3 db test.db
 306 do_execsql_test transitive1-500 {
 307   CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
 308   INSERT INTO x VALUES(10, '10');
 309   SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
 310 } {10 10}
 311 do_execsql_test transitive1-510 {
 312   CREATE TABLE t1(x TEXT);
 313   CREATE TABLE t2(y TEXT);
 314   INSERT INTO t1 VALUES('abc');
 315   INSERT INTO t2 VALUES('ABC');
 316   SELECT * FROM t1 CROSS JOIN t2 WHERE (x=y COLLATE nocase) AND y='ABC';
 317 } {abc ABC}
 318 do_execsql_test transitive1-520 {
 319   CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
 320   INSERT INTO t3 VALUES(10, '10');
 321   SELECT * FROM t3 WHERE i=t AND t = '10 ';
 322 } {}
 323 do_execsql_test transitive1-530 {
 324   CREATE TABLE u1(x TEXT, y INTEGER, z TEXT);
 325   CREATE INDEX i1 ON u1(x);
 326   INSERT INTO u1 VALUES('00013', 13, '013');
 327   SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
 328 } {00013 13 013}
 329 do_execsql_test transitive1-540 {
 330   CREATE TABLE b1(x, y);
 331   INSERT INTO b1 VALUES('abc', 'ABC');
 332   CREATE INDEX b1x ON b1(x);
 333   SELECT * FROM b1 WHERE (x=y COLLATE nocase) AND y='ABC';
 334 } {abc ABC}
 335 do_execsql_test transitive1-550 {
 336   CREATE TABLE c1(x, y COLLATE nocase, z);
 337   INSERT INTO c1 VALUES('ABC', 'ABC', 'abc');
 338   SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
 339 } {ABC ABC abc}
 340 do_execsql_test transitive1-560 {
 341   CREATE INDEX c1x ON c1(x);
 342   SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
 343 } {ABC ABC abc}
 344 do_execsql_test transitive1-560eqp {
 345   EXPLAIN QUERY PLAN
 346   SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
 347 } {/SCAN TABLE c1/}
 348 do_execsql_test transitive1-570 {
 349   SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
 350 } {}
 351 do_execsql_test transitive1-570eqp {
 352   EXPLAIN QUERY PLAN
 353   SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
 354 } {/SEARCH TABLE c1 USING INDEX c1x/}
 355
 356 finish_test