inbetween.out

derby database source code.good for you.

-- it's not key.  Now the key is it should be filtered out, otherwise we get exception.
select c1 from t2 where c1 in (select c1 from t1 where c2 in (5, t2.c2))
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Hash Exists Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 4
	Rows seen from the right = 0
	Rows filtered = 0
	Rows returned = 0
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Left result set:
		Table Scan ResultSet for T2 at serializable isolation level using share table locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 4
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			next time in milliseconds/row = 0
		scan information: 
			Bit set of columns fetched=All
			Number of columns fetched=2
			Number of pages visited=1
			Number of rows qualified=4
			Number of rows visited=4
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Project-Restrict ResultSet (4):
		Number of opens = 4
		Rows seen = 2
		Rows filtered = 2
		restriction = true
		projection = false
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Hash Scan ResultSet for T1 using index I12 at serializable isolation level using share table locking: 
			Number of opens = 4
			Hash table size = 5
			Hash key is column number 0
			Rows seen = 2
			Rows filtered = 0
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				next time in milliseconds/row = 0
			scan information: 
				Bit set of columns fetched={0, 1}
				Number of columns fetched=2
				Number of deleted rows visited=0
				Number of pages visited=1
				Number of rows qualified=5
				Number of rows visited=5
				Scan type=btree
				Tree height=1
				start position: 
	None
				stop position: 
	None
				scan qualifiers:
None
				next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- just some more tests in different situations, not for the bug 4316
-- many items
select c1 from t1 where c1 in ('9', '4', '8.0', '7.7',
	5.2, 6, '7.7', '4.9', '6.1');
