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