Materialize/test/sqllogictest/transform/literal_constraints.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
CREATE TABLE t1 (a int, b text)

statement ok
CREATE INDEX idx_t1_a_b ON t1(a,b)

statement ok
INSERT INTO t1 VALUES (0, 'nnn'), (1, 'l1'), (1, 'a'), (2, 'l2'), (3, 'l3'), (1234, 'xxx'), (3456, 'yyy'), (12345, 'zzz'), (null, 'nnn')

# A very large IN list shouldn't cause a stack overflow or other issue in the optimizer, see
# https://github.com/MaterializeInc/database-issues/issues/1924

query T rowsort
SELECT b FROM t1
WHERE a IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528,1529,1530,1531,1532,1533,1534,1535,1536,1537,1538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1573,1574,1575,1576,1577,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1590,1591,1592,1593,1594,1595,1596,1597,1598,1599,1600,1601,1602,1603,1604,1605,1606,1607,1608,1609,1610,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1674,1675,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694,1695,1696,1697,1698,1699,1700,1701,1702,1703,1704,1705,1706,1707,1708,1709,1710,1711,1712,1713,1714,1715,1716,1717,1718,1719,1720,1721,1722,1723,1724,1725,1726,1727,1728,1729,1730,1731,1732,1733,1734,1735,1736,1737,1738,1739,1740,1741,1742,1743,1744,1745,1746,1747,1748,1749,1750,1751,1752,1753,1754,1755,1756,1757,1758,1759,1760,1761,1762,1763,1764,1765,1766,1767,1768,1769,1770,1771,1772,1773,1774,1775,1776,1777,1778,1779,1780,1781,1782,1783,1784,1785,1786,1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1799,1800,1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829,1830,1831,1832,1833,1834,1835,1836,1837,1838,1839,1840,1841,1842,1843,1844,1845,1846,1847,1848,1849,1850,1851,1852,1853,1854,1855,1856,1857,1858,1859,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1870,1871,1872,1873,1874,1875,1876,1877,1878,1879,1880,1881,1882,1883,1884,1885,1886,1887,1888,1889,1890,1891,1892,1893,1894,1895,1896,1897,1898,1899,1900,1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,1915,1916,1917,1918,1919,1920,1921,1922,1923,1924,1925,1926,1927,1928,1929,1930,1931,1932,1933,1934,1935,1936,1937,1938,1939,1940,1941,1942,1943,1944,1945,1946,1947,1948,1949,1950,1951,1952,1953,1954,1955,1956,1957,1958,1959,1960,1961,1962,1963,1964,1965,1966,1967,1968,1969,1970,1971,1972,1973,1974,1975,1976,1977,1978,1979,1980,1981,1982,1983,1984,1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2113,2114,2115,2116,2117,2118,2119,2120,2121,2122,2123,2124,2125,2126,2127,2128,2129,2130,2131,2132,2133,2134,2135,2136,2137,2138,2139,2140,2141,2142,2143,2144,2145,2146,2147,2148,2149,2150,2151,2152,2153,2154,2155,2156,2157,2158,2159,2160,2161,2162,2163,2164,2165,2166,2167,2168,2169,2170,2171,2172,2173,2174,2175,2176,2177,2178,2179,2180,2181,2182,2183,2184,2185,2186,2187,2188,2189,2190,2191,2192,2193,2194,2195,2196,2197,2198,2199,2200,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2214,2215,2216,2217,2218,2219,2220,2221,2222,2223,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2243,2244,2245,2246,2247,2248,2249,2250,2251,2252,2253,2254,2255,2256,2257,2258,2259,2260,2261,2262,2263,2264,2265,2266,2267,2268,2269,2270,2271,2272,2273,2274,2275,2276,2277,2278,2279,2280,2281,2282,2283,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,2295,2296,2297,2298,2299,2300,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2326,2327,2328,2329,2330,2331,2332,2333,2334,2335,2336,2337,2338,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2363,2364,2365,2366,2367,2368,2369,2370,2371,2372,2373,2374,2375,2376,2377,2378,2379,2380,2381,2382,2383,2384,2385,2386,2387,2388,2389,2390,2391,2392,2393,2394,2395,2396,2397,2398,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,2419,2420,2421,2422,2423,2424,2425,2426,2427,2428,2429,2430,2431,2432,2433,2434,2435,2436,2437,2438,2439,2440,2441,2442,2443,2444,2445,2446,2447,2448,2449,2450,2451,2452,2453,2454,2455,2456,2457,2458,2459,2460,2461,2462,2463,2464,2465,2466,2467,2468,2469,2470,2471,2472,2473,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,2497,2498,2499,2500,2501,2502,2503,2504,2505,2506,2507,2508,2509,2510,2511,2512,2513,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2532,2533,2534,2535,2536,2537,2538,2539,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2554,2555,2556,2557,2558,2559,2560,2561,2562,2563,2564,2565,2566,2567,2568,2569,2570,2571,2572,2573,2574,2575,2576,2577,2578,2579,2580,2581,2582,2583,2584,2585,2586,2587,2588,2589,2590,2591,2592,2593,2594,2595,2596,2597,2598,2599,2600,2601,2602,2603,2604,2605,2606,2607,2608,2609,2610,2611,2612,2613,2614,2615,2616,2617,2618,2619,2620,2621,2622,2623,2624,2625,2626,2627,2628,2629,2630,2631,2632,2633,2634,2635,2636,2637,2638,2639,2640,2641,2642,2643,2644,2645,2646,2647,2648,2649,2650,2651,2652,2653,2654,2655,2656,2657,2658,2659,2660,2661,2662,2663,2664,2665,2666,2667,2668,2669,2670,2671,2672,2673,2674,2675,2676,2677,2678,2679,2680,2681,2682,2683,2684,2685,2686,2687,2688,2689,2690,2691,2692,2693,2694,2695,2696,2697,2698,2699,2700,2701,2702,2703,2704,2705,2706,2707,2708,2709,2710,2711,2712,2713,2714,2715,2716,2717,2718,2719,2720,2721,2722,2723,2724,2725,2726,2727,2728,2729,2730,2731,2732,2733,2734,2735,2736,2737,2738,2739,2740,2741,2742,2743,2744,2745,2746,2747,2748,2749,2750,2751,2752,2753,2754,2755,2756,2757,2758,2759,2760,2761,2762,2763,2764,2765,2766,2767,2768,2769,2770,2771,2772,2773,2774,2775,2776,2777,2778,2779,2780,2781,2782,2783,2784,2785,2786,2787,2788,2789,2790,2791,2792,2793,2794,2795,2796,2797,2798,2799,2800,2801,2802,2803,2804,2805,2806,2807,2808,2809,2810,2811,2812,2813,2814,2815,2816,2817,2818,2819,2820,2821,2822,2823,2824,2825,2826,2827,2828,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2839,2840,2841,2842,2843,2844,2845,2846,2847,2848,2849,2850,2851,2852,2853,2854,2855,2856,2857,2858,2859,2860,2861,2862,2863,2864,2865,2866,2867,2868,2869,2870,2871,2872,2873,2874,2875,2876,2877,2878,2879,2880,2881,2882,2883,2884,2885,2886,2887,2888,2889,2890,2891,2892,2893,2894,2895,2896,2897,2898,2899,2900,2901,2902,2903,2904,2905,2906,2907,2908,2909,2910,2911,2912,2913,2914,2915,2916,2917,2918,2919,2920,2921,2922,2923,2924,2925,2926,2927,2928,2929,2930,2931,2932,2933,2934,2935,2936,2937,2938,2939,2940,2941,2942,2943,2944,2945,2946,2947,2948,2949,2950,2951,2952,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2976,2977,2978,2979,2980,2981,2982,2983,2984,2985,2986,2987,2988,2989,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013,3014,3015,3016,3017,3018,3019,3020,3021,3022,3023,3024,3025,3026,3027,3028,3029,3030,3031,3032,3033,3034,3035,3036,3037,3038,3039,3040,3041,3042,3043,3044,3045,3046,3047,3048,3049,3050,3051,3052,3053,3054,3055,3056,3057,3058,3059,3060,3061,3062,3063,3064,3065,3066,3067,3068,3069,3070,3071,3072,3073,3074,3075,3076,3077,3078,3079,3080,3081,3082,3083,3084,3085,3086,3087,3088,3089,3090,3091,3092,3093,3094,3095,3096,3097,3098,3099,3100,3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3119,3120,3121,3122,3123,3124,3125,3126,3127,3128,3129,3130,3131,3132,3133,3134,3135,3136,3137,3138,3139,3140,3141,3142,3143,3144,3145,3146,3147,3148,3149,3150,3151,3152,3153,3154,3155,3156,3157,3158,3159,3160,3161,3162,3163,3164,3165,3166,3167,3168,3169,3170,3171,3172,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3199,3200,3201,3202,3203,3204,3205,3206,3207,3208,3209,3210,3211,3212,3213,3214,3215,3216,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3228,3229,3230,3231,3232,3233,3234,3235,3236,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3250,3251,3252,3253,3254,3255,3256,3257,3258,3259,3260,3261,3262,3263,3264,3265,3266,3267,3268,3269,3270,3271,3272,3273,3274,3275,3276,3277,3278,3279,3280,3281,3282,3283,3284,3285,3286,3287,3288,3289,3290,3291,3292,3293,3294,3295,3296,3297,3298,3299,3300,3301,3302,3303,3304,3305,3306,3307,3308,3309,3310,3311,3312,3313,3314,3315,3316,3317,3318,3319,3320,3321,3322,3323,3324,3325,3326,3327,3328,3329,3330,3331,3332,3333,3334,3335,3336,3337,3338,3339,3340,3341,3342,3343,3344,3345,3346,3347,3348,3349,3350,3351,3352,3353,3354,3355,3356,3357,3358,3359,3360,3361,3362,3363,3364,3365,3366,3367,3368,3369,3370,3371,3372,3373,3374,3375,3376,3377,3378,3379,3380,3381,3382,3383,3384,3385,3386,3387,3388,3389,3390,3391,3392,3393,3394,3395,3396,3397,3398,3399,3400,3401,3402,3403,3404,3405,3406,3407,3408,3409,3410,3411,3412,3413,3414,3415,3416,3417,3418,3419,3420,3421,3422,3423,3424,3425,3426,3427,3428,3429,3430,3431,3432,3433,3434,3435,3436,3437,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3448,3449,3450,3451,3452,3453,3454,3455,3456,3457,3458,3459,3460,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,3475,3476,3477,3478,3479,3480,3481,3482,3483,3484,3485,3486,3487,3488,3489,3490,3491,3492,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3508,3509,3510,3511,3512,3513,3514,3515,3516,3517,3518,3519,3520,3521,3522,3523,3524,3525,3526,3527,3528,3529,3530,3531,3532,3533,3534,3535,3536,3537,3538,3539,3540,3541,3542,3543,3544,3545,3546,3547,3548,3549,3550,3551,3552,3553,3554,3555,3556,3557,3558,3559,3560,3561,3562,3563,3564,3565,3566,3567,3568,3569,3570,3571,3572,3573,3574,3575,3576,3577,3578,3579,3580,3581,3582,3583,3584,3585,3586,3587,3588,3589,3590,3591,3592,3593,3594,3595,3596,3597,3598,3599,3600,3601,3602,3603,3604,3605,3606,3607,3608,3609,3610,3611,3612,3613,3614,3615,3616,3617,3618,3619,3620,3621,3622,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3633,3634,3635,3636,3637,3638,3639,3640,3641,3642,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3653,3654,3655,3656,3657,3658,3659,3660,3661,3662,3663,3664,3665,3666,3667,3668,3669,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3692,3693,3694,3695,3696,3697,3698,3699,3700,3701,3702,3703,3704,3705,3706,3707,3708,3709,3710,3711,3712,3713,3714,3715,3716,3717,3718,3719,3720,3721,3722,3723,3724,3725,3726,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,3739,3740,3741,3742,3743,3744,3745,3746,3747,3748,3749,3750,3751,3752,3753,3754,3755,3756,3757,3758,3759,3760,3761,3762,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,3773,3774,3775,3776,3777,3778,3779,3780,3781,3782,3783,3784,3785,3786,3787,3788,3789,3790,3791,3792,3793,3794,3795,3796,3797,3798,3799,3800,3801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3811,3812,3813,3814,3815,3816,3817,3818,3819,3820,3821,3822,3823,3824,3825,3826,3827,3828,3829,3830,3831,3832,3833,3834,3835,3836,3837,3838,3839,3840,3841,3842,3843,3844,3845,3846,3847,3848,3849,3850,3851,3852,3853,3854,3855,3856,3857,3858,3859,3860,3861,3862,3863,3864,3865,3866,3867,3868,3869,3870,3871,3872,3873,3874,3875,3876,3877,3878,3879,3880,3881,3882,3883,3884,3885,3886,3887,3888,3889,3890,3891,3892,3893,3894,3895,3896,3897,3898,3899,3900,3901,3902,3903,3904,3905,3906,3907,3908,3909,3910,3911,3912,3913,3914,3915,3916,3917,3918,3919,3920,3921,3922,3923,3924,3925,3926,3927,3928,3929,3930,3931,3932,3933,3934,3935,3936,3937,3938,3939,3940,3941,3942,3943,3944,3945,3946,3947,3948,3949,3950,3951,3952,3953,3954,3955,3956,3957,3958,3959,3960,3961,3962,3963,3964,3965,3966,3967,3968,3969,3970,3971,3972,3973,3974,3975,3976,3977,3978,3979,3980,3981,3982,3983,3984,3985,3986,3987,3988,3989,3990,3991,3992,3993,3994,3995,3996,3997,3998,3999,4000,4001,4002,4003,4004,4005,4006,4007,4008,4009,4010,4011,4012,4013,4014,4015,4016,4017,4018,4019,4020,4021,4022,4023,4024,4025,4026,4027,4028,4029,4030,4031,4032,4033,4034,4035,4036,4037,4038,4039,4040,4041,4042,4043,4044,4045,4046,4047,4048,4049,4050,4051,4052,4053,4054,4055,4056,4057,4058,4059,4060,4061,4062,4063,4064,4065,4066,4067,4068,4069,4070,4071,4072,4073,4074,4075,4076,4077,4078,4079,4080,4081,4082,4083,4084,4085,4086,4087,4088,4089,4090,4091,4092,4093,4094,4095,4096,4097,4098,4099,4100,4101,4102,4103,4104,4105,4106,4107,4108,4109,4110,4111,4112,4113,4114,4115,4116,4117,4118,4119,4120,4121,4122,4123,4124,4125,4126,4127,4128,4129,4130,4131,4132,4133,4134,4135,4136,4137,4138,4139,4140,4141,4142,4143,4144,4145,4146,4147,4148,4149,4150,4151,4152,4153,4154,4155,4156,4157,4158,4159,4160,4161,4162,4163,4164,4165,4166,4167,4168,4169,4170,4171,4172,4173,4174,4175,4176,4177,4178,4179,4180,4181,4182,4183,4184,4185,4186,4187,4188,4189,4190,4191,4192,4193,4194,4195,4196,4197,4198,4199,4200,4201,4202,4203,4204,4205,4206,4207,4208,4209,4210,4211,4212,4213,4214,4215,4216,4217,4218,4219,4220,4221,4222,4223,4224,4225,4226,4227,4228,4229,4230,4231,4232,4233,4234,4235,4236,4237,4238,4239,4240,4241,4242,4243,4244,4245,4246,4247,4248,4249,4250,4251,4252,4253,4254,4255,4256,4257,4258,4259,4260,4261,4262,4263,4264,4265,4266,4267,4268,4269,4270,4271,4272,4273,4274,4275,4276,4277,4278,4279,4280,4281,4282,4283,4284,4285,4286,4287,4288,4289,4290,4291,4292,4293,4294,4295,4296,4297,4298,4299,4300,4301,4302,4303,4304,4305,4306,4307,4308,4309,4310,4311,4312,4313,4314,4315,4316,4317,4318,4319,4320,4321,4322,4323,4324,4325,4326,4327,4328,4329,4330,4331,4332,4333,4334,4335,4336,4337,4338,4339,4340,4341,4342,4343,4344,4345,4346,4347,4348,4349,4350,4351,4352,4353,4354,4355,4356,4357,4358,4359,4360,4361,4362,4363,4364,4365,4366,4367,4368,4369,4370,4371,4372,4373,4374,4375,4376,4377,4378,4379,4380,4381,4382,4383,4384,4385,4386,4387,4388,4389,4390,4391,4392,4393,4394,4395,4396,4397,4398,4399,4400,4401,4402,4403,4404,4405,4406,4407,4408,4409,4410,4411,4412,4413,4414,4415,4416,4417,4418,4419,4420,4421,4422,4423,4424,4425,4426,4427,4428,4429,4430,4431,4432,4433,4434,4435,4436,4437,4438,4439,4440,4441,4442,4443,4444,4445,4446,4447,4448,4449,4450,4451,4452,4453,4454,4455,4456,4457,4458,4459,4460,4461,4462,4463,4464,4465,4466,4467,4468,4469,4470,4471,4472,4473,4474,4475,4476,4477,4478,4479,4480,4481,4482,4483,4484,4485,4486,4487,4488,4489,4490,4491,4492,4493,4494,4495,4496,4497,4498,4499,4500,4501,4502,4503,4504,4505,4506,4507,4508,4509,4510,4511,4512,4513,4514,4515,4516,4517,4518,4519,4520,4521,4522,4523,4524,4525,4526,4527,4528,4529,4530,4531,4532,4533,4534,4535,4536,4537,4538,4539,4540,4541,4542,4543,4544,4545,4546,4547,4548,4549,4550,4551,4552,4553,4554,4555,4556,4557,4558,4559,4560,4561,4562,4563,4564,4565,4566,4567,4568,4569,4570,4571,4572,4573,4574,4575,4576,4577,4578,4579,4580,4581,4582,4583,4584,4585,4586,4587,4588,4589,4590,4591,4592,4593,4594,4595,4596,4597,4598,4599,4600,4601,4602,4603,4604,4605,4606,4607,4608,4609,4610,4611,4612,4613,4614,4615,4616,4617,4618,4619,4620,4621,4622,4623,4624,4625,4626,4627,4628,4629,4630,4631,4632,4633,4634,4635,4636,4637,4638,4639,4640,4641,4642,4643,4644,4645,4646,4647,4648,4649,4650,4651,4652,4653,4654,4655,4656,4657,4658,4659,4660,4661,4662,4663,4664,4665,4666,4667,4668,4669,4670,4671,4672,4673,4674,4675,4676,4677,4678,4679,4680,4681,4682,4683,4684,4685,4686,4687,4688,4689,4690,4691,4692,4693,4694,4695,4696,4697,4698,4699,4700,4701,4702,4703,4704,4705,4706,4707,4708,4709,4710,4711,4712,4713,4714,4715,4716,4717,4718,4719,4720,4721,4722,4723,4724,4725,4726,4727,4728,4729,4730,4731,4732,4733,4734,4735,4736,4737,4738,4739,4740,4741,4742,4743,4744,4745,4746,4747,4748,4749,4750,4751,4752,4753,4754,4755,4756,4757,4758,4759,4760,4761,4762,4763,4764,4765,4766,4767,4768,4769,4770,4771,4772,4773,4774,4775,4776,4777,4778,4779,4780,4781,4782,4783,4784,4785,4786,4787,4788,4789,4790,4791,4792,4793,4794,4795,4796,4797,4798,4799,4800,4801,4802,4803,4804,4805,4806,4807,4808,4809,4810,4811,4812,4813,4814,4815,4816,4817,4818,4819,4820,4821,4822,4823,4824,4825,4826,4827,4828,4829,4830,4831,4832,4833,4834,4835,4836,4837,4838,4839,4840,4841,4842,4843,4844,4845,4846,4847,4848,4849,4850,4851,4852,4853,4854,4855,4856,4857,4858,4859,4860,4861,4862,4863,4864,4865,4866,4867,4868,4869,4870,4871,4872,4873,4874,4875,4876,4877,4878,4879,4880,4881,4882,4883,4884,4885,4886,4887,4888,4889,4890,4891,4892,4893,4894,4895)
----
l3
xxx
yyy

