Chromium Code Reviews
chromiumcodereview-hr@appspot.gserviceaccount.com (chromiumcodereview-hr) | Please choose your nickname with Settings | Help | Chromium Project | Gerrit Changes | Sign out
(91)

Issues Search
    My Issues | Starred     Open | Closed | All

Side by Side Diff: third_party/sqlite/sqlite-src-3170000/test/transitive1.test

Issue 2747283002: [sql] Import reference version of SQLite 3.17.. (Closed)
Patch Set: Created 3 years, 9 months ago
Use n/p to move between diff chunks; N/P to move between comments. Draft comments are only viewable by you.
Jump to:
View unified diff | Download patch
« no previous file with comments | « third_party/sqlite/sqlite-src-3170000/test/trans3.test ('k') | third_party/sqlite/sqlite-src-3170000/test/trigger1.test » ('j') | no next file with comments »
Toggle Intra-line Diffs ('i') | Expand Comments ('e') | Collapse Comments ('c') | Show Comments Hide Comments ('s')
OLDNEW
(Empty)
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, timeIn Seconds double, totalTimeInSeconds double, thumbNailImage text, player text, pla yerState text, type integer);
142 CREATE TABLE path ( idPath integer primary key, strPath text, strContent text, strScraper text, strHash text, scanRecursive integer, useFolderNames bool, strS ettings text, noUpdate bool, exclude bool, dateAdded text);
143 INSERT INTO "path" VALUES(1,'/tmp/tvshows/','tvshows','metadata.tvdb.com','989 B1CE5680A14F5F86123F751169B49',0,0,'<settings><setting id="absolutenumber" value ="false" /><setting id="dvdorder" value="false" /><setting id="fanart" value="tr ue" /><setting id="language" value="en" /></settings>',0,0,NULL);
144 INSERT INTO "path" VALUES(2,'/tmp/tvshows/The.Big.Bang.Theory/','','','85E1DAA B2F5FF6EAE8AEDF1B5C882D1E',NULL,NULL,NULL,NULL,NULL,'2013-10-23 18:58:43');
145 CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename t ext, 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,c0 3 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c 12 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 Sh eldon Cooper are brilliant physicists, the kind of "beautiful minds" that unders tand how the universe works. But none of that genius helps them interact with pe ople, especially women. All this begins to change when a free-spirited beauty na med Penny moves in next door. Sheldon, Leonard''s roommate, is quite content spe nding his nights playing Klingon Boggle with their socially dysfunctional friend s, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leon ard sees in Penny a whole new universe of possibilities... including love.','',' ','9.200000','2007-09-24','<thumb aspect="banner">http://thetvdb.com/banners/gra phical/80379-g13.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/gr aphical/80379-g23.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/g raphical/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">htt p://thetvdb.com/banners/graphical/80379-g19.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g3.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g4.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g15.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g22.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g7.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g10.jpg</thumb><thumb
150 aspect="banner">http://thetvdb.com/banners/graphical/80379-g24.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g8.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g9.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g14.jpg</thumb><th umb aspect="banner">http://thetvdb.com/banners/graphical/80379-g16.jpg</thumb><t humb 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="bann er">http://thetvdb.com/banners/text/80379-5.jpg</thumb><thumb aspect="poster" ty pe="season" season="6">http://thetvdb.com/banners/seasons/80379-6-8.jpg</thumb>< thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/season s/80379-0-4.jpg</thumb><thumb aspect="poster" type="season"
151 season="1">http://thetvdb.com/banners/seasons/80379-1-12.jpg</thumb><thumb asp ect="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" seas on="5">http://thetvdb.com/banners/seasons/80379-5-9.jpg</thumb><thumb aspect="po ster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-8.jpg< /thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banner s/seasons/80379-7-3.jpg</thumb><thumb aspect="poster" type="season" season="3">h ttp://thetvdb.com/banners/seasons/80379-3-4.jpg</thumb><thumb aspect="poster" ty pe="season" season="4">http://thetvdb.com/banners/seasons/80379-4-5.jpg</thumb>< thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/season s/80379-2-9.jpg</thumb><thumb aspect="poster" type="season" season="0">http://th etvdb.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/ba nners/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</thu mb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/se asons/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="se ason" season="3">http://thetvdb.com/banners/seasons/80379-3.jpg</thumb><thumb as pect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379- 4-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.co m/banners/seasons/80379-5-7.jpg</thumb><thumb aspect="poster" type="season" seas on="2">http://thetvdb.com/banners/seasons/80379-2-10.jpg</
153 thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banne rs/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" t ype="season" season="5">http://thetvdb.com/banners/seasons/80379-5-4.jpg</thumb> <thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seaso ns/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="6">http://the tvdb.com/banners/seasons/80379-6-3.jpg</thumb><thumb aspect="poster" type="seaso n" season="5">http://thetvdb.com/banners/seasons/80379-5.jpg</thumb><thumb aspec t="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/b anners/seasons/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="7 ">http://thetvdb.com/banners/seasons/80379-7.jpg</thumb><thumb aspect="poster" t ype="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" se ason="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.jp g</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/bann ers/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/seas ons/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="seas on" season="3">http://thetvdb.com/banners/seasons/80379-3-5.jpg</thumb><thumb as pect="poster" type="season" season="1">http://thetvdb.com/banners/
155 seasons/80379-1-6.jpg</thumb><thumb aspect="poster" type="season" season="2">h ttp://thetvdb.com/banners/seasons/80379-2-3.jpg</thumb><thumb aspect="poster" ty pe="season" season="2">http://thetvdb.com/banners/seasons/80379-2-8.jpg</thumb>< thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/season s/80379-6-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://th etvdb.com/banners/seasons/80379-5-8.jpg</thumb><thumb aspect="poster" type="seas on" season="4">http://thetvdb.com/banners/seasons/80379-4-7.jpg</thumb><thumb as pect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379- 2-6.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.co m/banners/seasons/80379-3-8.jpg</thumb><thumb aspect="poster" type="season" seas on="1">http://thetvdb.com/banners/seasons/80379-1-11.jpg</thumb><thumb aspect="p oster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-10.jp g</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><thum b aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80 379-2-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvd b.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/ba nners/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/seas ons/80379-5-6.jpg</thumb><thumb aspect="poster" type="season"
157 season="3">http://thetvdb.com/banners/seasons/80379-3-2.jpg</thumb><thumb aspe ct="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" seas on="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.co m/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 as pect="banner" type="season" season="0">http://thetvdb.com/banners/seasonswide/80 379-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/banne rs/seasonswide/80379-2.jpg</thumb><thumb aspect="banner" type="season" season="4 ">http://thetvdb.com/banners/seasonswide/80379-4.jpg</thumb><thumb aspect="banne r" type="season" season="3">http://thetvdb.com/banners/seasonswide/80379-3.jpg</ thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-22.jpg</th umb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-18.jpg</thum b><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-13.jpg</thumb> <thumb aspect="poster">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><t humb aspect="poster">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thu mb aspect="poster">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb asp ect="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</t humb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-4.jpg</thum b><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-20.jpg</thumb> <thumb aspect="poster">http://thetvdb.com/banners/posters/80379-23.jpg</thumb><t humb aspect="poster">http://thetvdb.com/banners/posters/80379-7.jpg</thumb><thum b aspect="poster">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb a spect="poster">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb asp ect="poster">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspec t="poster">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect= "poster">http://thetvdb.com/banners/posters/80379-21.jpg</thumb><thumb aspect="p oster">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="pos ter">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poste r">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb
160 aspect="poster">http://thetvdb.com/banners/posters/80379-5.jpg</thumb><thumb a spect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/8037 9-22.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb. com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster" type="season" sea son="-1">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="p oster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-10.jpg </thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/bann ers/posters/80379-16.jpg</thumb><thumb aspect="poster" type="season" season="-1" >http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster" ty pe="season" season="-1">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><t humb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/poster s/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://the tvdb.com/banners/posters/80379-19.jpg</thumb><thumb aspect="
161 poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-8.j pg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/ba nners/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/pos ters/80379-7.jpg</thumb><thumb aspect="poster" type="season" season="-1">http:// thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster" type="seas on" season="-1">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb as pect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379 -11.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.c om/banners/posters/80379-15.jpg</thumb><thumb aspect="poster" type="season" seas on="-1">http://thetvdb.com/banners/posters/80379-21.jpg</
162 thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/bann ers/posters/80379-14.jpg</thumb><thumb aspect="poster" type="season" season="-1" >http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster" t ype="season" season="-1">http://thetvdb.com/banners/posters/80379-6.jpg</thumb>< thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/poste rs/80379-5.jpg</thumb>','','Comedy','','<episodeguide><url cache="80379-en.xml"> http://thetvdb.com/api/1D62F2F90030C444/series/80379/all/en.zip</url></episodegu ide>','<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">fa nart/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|" p review="_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,10 2,152|116,109,116|235,152,146|" preview="_cache/fanart/original/80379-14.jpg">fa nart/original/80379-14.jpg</thumb><thumb dim="1920x1080" colors="|150,158,161|17 4,75,121|150,98,58|" preview="_cache/fanart/original/80379-16.jpg">fanart/origin al/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</t humb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-35.j pg">fanart/original/80379-35.jpg</thumb><thumb dim="1920x1080" colors="" preview ="_cache/fanart/original/80379-40.jpg">fanart/original/80379-40.jpg</thumb><thum b 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</th umb><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/fan art/original/80379-11.jpg">fanart/original/80379-11.jpg</thumb><thumb dim="1280x 720" colors="|195,129,97|244,192,168|219,148,118|" preview="_cache/fanart/origin al/80379-24.jpg">fanart/original/80379-24.jpg</thumb><thumb dim="1920x1080" colo rs="|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="" previ ew="_cache/fanart/original/80379-19.jpg">fanart/original/80379-19.jpg</thumb><th umb dim="1920x1080" colors="|246,199,69|98,55,38|161,127,82|" preview="_cache/fa nart/original/80379-9.jpg">fanart/original/80379-9.jpg</thumb><thumb dim="1280x7 20" 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/origin al/80379-45.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/o riginal/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/8037 9-10.jpg">fanart/original/80379-10.jpg</thumb><thumb dim="1920x1080" colors="" p review="_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/fa nart/original/80379-12.jpg">fanart/original/80379-12.jpg</thumb><thumb dim="1920 x1080" colors="|226,209,165|51,18,9|89,54,24|" preview="_cache/fanart/original/8 0379-5.jpg">fanart/original/80379-5.jpg</thumb><thumb dim="1280x720" colors="" p review="_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.j pg</thumb><thumb dim="1920x1080" colors="|233,218,65|30,27,46|173,53,18|" previe w="_cache/fanart/original/80379-32.jpg">fanart/original/80379-32.jpg</thumb><thu mb dim="1280x720" colors="|248,248,248|64,54,78|188,193,196|" preview="_cache/fa nart/original/80379-3.jpg">fanart/original/80379-3.jpg</thumb><thumb dim="1280x7 20" colors="" preview="_cache/fanart/original/80379-25.jpg">fanart/original/8037 9-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</thum b><thumb dim="1920x1080" colors="|221,191,157|11,7,6|237,146,102|" preview="_cac he/fanart/original/80379-21.jpg">fanart/original/80379-21.jpg</thumb><thumb dim= "1280x720" colors="" preview="_cache/fanart/original/80379-28.jpg">fanart/origin al/80379-28.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/o riginal/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/fan art/original/80379-38.jpg">fanart/original/80379-38.jpg</thumb><thumb dim="1920x 1080" colors="|174,111,68|243,115,50|252,226,45|" preview="_cache/fanart/origina l/80379-20.jpg">fanart/original/80379-20.jpg</thumb><thumb dim="1920x1080" color s="|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 d im="1280x720" colors="|69,68,161|142,118,142|222,191,137|" preview="_cache/fanar t/original/80379-22.jpg">fanart/original/80379-22.jpg</thumb><thumb dim="1280x72 0" 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">f anart/original/80379-18.jpg</thumb><thumb dim="1280x720" colors=""
168 preview="_cache/fanart/original/80379-6.jpg">fanart/original/80379-6.jpg</thum b><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-8.jpg">f anart/original/80379-8.jpg</thumb><thumb dim="1280x720" colors="" preview="_cach e/fanart/original/80379-41.jpg">fanart/original/80379-41.jpg</thumb><thumb dim=" 1920x1080" colors="" preview="_cache/fanart/original/80379-44.jpg">fanart/origin al/80379-44.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/or iginal/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,NU LL);
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,c1 7 varchar(24),c18 text,c19 text,c20 text,c21 text,c22 text,c23 text, idShow inte ger);
170 INSERT INTO "episode" VALUES(1,1,'Pilot','Brilliant physicist roommates Leonar d and Sheldon meet their new neighbor Penny, who begins showing them that as muc h as they know about science, they know little about actual living.','','7.70000 0','Chuck Lorre / Bill Prady','2007-09-24','<thumb>http://thetvdb.com/banners/ep isodes/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 in teger);
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/actor s/73597.jpg');
185 INSERT INTO "art" VALUES(2,2,'actor','thumb','http://thetvdb.com/banners/actor s/73596.jpg');
186 INSERT INTO "art" VALUES(3,3,'actor','thumb','http://thetvdb.com/banners/actor s/73595.jpg');
187 INSERT INTO "art" VALUES(4,4,'actor','thumb','http://thetvdb.com/banners/actor s/73599.jpg');
188 INSERT INTO "art" VALUES(5,5,'actor','thumb','http://thetvdb.com/banners/actor s/73598.jpg');
189 INSERT INTO "art" VALUES(6,6,'actor','thumb','http://thetvdb.com/banners/actor s/283158.jpg');
190 INSERT INTO "art" VALUES(7,7,'actor','thumb','http://thetvdb.com/banners/actor s/283157.jpg');
191 INSERT INTO "art" VALUES(8,8,'actor','thumb','http://thetvdb.com/banners/actor s/91271.jpg');
192 INSERT INTO "art" VALUES(9,9,'actor','thumb','http://thetvdb.com/banners/actor s/294178.jpg');
193 INSERT INTO "art" VALUES(10,10,'actor','thumb','http://thetvdb.com/banners/act ors/283159.jpg');
194 INSERT INTO "art" VALUES(11,1,'tvshow','banner','http://thetvdb.com/banners/gr aphical/80379-g13.jpg');
195 INSERT INTO "art" VALUES(12,1,'tvshow','fanart','http://thetvdb.com/banners/fa nart/original/80379-2.jpg');
196 INSERT INTO "art" VALUES(13,1,'tvshow','poster','http://thetvdb.com/banners/po sters/80379-22.jpg');
197 INSERT INTO "art" VALUES(14,1,'season','poster','http://thetvdb.com/banners/po sters/80379-22.jpg');
198 INSERT INTO "art" VALUES(15,2,'season','banner','http://thetvdb.com/banners/se asonswide/80379-0.jpg');
199 INSERT INTO "art" VALUES(16,2,'season','poster','http://thetvdb.com/banners/se asons/80379-0-4.jpg');
200 INSERT INTO "art" VALUES(17,3,'season','banner','http://thetvdb.com/banners/se asonswide/80379-1-2.jpg');
201 INSERT INTO "art" VALUES(18,3,'season','poster','http://thetvdb.com/banners/se asons/80379-1-12.jpg');
202 INSERT INTO "art" VALUES(19,4,'season','banner','http://thetvdb.com/banners/se asonswide/80379-2-2.jpg');
203 INSERT INTO "art" VALUES(20,4,'season','poster','http://thetvdb.com/banners/se asons/80379-2-11.jpg');
204 INSERT INTO "art" VALUES(21,5,'season','banner','http://thetvdb.com/banners/se asonswide/80379-3-2.jpg');
205 INSERT INTO "art" VALUES(22,5,'season','poster','http://thetvdb.com/banners/se asons/80379-3-9.jpg');
206 INSERT INTO "art" VALUES(23,6,'season','banner','http://thetvdb.com/banners/se asonswide/80379-4-2.jpg');
207 INSERT INTO "art" VALUES(24,6,'season','poster','http://thetvdb.com/banners/se asons/80379-4-8.jpg');
208 INSERT INTO "art" VALUES(25,7,'season','banner','http://thetvdb.com/banners/se asonswide/80379-5.jpg');
209 INSERT INTO "art" VALUES(26,7,'season','poster','http://thetvdb.com/banners/se asons/80379-5-9.jpg');
210 INSERT INTO "art" VALUES(27,8,'season','poster','http://thetvdb.com/banners/se asons/80379-6-8.jpg');
211 INSERT INTO "art" VALUES(28,9,'season','poster','http://thetvdb.com/banners/se asons/80379-7-3.jpg');
212 INSERT INTO "art" VALUES(29,1,'episode','thumb','http://thetvdb.com/banners/ep isodes/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 be auty named Penny moves in next door. Sheldon, Leonard's roommate, is quite conte nt 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
OLDNEW
« no previous file with comments | « third_party/sqlite/sqlite-src-3170000/test/trans3.test ('k') | third_party/sqlite/sqlite-src-3170000/test/trigger1.test » ('j') | no next file with comments »

Powered by Google App Engine
This is Rietveld 408576698