Gitiles
Code Review Sign In
gerrit.openfyde.cn / chromium.googlesource.com / chromium / deps / sqlite / refs/heads/upstream/unix-timed-wait-exp / . / test / transitive1.test
blob: 87553384921fcb43b15ba9ba4b915ffaabdbb169 [file] [log] [blame]
drh63db0392013-01-17 16:18:55 +0000[diff] [blame]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
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18do_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}
28do_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}
31do_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
35do_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}
43do_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}
46do_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
drhb5246e52013-07-08 21:12:57 +0000[diff] [blame]50# Test cases for ticket [[d805526eae253103] 2013-07-08
51# "Incorrect join result or assertion fault due to transitive constraints"
52#
53do_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}
63do_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}
drh5d8806e2015-05-14 14:03:21 +0000[diff] [blame]69do_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}
drhb5246e52013-07-08 21:12:57 +0000[diff] [blame]75do_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}
81do_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}
87do_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}
93do_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}
98do_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}
104do_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
drh70a02222013-10-28 20:38:52 +0000[diff] [blame]111# Ticket [c620261b5b5dc] circa 2013-10-28.
drh5d8806e2015-05-14 14:03:21 +0000[diff] [blame]112# Make sure constraints are not used with LEFT JOINs.
drh70a02222013-10-28 20:38:52 +0000[diff] [blame]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#
117do_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}
drh5d8806e2015-05-14 14:03:21 +0000[diff] [blame]125do_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}
128do_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}
131do_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
drh70a02222013-10-28 20:38:52 +0000[diff] [blame]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#
140do_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
drh69c15fe2015-05-18 11:34:52 +0000[diff] [blame]299##############################################################################
300# 2015-05-18. Make sure transitive constraints are avoided when column
301# affinities and collating sequences get in the way.
302#
303db close
304forcedelete test.db
305sqlite3 db test.db
306do_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}
311do_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}
318do_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} {}
323do_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}
329do_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}
335do_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}
340do_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}
344do_execsql_test transitive1-560eqp {
345 EXPLAIN QUERY PLAN
346 SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
drh82102332021-03-20 15:11:29 +0000[diff] [blame]347} {/SCAN c1/}
drh69c15fe2015-05-18 11:34:52 +0000[diff] [blame]348do_execsql_test transitive1-570 {
349 SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
350} {}
351do_execsql_test transitive1-570eqp {
352 EXPLAIN QUERY PLAN
353 SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
drh82102332021-03-20 15:11:29 +0000[diff] [blame]354} {/SEARCH c1 USING INDEX c1x/}
drh70a02222013-10-28 20:38:52 +0000[diff] [blame]355
drh67656ac2021-05-04 23:21:35 +0000[diff] [blame]356# 2021-05-04 forum https://sqlite.org/forum/forumpost/eb8613976a
357reset_db
358do_execsql_test transitive1-600 {
359 CREATE TABLE t0(a0 INT, b1 INT);
360 CREATE INDEX t0b1 ON t0(b1);
361 CREATE TABLE t1(w,x,y,z3 INT);
362 INSERT INTO t0(a0, b1) VALUES (0,1);
363 INSERT INTO t1(w,x,y,z3) VALUES (7,8,9,1);
364} {}
365do_execsql_test transitive1-610 {
366 SELECT ALL * FROM t0,t1 WHERE b1=z3 AND a0=z3;
367} {}
368do_execsql_test transitive1-620 {
369 SELECT ALL * FROM t0,t1 WHERE likely(b1=z3) AND a0=z3;
370} {}
371do_execsql_test transitive1-630 {
372 DROP TABLE t0;
373 DROP TABLE t1;
374 CREATE TABLE t0(c0 INT, c1 INT UNIQUE);
375 CREATE TABLE t1(c0 INT);
376 INSERT INTO t0(c0, c1) VALUES (0, 1);
377 INSERT INTO t1(c0) VALUES (1);
378 SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
379 SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
380 SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);
381} {}
382
drh63db0392013-01-17 16:18:55 +0000[diff] [blame]383finish_test