# IN list translated to fast path full scan, but no semi-join or lookup, because the index is too wide

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE a IN (1, 1+1)
----
Explained Query (fast path):
  Filter ((#0{a} = 1) OR (#0{a} = 2))
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[*** full scan ***]

Used Indexes:
  - materialize.public.idx_t1_a_b (*** full scan ***)

Target cluster: quickstart

Notices:
  - Notice: Index materialize.public.idx_t1_a_b on t1(a, b) is too wide to use for literal equalities `a IN (1, 2)`.
    Hint: If your literal equalities filter out many rows, create an index whose key exactly matches your literal equalities: (a).

EOF

query IT rowsort
SELECT * FROM t1
WHERE a IN (1, 1+1)
----
1  a
1  l1
2  l2

# Create the index that we would have needed in the previous query.

statement ok
CREATE INDEX idx_t1_a ON t1(a)

# Same query as above, but use the index.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE a IN (1, 1+1)
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t1 idx_t1_a=[lookup values=[(1); (2)]]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query IT rowsort
SELECT * FROM t1
WHERE a IN (1, 1+1)
----
1  a
1  l1
2  l2

# IndexedFilter join executed on fast path with a lookup. (And the filter is removed.)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE a = 2 AND b = 'l2'
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup value=(2, "l2")]

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

query IT rowsort
SELECT * FROM t1
WHERE a = 2 AND b = 'l2'
----
2  l2

# IndexedFilter join executed in a dataflow

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT count(*) FROM t1
WHERE a = 2 AND b = 'l2' OR a = 1 + 1 + 1 AND b = 'l3'
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*)] // { arity: 1 }
        Project () // { arity: 0 }
          ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup values=[(2, "l2"); (3, "l3")]] // { arity: 4 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (0) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

