Debian Codegraph @

test/transitive1.test

raw
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 set testprefix transitive1
18
19 do_execsql_test transitive1-100 {
20 CREATE TABLE t1(a TEXT, b TEXT, c TEXT COLLATE NOCASE);
21 INSERT INTO t1 VALUES('abc','abc','Abc');
22 INSERT INTO t1 VALUES('def','def','def');
23 INSERT INTO t1 VALUES('ghi','ghi','GHI');
24 CREATE INDEX t1a1 ON t1(a);
25 CREATE INDEX t1a2 ON t1(a COLLATE nocase);
26
27 SELECT * FROM t1 WHERE a=b AND c=b AND c='DEF';
28 } {def def def}
29 do_execsql_test transitive1-110 {
30 SELECT * FROM t1 WHERE a=b AND c=b AND c>='DEF' ORDER BY +a;
31 } {def def def ghi ghi GHI}
32 do_execsql_test transitive1-120 {
33 SELECT * FROM t1 WHERE a=b AND c=b AND c<='DEF' ORDER BY +a;
34 } {abc abc Abc def def def}
35
36 do_execsql_test transitive1-200 {
37 CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
38 INSERT INTO t2 VALUES(100,100,100);
39 INSERT INTO t2 VALUES(20,20,20);
40 INSERT INTO t2 VALUES(3,3,3);
41
42 SELECT * FROM t2 WHERE a=b AND c=b AND c=20;
43 } {20 20 20}
44 do_execsql_test transitive1-210 {
45 SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
46 } {3 3 3 20 20 20}
47 do_execsql_test transitive1-220 {
48 SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
49 } {20 20 20 100 100 100}
50
51 # Test cases for ticket [[d805526eae253103] 2013-07-08
52 # "Incorrect join result or assertion fault due to transitive constraints"
53 #
54 do_execsql_test transitive1-300 {
55 CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
56 CREATE TABLE t302(y INTEGER UNIQUE, z);
57 INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
58 INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
59 SELECT *
60 FROM t301 CROSS JOIN t302
61 WHERE w=y AND y IS NOT NULL
62 ORDER BY +w;
63 } {1 2 1 3 3 4 3 6 5 6 5 7}
64 do_execsql_test transitive1-301 {
65 SELECT *
66 FROM t301 CROSS JOIN t302
67 WHERE w=y AND y IS NOT NULL
68 ORDER BY w;
69 } {1 2 1 3 3 4 3 6 5 6 5 7}
70 do_execsql_test transitive1-302 {
71 SELECT *
72 FROM t301 CROSS JOIN t302
73 WHERE w IS y AND y IS NOT NULL
74 ORDER BY w;
75 } {1 2 1 3 3 4 3 6 5 6 5 7}
76 do_execsql_test transitive1-310 {
77 SELECT *
78 FROM t301 CROSS JOIN t302 ON w=y
79 WHERE y>1
80 ORDER BY +w
81 } {3 4 3 6 5 6 5 7}
82 do_execsql_test transitive1-311 {
83 SELECT *
84 FROM t301 CROSS JOIN t302 ON w=y
85 WHERE y>1
86 ORDER BY w
87 } {3 4 3 6 5 6 5 7}
88 do_execsql_test transitive1-312 {
89 SELECT *
90 FROM t301 CROSS JOIN t302 ON w=y
91 WHERE y>1
92 ORDER BY w DESC
93 } {5 6 5 7 3 4 3 6}
94 do_execsql_test transitive1-320 {
95 SELECT *
96 FROM t301 CROSS JOIN t302 ON w=y
97 WHERE y BETWEEN 2 AND 4;
98 } {3 4 3 6}
99 do_execsql_test transitive1-331 {
100 SELECT *
101 FROM t301 CROSS JOIN t302 ON w=y
102 WHERE y BETWEEN 1 AND 4
103 ORDER BY w;
104 } {1 2 1 3 3 4 3 6}
105 do_execsql_test transitive1-332 {
106 SELECT *
107 FROM t301 CROSS JOIN t302 ON w=y
108 WHERE y BETWEEN 1 AND 4
109 ORDER BY w DESC;
110 } {3 4 3 6 1 2 1 3}
111
112 # Ticket [c620261b5b5dc] circa 2013-10-28.
113 # Make sure constraints are not used with LEFT JOINs.
114 #
115 # The next case is from the ticket report. It outputs no rows in 3.8.1
116 # prior to the bug-fix.
117 #
118 do_execsql_test transitive1-400 {
119 CREATE TABLE t401(a);
120 CREATE TABLE t402(b);
121 CREATE TABLE t403(c INTEGER PRIMARY KEY);
122 INSERT INTO t401 VALUES(1);
123 INSERT INTO t403 VALUES(1);
124 SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
125 } {1-row}
126 do_execsql_test transitive1-401 {
127 SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
128 } {1-row}
129 do_execsql_test transitive1-402 {
130 SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
131 } {1-row}
132 do_execsql_test transitive1-403 {
133 SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
134 } {1-row}
135
136
137 # The following is a script distilled from the XBMC project where the
138 # bug was originally encountered. The correct answer is a single row
139 # of output. Before the bug was fixed, zero rows were generated.
140 #
141 do_execsql_test transitive1-410 {
142 CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
143 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);
144 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);
145 INSERT INTO "path" VALUES(2,'/tmp/tvshows/The.Big.Bang.Theory/','','','85E1DAAB2F5FF6EAE8AEDF1B5C882D1E',NULL,NULL,NULL,NULL,NULL,'2013-10-23 18:58:43');
146 CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename text, playCount integer, lastPlayed text, dateAdded text);
147 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');
148 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);
149 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://
150 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
151 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"
152 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="
153 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</
154 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-
155 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/
156 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://
157 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"
158 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</
159 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</
160 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
161 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="
162 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</
163 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-
164 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|"
165 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/
166 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,
167 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
168 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=""
169 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);
170 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);
171 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);
172 CREATE TABLE tvshowlinkpath (idShow integer, idPath integer);
173 INSERT INTO "tvshowlinkpath" VALUES(1,2);
174 CREATE TABLE seasons ( idSeason integer primary key, idShow integer, season integer);
175 INSERT INTO "seasons" VALUES(1,1,-1);
176 INSERT INTO "seasons" VALUES(2,1,0);
177 INSERT INTO "seasons" VALUES(3,1,1);
178 INSERT INTO "seasons" VALUES(4,1,2);
179 INSERT INTO "seasons" VALUES(5,1,3);
180 INSERT INTO "seasons" VALUES(6,1,4);
181 INSERT INTO "seasons" VALUES(7,1,5);
182 INSERT INTO "seasons" VALUES(8,1,6);
183 INSERT INTO "seasons" VALUES(9,1,7);
184 CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT);
185 INSERT INTO "art" VALUES(1,1,'actor','thumb','http://thetvdb.com/banners/actors/73597.jpg');
186 INSERT INTO "art" VALUES(2,2,'actor','thumb','http://thetvdb.com/banners/actors/73596.jpg');
187 INSERT INTO "art" VALUES(3,3,'actor','thumb','http://thetvdb.com/banners/actors/73595.jpg');
188 INSERT INTO "art" VALUES(4,4,'actor','thumb','http://thetvdb.com/banners/actors/73599.jpg');
189 INSERT INTO "art" VALUES(5,5,'actor','thumb','http://thetvdb.com/banners/actors/73598.jpg');
190 INSERT INTO "art" VALUES(6,6,'actor','thumb','http://thetvdb.com/banners/actors/283158.jpg');
191 INSERT INTO "art" VALUES(7,7,'actor','thumb','http://thetvdb.com/banners/actors/283157.jpg');
192 INSERT INTO "art" VALUES(8,8,'actor','thumb','http://thetvdb.com/banners/actors/91271.jpg');
193 INSERT INTO "art" VALUES(9,9,'actor','thumb','http://thetvdb.com/banners/actors/294178.jpg');
194 INSERT INTO "art" VALUES(10,10,'actor','thumb','http://thetvdb.com/banners/actors/283159.jpg');
195 INSERT INTO "art" VALUES(11,1,'tvshow','banner','http://thetvdb.com/banners/graphical/80379-g13.jpg');
196 INSERT INTO "art" VALUES(12,1,'tvshow','fanart','http://thetvdb.com/banners/fanart/original/80379-2.jpg');
197 INSERT INTO "art" VALUES(13,1,'tvshow','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
198 INSERT INTO "art" VALUES(14,1,'season','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
199 INSERT INTO "art" VALUES(15,2,'season','banner','http://thetvdb.com/banners/seasonswide/80379-0.jpg');
200 INSERT INTO "art" VALUES(16,2,'season','poster','http://thetvdb.com/banners/seasons/80379-0-4.jpg');
201 INSERT INTO "art" VALUES(17,3,'season','banner','http://thetvdb.com/banners/seasonswide/80379-1-2.jpg');
202 INSERT INTO "art" VALUES(18,3,'season','poster','http://thetvdb.com/banners/seasons/80379-1-12.jpg');
203 INSERT INTO "art" VALUES(19,4,'season','banner','http://thetvdb.com/banners/seasonswide/80379-2-2.jpg');
204 INSERT INTO "art" VALUES(20,4,'season','poster','http://thetvdb.com/banners/seasons/80379-2-11.jpg');
205 INSERT INTO "art" VALUES(21,5,'season','banner','http://thetvdb.com/banners/seasonswide/80379-3-2.jpg');
206 INSERT INTO "art" VALUES(22,5,'season','poster','http://thetvdb.com/banners/seasons/80379-3-9.jpg');
207 INSERT INTO "art" VALUES(23,6,'season','banner','http://thetvdb.com/banners/seasonswide/80379-4-2.jpg');
208 INSERT INTO "art" VALUES(24,6,'season','poster','http://thetvdb.com/banners/seasons/80379-4-8.jpg');
209 INSERT INTO "art" VALUES(25,7,'season','banner','http://thetvdb.com/banners/seasonswide/80379-5.jpg');
210 INSERT INTO "art" VALUES(26,7,'season','poster','http://thetvdb.com/banners/seasons/80379-5-9.jpg');
211 INSERT INTO "art" VALUES(27,8,'season','poster','http://thetvdb.com/banners/seasons/80379-6-8.jpg');
212 INSERT INTO "art" VALUES(28,9,'season','poster','http://thetvdb.com/banners/seasons/80379-7-3.jpg');
213 INSERT INTO "art" VALUES(29,1,'episode','thumb','http://thetvdb.com/banners/episodes/80379/332484.jpg');
214 CREATE INDEX ix_bookmark ON bookmark (idFile, type);
215 CREATE INDEX ix_path ON path ( strPath );
216 CREATE INDEX ix_files ON files ( idPath, strFilename );
217 CREATE UNIQUE INDEX ix_episode_file_1 on episode (idEpisode, idFile);
218 CREATE UNIQUE INDEX id_episode_file_2 on episode (idFile, idEpisode);
219 CREATE INDEX ix_episode_season_episode on episode (c12, c13);
220 CREATE INDEX ix_episode_bookmark on episode (c17);
221 CREATE INDEX ix_episode_show1 on episode(idEpisode,idShow);
222 CREATE INDEX ix_episode_show2 on episode(idShow,idEpisode);
223 CREATE UNIQUE INDEX ix_tvshowlinkpath_1 ON tvshowlinkpath ( idShow, idPath );
224 CREATE UNIQUE INDEX ix_tvshowlinkpath_2 ON tvshowlinkpath ( idPath, idShow );
225 CREATE INDEX ixEpisodeBasePath ON episode ( c19 );
226 CREATE INDEX ixTVShowBasePath on tvshow ( c17 );
227 CREATE INDEX ix_seasons ON seasons (idShow, season);
228 CREATE INDEX ix_art ON art(media_id, media_type, type);
229 CREATE VIEW episodeview
230 AS
231 SELECT episode.*,
232 files.strfilename AS strFileName,
233 path.strpath AS strPath,
234 files.playcount AS playCount,
235 files.lastplayed AS lastPlayed,
236 files.dateadded AS dateAdded,
237 tvshow.c00 AS strTitle,
238 tvshow.c14 AS strStudio,
239 tvshow.c05 AS premiered,
240 tvshow.c13 AS mpaa,
241 tvshow.c16 AS strShowPath,
242 bookmark.timeinseconds AS resumeTimeInSeconds,
243 bookmark.totaltimeinseconds AS totalTimeInSeconds,
244 seasons.idseason AS idSeason
245 FROM episode
246 JOIN files
247 ON files.idfile = episode.idfile
248 JOIN tvshow
249 ON tvshow.idshow = episode.idshow
250 LEFT JOIN seasons
251 ON seasons.idshow = episode.idshow
252 AND seasons.season = episode.c12
253 JOIN path
254 ON files.idpath = path.idpath
255 LEFT JOIN bookmark
256 ON bookmark.idfile = episode.idfile
257 AND bookmark.type = 1;
258 CREATE VIEW tvshowview
259 AS
260 SELECT tvshow.*,
261 path.strpath AS strPath,
262 path.dateadded AS dateAdded,
263 Max(files.lastplayed) AS lastPlayed,
264 NULLIF(Count(episode.c12), 0) AS totalCount,
265 Count(files.playcount) AS watchedcount,
266 NULLIF(Count(DISTINCT( episode.c12 )), 0) AS totalSeasons
267 FROM tvshow
268 LEFT JOIN tvshowlinkpath
269 ON tvshowlinkpath.idshow = tvshow.idshow
270 LEFT JOIN path
271 ON path.idpath = tvshowlinkpath.idpath
272 LEFT JOIN episode
273 ON episode.idshow = tvshow.idshow
274 LEFT JOIN files
275 ON files.idfile = episode.idfile
276 GROUP BY tvshow.idshow;
277 SELECT
278 episodeview.c12,
279 path.strPath,
280 tvshowview.c00,
281 tvshowview.c01,
282 tvshowview.c05,
283 tvshowview.c08,
284 tvshowview.c14,
285 tvshowview.c13,
286 seasons.idSeason,
287 count(1),
288 count(files.playCount)
289 FROM episodeview
290 JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
291 JOIN seasons ON (seasons.idShow = tvshowview.idShow
292 AND seasons.season = episodeview.c12)
293 JOIN files ON files.idFile = episodeview.idFile
294 JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
295 JOIN path ON path.idPath = tvshowlinkpath.idPath
296 WHERE tvshowview.idShow = 1
297 GROUP BY episodeview.c12;
298 } {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}
299
300 ##############################################################################
301 # 2015-05-18. Make sure transitive constraints are avoided when column
302 # affinities and collating sequences get in the way.
303 #
304 db close
305 forcedelete test.db
306 sqlite3 db test.db
307 do_execsql_test transitive1-500 {
308 CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
309 INSERT INTO x VALUES(10, '10');
310 SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
311 } {10 10}
312 do_execsql_test transitive1-510 {
313 CREATE TABLE t1(x TEXT);
314 CREATE TABLE t2(y TEXT);
315 INSERT INTO t1 VALUES('abc');
316 INSERT INTO t2 VALUES('ABC');
317 SELECT * FROM t1 CROSS JOIN t2 WHERE (x=y COLLATE nocase) AND y='ABC';
318 } {abc ABC}
319 do_execsql_test transitive1-520 {
320 CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
321 INSERT INTO t3 VALUES(10, '10');
322 SELECT * FROM t3 WHERE i=t AND t = '10 ';
323 } {}
324 do_execsql_test transitive1-530 {
325 CREATE TABLE u1(x TEXT, y INTEGER, z TEXT);
326 CREATE INDEX i1 ON u1(x);
327 INSERT INTO u1 VALUES('00013', 13, '013');
328 SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
329 } {00013 13 013}
330 do_execsql_test transitive1-540 {
331 CREATE TABLE b1(x, y);
332 INSERT INTO b1 VALUES('abc', 'ABC');
333 CREATE INDEX b1x ON b1(x);
334 SELECT * FROM b1 WHERE (x=y COLLATE nocase) AND y='ABC';
335 } {abc ABC}
336 do_execsql_test transitive1-550 {
337 CREATE TABLE c1(x, y COLLATE nocase, z);
338 INSERT INTO c1 VALUES('ABC', 'ABC', 'abc');
339 SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
340 } {ABC ABC abc}
341 do_execsql_test transitive1-560 {
342 CREATE INDEX c1x ON c1(x);
343 SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
344 } {ABC ABC abc}
345 do_execsql_test transitive1-560eqp {
346 EXPLAIN QUERY PLAN
347 SELECT * FROM c1 WHERE x=y AND y=z AND z='abc';
348 } {/SCAN c1/}
349 do_execsql_test transitive1-570 {
350 SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
351 } {}
352 do_execsql_test transitive1-570eqp {
353 EXPLAIN QUERY PLAN
354 SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
355 } {/SCAN c1/}
356
357 # 2021-05-04 forum https://sqlite.org/forum/forumpost/eb8613976a
358 reset_db
359 do_execsql_test transitive1-600 {
360 CREATE TABLE t0(a0 INT, b1 INT);
361 CREATE INDEX t0b1 ON t0(b1);
362 CREATE TABLE t1(w,x,y,z3 INT);
363 INSERT INTO t0(a0, b1) VALUES (0,1);
364 INSERT INTO t1(w,x,y,z3) VALUES (7,8,9,1);
365 } {}
366 do_execsql_test transitive1-610 {
367 SELECT ALL * FROM t0,t1 WHERE b1=z3 AND a0=z3;
368 } {}
369 do_execsql_test transitive1-620 {
370 SELECT ALL * FROM t0,t1 WHERE likely(b1=z3) AND a0=z3;
371 } {}
372 do_execsql_test transitive1-630 {
373 DROP TABLE t0;
374 DROP TABLE t1;
375 CREATE TABLE t0(c0 INT, c1 INT UNIQUE);
376 CREATE TABLE t1(c0 INT);
377 INSERT INTO t0(c0, c1) VALUES (0, 1);
378 INSERT INTO t1(c0) VALUES (1);
379 SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
380 SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
381 SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);
382 } {}
383
384 #-------------------------------------------------------------------------
385 # 2021-08-31 forum https://sqlite.org/forum/forumpost/8d1b58f112
386 reset_db
387 do_execsql_test transitive1-700 {
388 CREATE TABLE t1(a INT PRIMARY KEY);
389 INSERT INTO t1(a) VALUES(1),(2),(3);
390 CREATE TABLE t2(x INTEGER PRIMARY KEY,y INT);
391 INSERT INTO t2(y) VALUES(2),(3);
392 }
393
394 do_execsql_test transitive1-710 {
395 SELECT * FROM t1 CROSS JOIN t2 WHERE t2.y=t1.a AND t1.a=t2.x
396 } {}
397
398 do_execsql_test transitive1-720 {
399 SELECT * FROM t1 CROSS JOIN t2 WHERE likely(t2.y=t1.a) AND unlikely(t1.a=t2.x)
400 } {}
401
402 # 2021-10-04 forum https://sqlite.org/forum/forumpost/a65cacbf5e1c41ba
403 #
404 reset_db
405 do_execsql_test transitive1-800 {
406 CREATE TABLE t1(a INT);
407 INSERT INTO t1 VALUES(0),(3);
408 CREATE TABLE t2(b INT UNIQUE, c INT);
409 INSERT INTO t2 VALUES(1,4) ,(0,5);
410 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a AND b IS a);
411 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);
412 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND b IS a);
413 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a AND a IS b);
414 } {}
415 do_execsql_test transitive1-810 {
416 CREATE TABLE t3(a INTEGER PRIMARY KEY,b);
417 INSERT INTO t3(a,b) VALUES(1,2),(5,5),(7,11);
418 SELECT * FROM t3 WHERE a=b AND a='5';
419 } {5 5}
420 do_execsql_test transitive1-811 {
421 SELECT * FROM t3 WHERE a=b AND a='4';
422 } {}
423 do_execsql_test transitive1-812 {
424 SELECT * FROM t3 WHERE a=b AND a='7';
425 } {}
426 do_execsql_test transitive1-813 {
427 SELECT * FROM t3 WHERE a=b AND a='5x';
428 } {}
429
430 # Bug 2026-05-21T03:39:28Z
431 #
432 reset_db
433 do_execsql_test transitive1-900 {
434 CREATE TABLE t1(a TEXT);
435 CREATE TABLE t2(b TEXT COLLATE NOCASE);
436 INSERT INTO t1 VALUES('abc');
437 INSERT INTO t2 VALUES('abc');
438 SELECT * FROM t1 CROSS JOIN t2 WHERE a=b AND b='ABC';
439 } {abc abc}
440
441 #-------------------------------------------------------------------------
442 reset_db
443 db cache size 0
444
445 do_execsql_test 1000 {
446 PRAGMA automatic_index = 0;
447
448 CREATE TABLE t1(a TEXT);
449 CREATE TABLE t2(b TEXT);
450
451 INSERT INTO t1 VALUES ('hello');
452 INSERT INTO t1 VALUES ('Hello');
453 INSERT INTO t1 VALUES ('HELLO');
454 INSERT INTO t1 VALUES ('HeLLo');
455
456 INSERT INTO t2 VALUES ('hello');
457 INSERT INTO t2 VALUES ('Hello');
458 INSERT INTO t2 VALUES ('HELLO');
459 INSERT INTO t2 VALUES ('HeLLo');
460 }
461
462 optimization_control db transitive 1
463
464 do_execsql_test 1010 {
465 SELECT t1.a, t2.b
466 FROM t1 CROSS JOIN t2
467 WHERE t1.a = t2.b
468 AND t2.b COLLATE NOCASE = 'Hello';
469 } {
470 hello hello
471 Hello Hello
472 HELLO HELLO
473 HeLLo HeLLo
474 }
475
476 optimization_control db transitive 0
477
478 do_execsql_test 1020 {
479 SELECT t1.a, t2.b
480 FROM t1 CROSS JOIN t2
481 WHERE t1.a = t2.b
482 AND t2.b COLLATE NOCASE = 'Hello';
483 } {
484 hello hello
485 Hello Hello
486 HELLO HELLO
487 HeLLo HeLLo
488 }
489
490 #-------------------------------------------------------------------------
491 reset_db
492
493 do_execsql_test 1100 {
494 PRAGMA automatic_index = 0;
495 CREATE TABLE t1(x);
496 CREATE TABLE t2(y);
497
498 INSERT INTO t1 VALUES('ABC');
499 INSERT INTO t2 VALUES('abc');
500 }
501
502 do_execsql_test 1110 {
503 SELECT * FROM t1 CROSS JOIN t2
504 WHERE (t1.x COLLATE nocase) = (t2.y COLLATE nocase)
505 AND (t2.y = 'ABC')
506 } {}
507
508 #-------------------------------------------------------------------------
509 reset_db
510
511 do_execsql_test 1200 {
512 PRAGMA automatic_index = 0;
513 CREATE TABLE t1(x);
514 CREATE TABLE t2(y);
515
516 INSERT INTO t1 VALUES('ABC');
517 INSERT INTO t2 VALUES('ABC');
518 }
519
520 do_execsql_test 1210 {
521 SELECT * FROM t1 CROSS JOIN t2
522 WHERE t1.x = t2.y
523 AND (t1.x COLLATE nocase = 'abc')
524 } {ABC ABC}
525
526 finish_test