inbetween.sql

derby database source code.good for you.

insert into t values (20, 200, 'maybe', 'noone is here', 800.0e0,
	1000.0e0, '1892-01-01', '07:30:30', '1892-01-01 07:30:30');
insert into t values (-50, -200, 'never', 'noone is there', -800.0e0,
	-10300.0e0, '2992-01-02', '19:30:59', '2992-01-01 19:30:45');

-- test the static in list xform for the various types
select i from t;
select i from t where i in (80, 20, -60, -1);

select s from t;
select s from t where s in (100, -200, -400);

select c from t;
select c from t where c in ('a', 'goodbye', '');

select v from t;
select v from t where v in ('noone is there', 'everyone is here', '');

select d from t;
select d from t where d in (200, -800);

select r from t;
select r from t where r in (300.0, -10300.0);

select e from t;
select e from t where e in ('2992-01-02', '3999-08-08', '1992-01-02');

select t from t;
select t from t where t in ('12:30:58', '07:20:20', '07:30:30');

-- verify that added predicates getting pushed down
select p from t;
select p from t where p in ('1095-09-08 12:30:58.3', '1892-01-01 07:20:20.0', '1892-01-01 07:30:30.0');

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 2000;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- big in lists (test binary search)
create table big(i int, c char(10));
insert into big values 
	(1, '1'), (2, '2'), (3, '3'), (4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'), (10, '10'), 
	(11, '11'), (12, '12'), (13, '13'), (14, '14'), (15, '15'), (16, '16'), (17, '17'), (18, '18'), (19, '19'), (20, '20'), 
	(21, '21'), (22, '22'), (23, '23'), (24, '24'), (25, '25'), (26, '26'), (27, '27'), (28, '28'), (29, '29'), (30, '30'); 

select * from big where i in (1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where i in (31, 32, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 1);
select * from big where i in (1, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where i in (1, 5, 7, 9, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where i in (1, 5, 7, 9, 13, 15, 17, 19, 23, 25, 27, 29, 31);
select * from big where i in (3, 3, 3, 3);
select * from big where i in (4, 4, 4, 4);
select * from big where c in (1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where c in (31, 32, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 1);
select * from big where c in (1, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where c in (1, 5, 7, 9, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31);
select * from big where c in ('1', '5', '7', '9', '13', '15', '17', '19', '21', '23', '25', '27', '29', '31');
select * from big where i in (1, 5, 7, 9, 13, 15, 17, 19, 23, 25, 27, 29, 31);

-- check consistency of scans, etc.
values ConsistencyChecker();

-- beetle 4316, check "in" with self-reference and correlation, etc.

create table t1 (c1 real, c2 real);
create index i11 on t1 (c1);
create table t2 (c1 real, c2 real);

insert into t1 values (2, 1), (3, 9), (8, 63), (5, 25), (20, 5);
insert into t2 values (4, 8), (8, 8), (7, 6), (5, 6);

select c1 from t1 where c1 in (2, sqrt(c2));
select c1 from t1 where c1 in ('10', '5', '20') and c1 > 3
and c1 < 19;
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 4000;
-- nested loop exists join, right side should be ProjectRestrict on index scan with start and stop keys
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- nested loop exists join, right side should be ProjectRestrict on index scan with start and stop keys
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2) and c1 in ('5', '7'));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- hash exists join, right side PR on hash index scan, no start/stop key, next qualifier "=".
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- hash exists join, right side PR on hash index scan, still no start/stop key, next qualifier "=".
-- It still doesn't have start/stop key because c1 in ('5', '7') is blocked out by 2 others.
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2) and c1 in ('5', '7'));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

create index i12 on t1 (c1, c2);

-- at push "in" time, we determined that it is key and we can push; but at hash time we determined
-- 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));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- 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');
prepare q1 as 'select c1 from t1 where c1 in (3, ?)';
execute q1 using 'values 8';
execute q1 using 'values 9';
remove q1;

-- reset autocommit
autocommit on;

-- Clean up

drop table ss;
drop table t;
drop table u;
drop table big;
drop table t1;
drop table t2;
drop table test;

--reproduction for beetle 5135 ( long list of constants in IN clause)
create table t1(id int);
insert into t1 values(2);
insert into t1 values(5644);
insert into t1 values(723);
insert into t1 values(0);
insert into t1 values(1827);
insert into t1 values(4107);
insert into t1 values(5095);
insert into t1 values(6666);
insert into t1 values(7777);
insert into t1 values(15157);
insert into t1 values(13037);
insert into t1 values(9999);


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