query I rowsort
SELECT count(*) FROM t1
WHERE a = 2 AND b = 'l2' OR a = 1 + 1 + 1 AND b = 'l3'
----
2

# Physical plan for the above. Should have an LIR ArrangeBy for the constant collection.

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT count(*) FROM t1
WHERE a = 2 AND b = 'l2' OR a = 1 + 1 + 1 AND b = 'l3'
----
Explained Query:
  With
    cte l0 =
      Reduce::Accumulable
        simple_aggrs[0]=(0, 0, count(*))
        val_plan
          project=(#0)
          map=(true)
        key_plan=id
        Join::Linear
          linear_stage[0]
            closure
              project=()
            lookup={ relation=0, key=[#0{a}, #1{b}] }
            stream={ key=[#0, #1], thinning=() }
          source={ relation=1, key=[#0, #1] }
          Get::PassArrangements materialize.public.t1
            raw=false
            arrangements[0]={ key=[#0{a}, #1{b}], permutation=id, thinning=() }
            types=[integer?, text?]
          ArrangeBy
            raw=true
            arrangements[0]={ key=[#0, #1], permutation=id, thinning=() }
            types=[integer, text]
            Constant
              - (2, "l2")
              - (3, "l3")
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0) }
      Mfp
        project=(#0)
        map=(0)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0) }
          Constant
            - ()

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

# `a = NULL` should NOT find the NULL in the table.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE a = NULL OR a = 2
----
Explained Query (fast path):
  Filter (null OR (#0{a} = 2))
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[*** full scan ***]

Used Indexes:
  - materialize.public.idx_t1_a_b (*** full scan ***)

Target cluster: quickstart

EOF

query IT rowsort
SELECT * FROM t1
WHERE a = NULL OR a = 2
----
2  l2

# Filter predicate occurring also as an output column

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a = 1 FROM t1 WHERE a = 1;
----
Explained Query (fast path):
  Project (#3)
    Map (true)
      ReadIndex on=materialize.public.t1 idx_t1_a=[lookup value=(1)]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query B rowsort
SELECT a = 1 FROM t1 WHERE a = 1;
----
true
true


# IN list with multiple fields --> RecordCreate has to be decomposed

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE (a,b) IN ((1,'l1'), (2,'l2'))
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup values=[(1, "l1"); (2, "l2")]]

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

query IT
SELECT * FROM t1
WHERE (a,b) IN ((1,'l1'), (2,'l2'))
----
1  l1
2  l2

# Exercise LiteralConstraints::distribute_and_over_or (by having 1 in both tuples)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE (a,b) IN ((1, 'l1'), (1, 'a'))
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup values=[(1, "a"); (1, "l1")]]

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

query IT rowsort
SELECT * FROM t1
WHERE (a,b) IN ((1, 'l1'), (1, 'a'))
----
1  a
1  l1

statement ok
CREATE TABLE t2 (a int, b int, c int)

statement ok
CREATE INDEX idx_t2_a_b_c ON t2(a,b,c)

statement ok
INSERT INTO t2 VALUES (1,11,21),(2,22,32),(3,23,33),(1,4,7)

# LiteralConstraints::distribute_and_over_or needs to do 2 steps

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2
WHERE (a,b,c) IN ((1,2,3), (1,4,5), (1,4,7))
----
Explained Query (fast path):
  Project (#0{a}..=#2{c})
    ReadIndex on=materialize.public.t2 idx_t2_a_b_c=[lookup values=[(1, 2, 3); (1, 4, 5); (1, 4, 7)]]

Used Indexes:
  - materialize.public.idx_t2_a_b_c (lookup)

Target cluster: quickstart

EOF

query III rowsort
SELECT * FROM t2
WHERE (a,b,c) IN ((1,2,3), (1,4,5), (1,4,7))
----
1  4  7

# On the fast path, the IN list is looking for a record that is not present in the table, but there is a larger record.
# This exercises the situation when `cursor.seek_key` in `compute_state.rs` tries to seek to a non-existent key, and it
# lands on a different key.

query III rowsort
SELECT * FROM t2
WHERE (a,b,c) IN ((1,2,3))
----

# Shouldn't remove the literal constraint from the filter (because the residual OR branches differ), but should still
# use it

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE (a = 1) OR (a = 3 AND b = 'l3')
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    Filter ((#0{a} = 1) OR ((#0{a} = 3) AND (#1{b} = "l3")))
      ReadIndex on=materialize.public.t1 idx_t1_a=[lookup values=[(1); (3)]]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query IT
SELECT * FROM t1
WHERE (a = 1) OR (a = 3 AND b = 'l3')
----
1  a
1  l1
3  l3

# IN list is not fully a literal, so nothing to do

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE a IN (1, a+a)
----
Explained Query (fast path):
  Filter ((#0{a} = 1) OR (#0{a} = (#0{a} + #0{a})))
    ReadIndex on=materialize.public.t1 idx_t1_a_b=[*** full scan ***]

Used Indexes:
  - materialize.public.idx_t1_a_b (*** full scan ***)

Target cluster: quickstart

EOF

query IT
SELECT * FROM t1
WHERE a IN (2, a+a)
ORDER BY 1
----
0  nnn
2  l2

# A non-trivial expression in a literal constraint

statement ok
CREATE INDEX idx_t1_aa ON t1(a+a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE a+a = 2
----
Explained Query (fast path):
  Project (#1{a})
    ReadIndex on=materialize.public.t1 idx_t1_aa=[lookup value=(2)]

Used Indexes:
  - materialize.public.idx_t1_aa (lookup)

Target cluster: quickstart

EOF

query I
SELECT a FROM t1
WHERE a+a = 2
----
1
1

# A non-trivial expression in a literal constraint that came from an IN of multiple elements

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE a+a IN (-1, 1, 2, 3, 6, 7, 9)
----
Explained Query (fast path):
  Project (#1{a})
    ReadIndex on=materialize.public.t1 idx_t1_aa=[lookup values=[(-1); (1); (2); (3); (6); (7); (9)]]

Used Indexes:
  - materialize.public.idx_t1_aa (lookup)

Target cluster: quickstart

EOF

query I
SELECT a FROM t1
WHERE a+a IN (-1, 1, 2, 3, 6, 7, 9)
----
1
1
3

# A non-trivial expression in a literal constraint, and the same expression also appears in the output.
# Exercises the second part of `inline_literal_constraints`.
# https://github.com/MaterializeInc/database-issues/issues/3783#issuecomment-1192763523

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a+a FROM t1
WHERE a+a IN (2, 6);
----
Explained Query (fast path):
  Project (#4)
    Map ((#1{a} + #1{a}))
      ReadIndex on=materialize.public.t1 idx_t1_aa=[lookup values=[(2); (6)]]

Used Indexes:
  - materialize.public.idx_t1_aa (lookup)

Target cluster: quickstart

EOF

query I
SELECT a+a FROM t1
WHERE a+a IN (2, 6)
----
2
2
6

statement ok
CREATE INDEX idx_t2_a ON t2(a);

# Only some of the fields are literals in an IN list. This exercises the
# `record_create(a1, a2, ...) = record_create(b1, b2, ...)` case in `MirScalarExpr::reduce()`

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t2
WHERE (a,b) IN ((1, 4*a), (2, 5*a), (3, a+20))
----
Explained Query (fast path):
  Project (#0{a}..=#2{c})
    Filter (((#0{a} = 1) AND (#1{b} = (4 * #0{a}))) OR ((#0{a} = 2) AND (#1{b} = (5 * #0{a}))) OR ((#0{a} = 3) AND (#1{b} = (#0{a} + 20))))
      ReadIndex on=materialize.public.t2 idx_t2_a=[lookup values=[(1); (2); (3)]]

Used Indexes:
  - materialize.public.idx_t2_a (lookup)

Target cluster: quickstart

EOF

query III rowsort
SELECT * FROM t2
WHERE (a,b) IN ((1, 4*a), (2, 5*a), (3, a+20))
----
1  4  7
3  23  33

# Regression test for https://github.com/MaterializeInc/database-issues/issues/4144
# Copied from test/sqllogictest/sqlite/test/index/orderby_nosort/100/slt_good_3.test

statement ok
CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)

statement ok
INSERT INTO tab0 VALUES(0,146,632.63,'shwwd',703,412.47,'xsppr')

statement ok
INSERT INTO tab0 VALUES(1,81,536.29,'trhdh',49,726.3,'chuxv')

query I rowsort
SELECT pk FROM tab0 WHERE (((col4 >= 660.98) AND ((col4 IN (724.71,445.29,441.2,606.49) AND ((col0 > 523)) AND (((col3 > 975 OR (col3 IS NULL) AND (col1 > 259.62 OR col0 >= 896 OR col1 <= 947.3 OR col3 IS NULL OR (col3 > 788) AND ((col3 < 264)) AND col0 < 823 AND ((col0 < 164) AND col1 > 95.85) AND (col0 > 534) AND col4 > 922.46 AND col3 >= 528 AND col3 > 762 AND col0 <= 903) AND col3 IS NULL))) OR col1 < 760.15 OR ((((((col0 >= 551)))))) OR col0 IN (425,98,842,550))) OR col0 < 397 AND col4 > 32.43 AND col3 >= 13 OR col3 > 474 OR ((col1 < 746.36)) AND col1 >= 499.2 AND col0 < 362 OR ((col0 <= 979) AND ((col0 IS NULL) AND (col1 > 251.48) AND (col3 > 838 OR col3 > 529 AND col0 IN (627,345,774,557) OR ((col0 <= 992 OR (((col3 BETWEEN 277 AND 34)))) AND (col0 > 561))) AND col1 >= 912.45 OR (col0 < 4 AND (col3 <= 996 AND (col0 <= 364))) OR ((col0 < 979)) OR col1 > 599.0 OR (col4 <= 26.30 AND ((col3 = 547 AND col3 >= 799) OR (col1 BETWEEN 841.6 AND 776.13)) AND col1 > 614.41 OR col4 = 581.12))) AND col0 < 480 OR (col3 = 763 AND (col3 IS NULL)))) ORDER BY 1 DESC
----
0
1

# Regression test for https://github.com/MaterializeInc/database-issues/issues/4153
# Also tests the situation when `constraints_to_residual_sets` has multiple elements in the sets.

statement ok
CREATE INDEX idx_t1_b ON t1(b)

query I rowsort
SELECT a FROM t1
WHERE (a < 3 OR a > 0) AND b IN ('l1', 'l2', 'l3', 'l9')
----
1
2
3

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE (a < 3 OR a > 0) AND b IN ('l1', 'l2', 'l3', 'l9')
----
Explained Query (fast path):
  Project (#1{a})
    Filter ((#1{a} < 3) OR (#1{a} > 0))
      ReadIndex on=materialize.public.t1 idx_t1_b=[lookup values=[("l1"); ("l2"); ("l3"); ("l9")]]

Used Indexes:
  - materialize.public.idx_t1_b (lookup)

Target cluster: quickstart

EOF

# More tricky tests for `remove_literal_constraints`

query I rowsort
SELECT a FROM t1
WHERE (a < 3 OR a > 0 OR a < 7) AND b IN ('l1', 'l2', 'l3', 'l9') AND (b like 'l%' OR a > 5)
----
1
2
3

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE (a < 3 OR a > 0 OR a < 7) AND b IN ('l1', 'l2', 'l3', 'l9') AND (b like 'l%' OR a > 5)
----
Explained Query (fast path):
  Project (#1{a})
    Filter ((#1{a} < 3) OR (#1{a} < 7) OR (#1{a} > 0)) AND (like["l%"](#0{b}) OR (#1{a} > 5))
      ReadIndex on=materialize.public.t1 idx_t1_b=[lookup values=[("l1"); ("l2"); ("l3"); ("l9")]]

Used Indexes:
  - materialize.public.idx_t1_b (lookup)

Target cluster: quickstart

EOF

query IT
SELECT * FROM t1
WHERE ((b = 'l1' AND a = 1) OR (a = 2 AND b = 'l2')) AND (b like 'nonono' OR (b like 'l%' AND a < 10))
----
1  l1
2  l2

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1
WHERE ((b = 'l1' AND a = 1) OR (a = 2 AND b = 'l2')) AND (b like 'nonono' OR (b like 'l%' AND a < 10))
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    Filter (like["nonono"](#1{b}) OR (like["l%"](#1{b}) AND (#0{a} < 10)))
      ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup values=[(1, "l1"); (2, "l2")]]

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

# Even more tricky test for `remove_literal_constraints`: has multiple fields in the IN list and MFP CSE

query I
SELECT a FROM t1
WHERE (a % 3 = 0 OR a % 3 = 1) AND (a,b) IN ((1,'l1'), (2,'l2'), (3,'l3'), (9,'l9'))
----
1
3

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE (a % 3 = 0 OR a % 3 = 1) AND (a,b) IN ((1,'l1'), (2,'l2'), (3,'l3'), (9,'l9'))
----
Explained Query (fast path):
  Project (#0{a})
    Filter ((#4 = 0) OR (#4 = 1))
      Map ((#0{a} % 3))
        ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup values=[(1, "l1"); (2, "l2"); (3, "l3"); (9, "l9")]]

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)

Target cluster: quickstart

EOF

# Negative test for `remove_literal_constraints` when `constraints_to_residual_sets` has multiple elements in the sets.
# There is also an impossible constraint to remove by `remove_impossible_or_args`.

query I
SELECT a FROM t1
WHERE (
    (a = 1 AND b like 'nope') OR
    (a = 2) OR
    (a = 1 AND a = 2)
) AND
(b like 'l%' OR b like 'aaa')
----
2

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE (
    (a = 1 AND b like 'nope') OR
    (a = 2) OR
    (a = 1 AND a = 2)
) AND
(b like 'l%' OR b like 'aaa')
----
Explained Query (fast path):
  Project (#0{a})
    Filter (like["aaa"](#1{b}) OR like["l%"](#1{b})) AND ((#0{a} = 2) OR (like["nope"](#1{b}) AND (#0{a} = 1)))
      ReadIndex on=materialize.public.t1 idx_t1_a=[lookup values=[(1); (2)]]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

# Impossible predicates (exercise `remove_impossible_or_args`)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE a = 3 AND a = 5
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE a IN (1,2) AND a IN (3,4,5)
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

# Only some subexpressions of the predicates can be removed due to being impossible

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE (a = 4 AND a = 7) OR (a = 9)
----
Explained Query (fast path):
  Project (#0{a})
    ReadIndex on=materialize.public.t1 idx_t1_a=[lookup value=(9)]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE a IN (1,2) AND a IN (2,3,4)
----
Explained Query (fast path):
  Project (#0{a})
    ReadIndex on=materialize.public.t1 idx_t1_a=[lookup value=(2)]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

# Inlining complex expressions in `inline_literal_constraints`.
# The important thing in this query is the `(a = 2 AND a = 3)` appearing twice. The other stuff is just to make it
# impossible for `undistribute_and_or` to factor out the `(a = 2 AND a = 3)`, so that MFP CSE can kick in, and then we
# can test that `inline_literal_constraints` inlines it back.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM t1
WHERE
(b = 'bbb' OR b = 'y') AND
((a = 2 AND a = 3) OR b = 'x' OR b = 'y') AND
((a = 2 AND a = 3) OR b = 'z')
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

# IndexedFilter before a join.
# - LiteralConstraints has to run before JoinImplementation. Otherwise, JoinImplementation lifts the predicate, and then
#   LiteralConstraints can't create an IndexedFilter from the predicate anymore, because it's not on top of a Get.
# - This should be a Delta join! To ensure this, we have to look behind the IndexedFilter when collecting the available
#   arrangements on the join inputs.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT t1.a, t1.b, t2.c FROM t1, t2, t2 as t2p
WHERE
    t1.a = t2.a AND
    t2.a = t2p.a AND
    t1.b IN ('l2', 'l3')
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{a}]] // { arity: 3 }
        ReadIndex on=t2 idx_t2_a=[delta join lookup] // { arity: 3 }
  Return // { arity: 3 }
    Project (#0{a}, #1{b}, #5{c}) // { arity: 3 }
      Filter (#0{a}) IS NOT NULL // { arity: 9 }
        Join on=(#0{a} = #3{a} = #6{a}) type=delta // { arity: 9 }
          implementation
            %0:t1 » %1:l0[#0{a}]KA » %2:l0[#0{a}]KA
            %1:l0 » %0:t1[#0{a}]KAe » %2:l0[#0{a}]KA
            %2:l0 » %0:t1[#0{a}]KAe » %1:l0[#0{a}]KA
          ArrangeBy keys=[[#0{a}]] // { arity: 3 }
            ReadIndex on=materialize.public.t1 idx_t1_b=[lookup values=[("l2"); ("l3")]] // { arity: 3 }
          Get l0 // { arity: 3 }
          Get l0 // { arity: 3 }

Used Indexes:
  - materialize.public.idx_t2_a (delta join lookup)
  - materialize.public.idx_t1_b (lookup)

Target cluster: quickstart

EOF

query ITI rowsort
SELECT t1.a, t1.b, t2.c FROM t1, t2
WHERE
    t1.a = t2.a AND
    t1.b IN ('l2', 'l3')
----
2  l2  32
3  l3  33

# This is similar to the previous query, but it also tests that EquivalencePropagation and LiteralLifting don't mess up
# the IndexedFilter join.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT t1.a, t1.b, t2.c FROM t1, t2
WHERE
    t1.a = t2.a AND
    t1.b = 'l2'
----
Explained Query:
  Project (#0{a}, #1{b}, #5{c}) // { arity: 3 }
    Filter (#0{a}) IS NOT NULL // { arity: 6 }
      Join on=(#0{a} = #3{a}) type=differential // { arity: 6 }
        implementation
          %0:t1[#0{a}]KAe » %1:t2[#0{a}]KAe
        ArrangeBy keys=[[#0{a}]] // { arity: 3 }
          ReadIndex on=materialize.public.t1 idx_t1_b=[lookup value=("l2")] // { arity: 3 }
        ArrangeBy keys=[[#0{a}]] // { arity: 3 }
          ReadIndex on=t2 idx_t2_a=[differential join] // { arity: 3 }

Used Indexes:
  - materialize.public.idx_t2_a (differential join)
  - materialize.public.idx_t1_b (lookup)

Target cluster: quickstart

EOF

query ITI rowsort
SELECT t1.a, t1.b, t2.c FROM t1, t2
WHERE
    t1.a = t2.a AND
    t1.b = 'l2'
----
2  l2  32

# EquivalencePropagation is being propagated from one join input to the other: even though the literal constraint mentions
# only `t1`, the `t1.a = t2.a` join constraint allows us to propagate the literal constraint from `t1.a` to `t2.a`, and
# then insert an IndexedFilter also for `t2.a`.
# Note that the final join's key disappears: this is ok, it happens because we know that t1.a and t2.a are both 2, so
# they are always equal.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT t1.a, t1.b, t2.c FROM t1, t2
WHERE
    t1.a = t2.a AND
    t1.a = 2 AND t1.b = 'l2'
----
Explained Query:
  CrossJoin type=differential // { arity: 3 }
    implementation
      %0:t1[×]e » %1:t2[×]e
    ArrangeBy keys=[[]] // { arity: 2 }
      Project (#0{a}, #1{b}) // { arity: 2 }
        ReadIndex on=materialize.public.t1 idx_t1_a_b=[lookup value=(2, "l2")] // { arity: 4 }
    ArrangeBy keys=[[]] // { arity: 1 }
      Project (#2{c}) // { arity: 1 }
        ReadIndex on=materialize.public.t2 idx_t2_a=[lookup value=(2)] // { arity: 4 }

Used Indexes:
  - materialize.public.idx_t1_a_b (lookup)
  - materialize.public.idx_t2_a (lookup)

Target cluster: quickstart

EOF

query ITI rowsort
SELECT t1.a, t1.b, t2.c FROM t1, t2
WHERE
    t1.a = t2.a AND
    t1.a = 2 AND t1.b = 'l2'
----
2  l2  32

# Implicit casts between literals and indexed column types shouldn't prevent index access

statement ok
CREATE TABLE foo(a SMALLINT, b INT, c BIGINT, v VARCHAR);

statement ok
CREATE DEFAULT INDEX ON foo;

statement ok
INSERT INTO foo VALUES (0, 1, 2, 'xxx'), (3, 4, 5, 'yyy');

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE a = 0 AND b = 1 AND c = 2 AND v = 'xxx';
----
Explained Query (fast path):
  Project (#0{a}..=#3{v})
    ReadIndex on=materialize.public.foo foo_primary_idx=[lookup value=(0, 1, 2, "xxx")]

Used Indexes:
  - materialize.public.foo_primary_idx (lookup)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE 0 = a AND 1 = b AND 2 = c AND 'xxx' = v;
----
Explained Query (fast path):
  Project (#0{a}..=#3{v})
    ReadIndex on=materialize.public.foo foo_primary_idx=[lookup value=(0, 1, 2, "xxx")]

Used Indexes:
  - materialize.public.foo_primary_idx (lookup)

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE a = 0 AND b = 1 AND c = 2 AND v = 'xxx';
----
0  1  2  xxx

statement ok
CREATE INDEX idx_foo_a ON foo(a);

# Check that the deduplication in `remove_impossible_or_args` treats `a = 0` and `a = 0::SMALLINT` the same

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM foo
WHERE a = 0 AND a = 0::SMALLINT;
----
Explained Query (fast path):
  Project (#0{a})
    ReadIndex on=materialize.public.foo idx_foo_a=[lookup value=(0)]

Used Indexes:
  - materialize.public.idx_foo_a (lookup)

Target cluster: quickstart

EOF

query I rowsort
SELECT a FROM foo
WHERE a = 0 AND a = 0::SMALLINT;
----
0

# Check that `remove_impossible_or_args` recognizes that `a = 0 AND a = 2::SMALLINT` is impossible.
# For this, `any_expr_eq_literal` should peel off the cast in the returned expression.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM foo
WHERE (a = 0 AND a = 2::SMALLINT) OR a = 3;
----
Explained Query (fast path):
  Project (#0{a})
    ReadIndex on=materialize.public.foo idx_foo_a=[lookup value=(3)]

Used Indexes:
  - materialize.public.idx_foo_a (lookup)

Target cluster: quickstart

EOF

query I rowsort
SELECT a FROM foo
WHERE (a = 0 AND a = 2::SMALLINT) OR a = 3;
----
3

# The (not-anymore-needed) workaround of explicitly casting the literal to the smaller type should still work for
# SMALLINT. (It never worked for VARCHAR.)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a FROM foo
WHERE a = 3::SMALLINT;
----
Explained Query (fast path):
  Project (#0{a})
    ReadIndex on=materialize.public.foo idx_foo_a=[lookup value=(3)]

Used Indexes:
  - materialize.public.idx_foo_a (lookup)

Target cluster: quickstart

EOF

query I rowsort
SELECT a FROM foo
WHERE a = 3::SMALLINT;
----
3

# The (not-anymore-needed) workaround of adding an explicit cast at index creation should still work for SMALLINT

statement ok
DROP INDEX idx_foo_a;

statement ok
CREATE INDEX idx_foo_a_cast ON foo(a::INTEGER);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE a = 0;
----
Explained Query (fast path):
  Project (#1{a}..=#4{v})
    ReadIndex on=materialize.public.foo idx_foo_a_cast=[lookup value=(0)]

Used Indexes:
  - materialize.public.idx_foo_a_cast (lookup)

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE a = 0;
----
0  1  2  xxx

# The (not-anymore-needed) workaround of adding an explicit cast at index creation should still work for VARCHAR

statement ok
CREATE INDEX idx_foo_v_cast ON foo(v::TEXT);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE v = 'xxx';
----
Explained Query (fast path):
  Project (#1{a}..=#4{v})
    ReadIndex on=materialize.public.foo idx_foo_v_cast=[lookup value=("xxx")]

Used Indexes:
  - materialize.public.idx_foo_v_cast (lookup)

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE v = 'xxx';
----
0  1  2  xxx

# When both an explicitly cast and a raw index are present, we should choose the raw one by performing the inverse cast.
# SMALLINT

statement ok
CREATE INDEX idx_foo_a ON foo(a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE a = 0;
----
Explained Query (fast path):
  Project (#0{a}..=#3{v})
    ReadIndex on=materialize.public.foo idx_foo_a=[lookup value=(0)]

Used Indexes:
  - materialize.public.idx_foo_a (lookup)

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE a = 0;
----
0  1  2  xxx

# When both an explicitly cast and a raw index are present, we should choose the raw one by performing the inverse cast.
# VARCHAR

statement ok
CREATE INDEX idx_foo_v ON foo(v);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE v = 'xxx';
----
Explained Query (fast path):
  Project (#1{a}..=#3{c}, #0{v})
    ReadIndex on=materialize.public.foo idx_foo_v=[lookup value=("xxx")]

Used Indexes:
  - materialize.public.idx_foo_v (lookup)

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE v = 'xxx';
----
0  1  2  xxx

# Literal equalities with a cast where the inverse cast on the literal errors out should be detected as impossible.
# See `MirScalarExpr::impossible_literal_equality_because_types`.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM foo
WHERE a = 1000000;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query IIIT rowsort
SELECT * FROM foo
WHERE a = 1000000;
----


# In the following query, we might be able to detect literal constraints if we called `canonicalize_equivalences`

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a, b, c FROM t2 WHERE a IN (a + b, a + c, 1 + 5) AND a + b = 5 AND a + c = 4;
----
Explained Query (fast path):
  Project (#0{a}..=#2{c})
    Filter (#3 = 5) AND (#4 = 4) AND ((#0{a} = #3) OR (#0{a} = #4) OR (#0{a} = 6))
      Map ((#0{a} + #1{b}), (#0{a} + #2{c}))
        ReadIndex on=materialize.public.t2 idx_t2_a_b_c=[*** full scan ***]

Used Indexes:
  - materialize.public.idx_t2_a_b_c (*** full scan ***)

Target cluster: quickstart

EOF

# Regression test for https://github.com/MaterializeInc/database-issues/issues/4506
# CREATE INDEX should call reduce on index expressions.

statement ok
CREATE TABLE bar(a BOOL, b BOOL);

statement ok
CREATE INDEX ON bar(b AND a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM bar WHERE (b AND a) = TRUE;
----
Explained Query (fast path):
  Project (#1{a}, #2{b})
    ReadIndex on=materialize.public.bar bar_expr_idx=[lookup value=(true)]

Used Indexes:
  - materialize.public.bar_expr_idx (lookup)

Target cluster: quickstart

EOF

# Regression test for https://github.com/MaterializeInc/database-issues/issues/5424
# We should resist the temptation to do an index lookup in the following test; `preserves_uniqueness` should be checked
# not just on the inverse cast, but on the original cast as well.

statement ok
CREATE TABLE t3 ( c0 FLOAT );

statement ok
INSERT INTO t3 VALUES (-0.1), (0.6), (1), (0);

statement ok
CREATE INDEX t3i0 ON t3 (c0);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t3 WHERE NOT (((t3.c0)::INT != 0));
----
Explained Query (fast path):
  Filter (0 = double_to_integer(#0{c0}))
    ReadIndex on=materialize.public.t3 t3i0=[*** full scan ***]

Used Indexes:
  - materialize.public.t3i0 (*** full scan ***)

Target cluster: quickstart

EOF

query R rowsort
SELECT * FROM t3 WHERE NOT (((t3.c0)::INT != 0));
----
-0.1
0

# Float index lookup should work. (No casts in this test.)
# Also tests that deduplication works between `t3.c0 != -0.1` and `-0.1 != t3.c0`:
# `-0.1` should appear in lookup_values only once!

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t3 WHERE NOT ((t3.c0 != 0.6) AND (t3.c0 != -0.1) AND (t3.c0 != 0.1) AND (-0.1 != t3.c0));
----
Explained Query (fast path):
  Project (#0{c0})
    ReadIndex on=materialize.public.t3 t3i0=[lookup values=[(0.6); (0.1); (-0.1)]]

Used Indexes:
  - materialize.public.t3i0 (lookup)

Target cluster: quickstart

EOF

query R rowsort
SELECT * FROM t3 WHERE NOT ((t3.c0 != 0.6) AND (t3.c0 != -0.1) AND (t3.c0 != 0.1) AND (-0.1 != t3.c0));
----
-0.1
0.6

# The following (rounding) cast will be executed by the constant folding in `MirScalarExpr::reduce`. We should look up
# the rounded value.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM t3 WHERE t3.c0 = 0.8::INT OR t3.c0 = -0.1;
----
Explained Query (fast path):
  Project (#0{c0})
    ReadIndex on=materialize.public.t3 t3i0=[lookup values=[(1); (-0.1)]]

Used Indexes:
  - materialize.public.t3i0 (lookup)

Target cluster: quickstart

EOF

query R rowsort
SELECT * FROM t3 WHERE t3.c0 = 0.8::INT OR t3.c0 = -0.1;
----
-0.1
1

# Check the nullability- and unique key inference:
# The `ReadIndex` should have
# - non-nullable first 3 columns
# - non-nullable last 3 columns
# - the unique keys on #1 and #2 should remain
# - (It would be great to have a key that includes the first 3 columns, but our key inference is not smart enough
#   currently.)

statement ok
CREATE TABLE t4 (a int, b int unique, c int unique, d int, unique (a, b, c));

statement ok
CREATE INDEX idx_t4_a_b_c ON t4(a, b, c)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(arity, join implementations, types, keys, humanized expressions) AS VERBOSE TEXT FOR
SELECT sum(a*b*c*d) FROM t4
WHERE (a,b,c) IN ((15, 16, 17), (25, 26, 111), (35, 36, 111));
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum((((#0{a} * #1{b}) * #2{c}) * #3{d}))] // { arity: 1, types: "(bigint?)", keys: "([])" }
        Project (#0{a}..=#3{d}) // { arity: 4, types: "(integer, integer, integer, integer?)", keys: "([1], [2])" }
          ReadIndex on=materialize.public.t4 idx_t4_a_b_c=[lookup values=[(15, 16, 17); (25, 26, 111); (35, 36, 111)]] // { arity: 7, types: "(integer, integer, integer, integer?, integer, integer, integer)", keys: "([1], [2])" }
  Return // { arity: 1, types: "(bigint?)", keys: "([])" }
    Union // { arity: 1, types: "(bigint?)", keys: "([])" }
      Get l0 // { arity: 1, types: "(bigint?)", keys: "([])" }
      Map (null) // { arity: 1, types: "(bigint?)", keys: "()" }
        Union // { arity: 0, types: "()", keys: "()" }
          Negate // { arity: 0, types: "()", keys: "()" }
            Project () // { arity: 0, types: "()", keys: "([])" }
              Get l0 // { arity: 1, types: "(bigint?)", keys: "([])" }
          Constant // { arity: 0, types: "()", keys: "([])" }
            - ()

Used Indexes:
  - materialize.public.idx_t4_a_b_c (lookup)

Target cluster: quickstart

EOF

# WMR

statement ok
CREATE TABLE t5 (f1 int);

statement ok
CREATE INDEX idx_t5_f1 ON t5(f1);

statement ok
INSERT INTO t5 VALUES (0), (2), (5);

query I
WITH MUTUALLY RECURSIVE (RETURN AT RECURSION LIMIT = 3)
  c0(f1 int) as (
    (SELECT * FROM t5)
    UNION
    (SELECT t5.f1 + c0.f1
    FROM t5, c0
    WHERE (t5.f1 = 0 OR t5.f1 = 2 OR t5.f1 = 8))
  )
SELECT * FROM c0;
----
0
2
4
5
6
7
9

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE (RETURN AT RECURSION LIMIT = 3)
  c0(f1 int) as (
    (SELECT * FROM t5)
    UNION
    (SELECT t5.f1 + c0.f1
    FROM t5, c0
    WHERE (t5.f1 = 0 OR t5.f1 = 2 OR t5.f1 = 8))
  )
SELECT * FROM c0;
----
Explained Query:
  With Mutually Recursive [recursion_limit=3, return_at_limit]
    cte l0 =
      Distinct project=[#0{f1}]
        Union
          ReadIndex on=t5 idx_t5_f1=[*** full scan ***]
          Project (#2)
            Map ((#0{f1} + #1{f1}))
              CrossJoin type=differential
                ArrangeBy keys=[[]]
                  Project (#0{f1})
                    ReadIndex on=materialize.public.t5 idx_t5_f1=[lookup values=[(0); (2); (8)]]
                ArrangeBy keys=[[]]
                  Get l0
  Return
    Get l0

Used Indexes:
  - materialize.public.idx_t5_f1 (*** full scan ***, lookup)

Target cluster: quickstart

EOF

# Check literal constraints with LIMIT

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT a,b
FROM t1
WHERE a IN (4,3,2,1)
ORDER BY -a, b
LIMIT 3;
----
Explained Query (fast path):
  Finish order_by=[#2 asc nulls_last, #1 asc nulls_last] limit=3 output=[#0, #1]
    Project (#0{a}, #1{b}, #3)
      Map (-(#0{a}))
        ReadIndex on=materialize.public.t1 idx_t1_a=[lookup values=[(1); (2); (3); (4)]]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query IT nosort
SELECT a,b
FROM t1
WHERE a IN (4,3,2,1)
ORDER BY -a, b
LIMIT 3;
----
3  l3
2  l2
1  a

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT a,b
FROM t1
WHERE a IN (4,3,2,1)
ORDER BY -a, b
LIMIT 3 OFFSET 1;
----
Explained Query (fast path):
  Finish order_by=[#2 asc nulls_last, #1 asc nulls_last] limit=3 offset=1 output=[#0, #1]
    Project (#0{a}, #1{b}, #3)
      Map (-(#0{a}))
        ReadIndex on=materialize.public.t1 idx_t1_a=[lookup values=[(1); (2); (3); (4)]]

Used Indexes:
  - materialize.public.idx_t1_a (lookup)

Target cluster: quickstart

EOF

query IT nosort
SELECT a,b
FROM t1
WHERE a IN (4,3,2,1)
ORDER BY -a, b
LIMIT 3 OFFSET 1;
----
2  l2
1  a
1  l1