ERROR 42818: Comparisons between 'REAL' and 'CHAR' are not supported.
ij> prepare q1 as 'select c1 from t1 where c1 in (3, ?)';
ij> execute q1 using 'values 8';
C1           
-------------
3.0          
8.0          
ij> execute q1 using 'values 9';
C1           
-------------
3.0          
ij> remove q1;
ij> -- reset autocommit
autocommit on;
ij> -- Clean up
drop table ss;
0 rows inserted/updated/deleted
ij> drop table t;
0 rows inserted/updated/deleted
ij> drop table u;
0 rows inserted/updated/deleted
ij> drop table big;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table test;
0 rows inserted/updated/deleted
ij> --reproduction for beetle 5135 ( long list of constants in IN clause)
create table t1(id int);
0 rows inserted/updated/deleted
ij> insert into t1 values(2);
1 row inserted/updated/deleted
ij> insert into t1 values(5644);
1 row inserted/updated/deleted
ij> insert into t1 values(723);
1 row inserted/updated/deleted
ij> insert into t1 values(0);
1 row inserted/updated/deleted
ij> insert into t1 values(1827);
1 row inserted/updated/deleted
ij> insert into t1 values(4107);
1 row inserted/updated/deleted
ij> insert into t1 values(5095);
1 row inserted/updated/deleted
ij> insert into t1 values(6666);
1 row inserted/updated/deleted
ij> insert into t1 values(7777);
1 row inserted/updated/deleted
ij> insert into t1 values(15157);
1 row inserted/updated/deleted
ij> insert into t1 values(13037);
1 row inserted/updated/deleted
ij> insert into t1 values(9999);
1 row inserted/updated/deleted
ij> SELECT id FROM t1 WHERE id IN (2,3,5,7,6,8,11,13,14,15,16,18,19
,22,25,30,32,33,5712,34,39,42,43,46,51,54
,55,56,58,60,62,63,64,65,68,70,72,73,5663
,5743,74,5396,78,81,83,87,5267,89,91,92,93,94
,95,96,97,99,101,102,103,104,107,108,109,110,114
,115,116,118,121,122,124,126,128,129,130,131,132,134
,136,135,139,140,141,145,150,155,156,158,159,162,160
,164,165,166,168,169,170,171,172,173,174,175,176,178
,180,182,183,185,187,188,190,191,193,197,198,200,202
,203,208,5672,5221,5713,212,213,215,219,220,221,225,227
,229,5763,234,235,236,238,241,239,243,245,249,250,5716
,255,256,257,258,259,260,261,262,263,264,265,269,5644
,272,274,275,276,277,280,282,284,286,289,290,294,296
,293,299,301,303,305,5234,306,310,311,5473,313,314,315
,316,318,319,322,323,324,326,327,328,330,333,334,336
,337,338,340,341,342,343,344,345,346,347,348,350,351
,353,354,361,363,368,369,370,374,372,373,375,376,379
,380,384,388,389,390,392,394,396,397,398,400,403,404
,5775,406,407,408,409,410,412,413,414,416,420,422,423
,424,428,429,431,434,436,438,441,442,443,450,452,454
,456,457,458,462,467,466,468,469,5651,470,474,477,479
,481,482,483,484,488,486,493,494,495,496,498,500,501
,502,503,504,506,507,508,509,510,512,513,514,516,519
,520,522,523,524,527,528,530,532,534,535,538,539,542
,543,546,548,550,552,555,562,561,563,565,567,568,569
,571,574,572,5250,576,573,579,581,583,584,586,589,590
,5642,592,596,600,601,602,604,606,607,609,610,611,615
,616,617,618,619,620,621,623,624,625,626,627,629,630
,631,632,633,635,636,637,640,641,642,643,644,5246,647
,646,648,653,5324,654,655,656,658,660,662,663,665,668
,669,670,672,673,674,675,676,677,678,680,681,683,684
,686,689,691,2762,694,695,5464,696,697,698,700,701,705
,5635,5471,708,711,713,714,715,717,719,720,721,722,723
,724,726,728,729,730,731,733,735,740,741,746,747,748
,749,750,751,752,754,755,756,757,759,761,762,763,764
,766,768,769,772,774,776,775,779,780,781,783,788,790
,794,795,797,801,800,802,804,806,811,813,814,816,819
,822,823,824,825,826,827,829,5755,832,833,834,836,838
,840,841,843,844,846,847,848,850,851,852,855,856,857
,858,859,860,864,5602,865,869,871,872,873,874,876,878
,880,882,883,885,886,888,890,892,896,898,5528,900,901
,902,903,904,905,906,907,908,5334,911,912,913,914,915
,916,918,919,920,921,922,924,923,926,927,928,930,933
,934,937,938,939,941,942,943,947,945,948,949,951,955
,957,958,959,960,961,967,968,971,974,980,981,986,987
,988,991,989,993,995,996,997,999,1000,1001,1002,1003,1005
,1006,1007,1008,1009,1010,1012,1011,1014,1015,1016,1017,1019,1021
,1025,1026,1028,1029,1030,1031,1034,1036,1037,1039,1041,1042,1043
,1049,1047,1050,1051,1052,1053,1054,1056,1057,1058,1061,1062,1063
,1066,1071,1070,1073,1075,1077,1078,5710,1084,1085,1086,1088,1090
,1091,1093,1094,1095,1096,1099,1102,1104,1105,1107,1108,1109,1110
,1114,1117,1119,1121,1123,1124,1126,1127,1128,1129,1130,1131,1136
,1138,1141,1143,1144,1145,1147,1150,1151,1157,1146,1158,1164,1166
,1171,1170,1176,1177,1189,5525,1202,1203,1173,1175,1179,1181,1183
,1184,1186,1188,1193,1195,1196,1197,1198,1199,1200,1205,1207,1225
,1226,1227,1228,1209,1210,1214,1212,1215,1217,1218,1219,1220,5238
,1221,1223,5288,1230,5727,1232,1234,1235,1236,5795,5816,1238,1240
,1241,1245,1246,1247,1250,1253,1254,1258,1261,1262,1264,1265,1266
,1268,1270,1274,1275,1277,1278,1280,1281,1282,1283,1284,1286,1285
,1287,1288,1290,1293,1294,1295,1297,1301,1302,1305,1307,1308,1309
,1311,1313,1314,1316,1317,1318,1320,1321,1323,1327,1329,1332,1334
,1336,1338,1339,1341,1343,1348,1346,1347,1349,1350,1353,1357,1358
,1359,1361,1363,1366,1367,1368,1369,1370,1371,1374,5689,1376,1377
,1379,1380,1381,1386,1387,5661,1389,1390,1392,1393,1394,1395,1396
,1398,1400,1402,1408,1409,1410,1411,1412,1413,1414,1415,1416,1419
,1421,1425,1427,1428,5216,1430,1431,1432,1433,1434,1437,1438,1440
,1444,1446,1448,1449,1451,1453,1454,1456,1457,1458,1459,1461,1463
,1464,1465,1466,1467,1468,1472,1474,1475,1477,1476,1479,1480,1482
,1484,1485,1489,1490,1491,1492,1494,1495,1498,1496,1502,1503,1504
,1506,1507,1508,1510,1511,1512,1517,1519,5686,1521,1525,1528,1531
,1530,1529,1535,1537,1538,1539,1541,1542,1546,1549,1552,1554,1555
,1557,1558,1561,1562,1563,1566,1568,1570,1574,1575,1576,1580,1579
,1577,1581,1583,1584,1585,1586,1589,1588,1592,1590,1594,1597,1598
,1600,1601,1605,1606,1607,1608,1610,1611,1612,1613,1614,1615,1618
,1620,1624,1625,1626,1627,1628,1631,1633,1635,1639,1640,1641,1642
,5653,1645,1647,1649,1650,1655,5633,1656,1657,5647,1661,1662,1666
,1667,1668,1669,1671,1672,1673,1674,1675,1676,1677,1678,1680,1682
,1686,1688,1690,1694,1695,1696,1697,1699,1700,1701,1702,1703,1708
,1710,1714,1713,1716,1719,1722,1721,1723,1724,1726,1727,1728,1729
,1732,1734,1735,5419,1736,1737,1739,1740,1743,1744,1747,1748,1749
,1750,1751,1752,1754,1757,1758,1767,1759,1761,1762,1764,1765,1766
,1768,1771,1774,1775,1776,1779,1777,1781,1783,1785,1787,1789,1791
,1794,1795,1796,1797,1798,1802,1804,1805,1806,1808,1809,1811,1812
,1813,1814,1815,1816,1817,1819,5372,1822,1823,1824,1825,1827,1829
,5709,1830,1831,1832,1833,1834,1835,1837,1838,1839,1841,1842,1847
,5337,1848,1850,1851,1852,1854,1855,1858,1856,1859,1861,1862,1863
,1867,1866,1868,1870,1871,1873,1874,1878,1879,1880,1881,1883,1884
,1886,1889,1891,1893,1894,1896,1901,1903,1905,1906,1907,1908,1909
,1911,1915,1916,1918,1919,1921,1922,1924,1925,5468,5671,1930,1931
,1932,1933,1935,1937,1942,1943,1944,1947,1949,1951,1952,1955,1956
,1957,1961,1962,1963,5393,1965,1966,1968,1972,1971,1976,1978,1980
,1982,1983,1986,1989,1991,1992,1994,1995,1996,1997,1998,2000,2001
,2002,2003,2005,2006,2008,2009,2012,2013,2015,2016,2018,2024,2026
,2027,2028,2029,2031,2038,2039,2044,2046,2049,2050,2051,2052,2053
,2054,2056,2058,2055,2060,2061,2062,2063,2065,2069,2070,2066,2076
,2074,2072,2077,2079,2080,2083,2085,2086,2088,2089,2091,2092,2094
,2096,2095,2098,2097,2099,2100,2106,2107,2108,2111,2112,2113,2114
,2116,2117,2118,2119,2121,2123,2124,2125,2126,2127,2128,2129,2130
,2134,2138,2139,2144,2145,2147,2148,2150,2151,2152,2153,2156,2157
,2158,2160,2161,2162,2163,2164,2165,2166,2167,2170,2171,2172,2173
,2174,2175,2176,2178,2180,2181,2186,5408,2188,2189,2190,2191,2192
,2195,2198,2199,2201,2203,2206,2207,2209,2211,2212,5236,2213,2215
,2216,2217,2218,2219,5253,2224,2225,2226,2227,2229,2231,2232,2233
,2235,2236,2237,2238,2240,2241,2242,2243,2245,2246,2247,2248,2249
,2251,2257,2259,2260,2261,2262,2263,2264,2265,2266,2267,2270,2272
,2273,2274,2275,2276,2277,2281,2282,2284,2285,2288,2289,2290,2291
,2293,2294,2295,2296,2298,2299,2300,2301,2304,2306,2308,2310,2309
,2312,2313,2316,2317,2322,2324,2320,2318,2330,2331,2332,2334,5711
,2335,2337,2338,2339,2344,2345,2347,2348,2349,5740,2350,2354,2356
,2357,2358,2359,2361,2362,2365,2367,2368,2370,2372,2374,2378,2379
,2380,2381,2382,2385,2388,2389,2391,2392,2393,2395,2396,2398,2400
,2402,2401,2403,2404,2406,2408,2409,2411,2413,2417,2419,2421,2