I'm the lead developer of the Mondrian ROLAP server, and I confess that Mondrian generates SQL just like this too. The SQL is unpleasant, but when you want to 'join' a large array of values in memory to a database table, the SQL language doesn't present any better alternatives. Writing the values to a temp table and joining to that is not practical in a connection-pooled environment. It would be nice to say 'x in (?1)' where ?1 is a bind variable which holds a list of integers, but this is not widely supported, even if it is valid. If it's any consolation, other DBMSes that Mondrian runs on have problems with large IN lists too. Oracle refuses to accept an IN list with more than 1,000 elements, and for DB2 the practical limit is 5,000. The reality is that a lot of modern SQL is generated by stupid tools such as Mondrian, and generated SQL often has different patterns than hand-written SQL. Ideally Derby would recognize and optimize these patterns. In this case, one implementation strategy would be to recognise such a construct and implement internally as a join to an 'inline table'. But I don't expect miracles, and I'm not holding my breath. :) Julian
_____ From: Michael Segel [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, November 15, 2006 5:29 AM To: 'Derby Discussion' Subject: RE: Large IN clause produces server error Ok. Sigh. It's early in the morning and I haven't had my first cup of coffee. If we were in the same office, I'd roll up the C section of the WSJ and politely bop you on the head saying "Bad boy! Bad boy! Did you do this?" This is yet another example of a maxim : "Just because you can write code that is syntactically correct, doesn't mean it's a good idea." Now I realize that you're writing a unit test case, but what would be the *USE CASE* that would cause this type of query to be written? Of course I'm assuming that you're testing a section of code that is dynamically written. If the numbers are static, then you should be able to write a fact table and then write a sub query for the NOT IN clause. If the values are in a range, or a set of ranges you can use the '<' or '>' comparators. (Not to mention the combination with the '=' too. ;-) If this is more than just a "hypothetical" possibility, then I'd go back to the developer and tell them to think more about what it is they are doing and write better code. I believe that there is a problem with how derby handles the IN clause, but this goes beyond that. Sorry, HTH _____ From: Robert Enyedi [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 15, 2006 2:47 AM To: Derby Discussion Subject: Re: Large IN clause produces server error The query from the attached log file is: UPDATE task_config_permission SET default_value=NULL WHERE task_type_id IN (SELECT id FROM task_type WHERE proj_id=?) AND CAST(default_value AS INTEGER) NOT IN(0,1,2,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,7 8,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,2 64,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,2 83,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,3 02,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,3 21,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,3 40,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,3 59,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,3 78,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,3 97,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,4 16,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,4 35,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,4 54,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,4 73,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,4 92,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,5 11 ,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 ,7 59,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,7 78,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,7 97,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,8 16,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,8 35,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,8 54,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,8 73,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,8 92,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,9 11,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,9 30,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,9 49,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,9 68,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,9 87,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004, 10 05,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,102 0,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,1 066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,10 81,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,109 6,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,1 142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,11 57,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,117 2,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, 12 03,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,121 8,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,1 264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,12 79,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,129 4,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,1 340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,13 55,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,137 0,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, 14 01,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,141 6,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,1 462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,14 77,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,149 2,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,1 538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,15 53,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,156 8,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, 15 99,1600,1601,1602,1603,1604,1605,1606,1607,1608,1609,1610,1611,1612,1613,161 4,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,1 660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1674,16 75,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,169 0,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,1 736,1737,1738,1739,1740,1741,1742,1743,1744,1745,1746,1747,1748,1749,1750,17 51,1752,1753,1754,1755,1756,1757,1758,1759,1760,1761,1762,1763,1764,1765,176 6,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, 17 97,1798,1799) AND label_id IN(5,32) I'm not saying that this is efficient. This is a unit test which takes things a little bit to the extreme, but nevertheless this scenario might happen. Altering the query and the logic behind it is not really an option at this point so I would be more interested, first of all, for the query not to fail. Please note that the query does not fail on MySQL 4.1 and Oracle 10. Regards, Robert Michael Segel wrote: Can you provide the query? 1800 items in an IN clause? That doesn't sound right or efficient. Why not use a subselect? -----Original Message----- From: Robert Enyedi [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 9:51 AM To: Derby Discussion Subject: Large IN clause produces server error In the attached JUnit error log one can see that for an query containing an IN clause with 1800 items, Derby generates the following error: java.sql.SQLException: Statement too complex. Try rewriting the query to remove complexity. Eliminating many duplicate expressions or breaking up the query and storing interim results in a temporary table can often help resolve this error. SQLSTATE: XBCM4: Java class file format limit(s) exceeded: method:e4 code_length (134022 > 65535) in generated class org.apache.derby.exe.ac8dd747d7x010exe6b4x4757x0000000d8160183. If rewriting the query is not an option, what alternatives are there to overcome this limitation? Regards, Robert
