You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Robert Enyedi <ro...@intland.com> on 2006/11/14 16:50:50 UTC
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
Re: Large IN clause produces server error
Posted by Bryan Pendleton <bp...@amberpoint.com>.
>>> If rewriting the query is not an option, what alternatives are there to
>>> overcome this limitation?
What version of Derby are you using? (Apologies if you already said that
and I missed it). If you are using a version prior to 10.2, you should
definitely try 10.2, as there was a *lot* of work in the code generation
portion of Derby to avoid hitting class file format limits.
If you are using the latest version of Derby, then you may be somewhat
stuck, as the class file format limits are hard limits and there is no
JVM tuning parameter to get around them. The only solution is either to
express a simpler query, or modify Derby's code generation algorithms
so that we don't bump up against these class file format limitations.
There was a great discussion on this topic about 2 months ago on this
list. Dan Debrunner gave several pointers to background material with
more information in those messages. You can read them starting here:
http://www.nabble.com/Inserting-NULL-values-with-the-embedded-driver-tf2227777.html#a6191125
In my opinion, if you can reproduce your problem with the 10.2 release
of Derby, and if you can package up a small standalone test case with
your DDL and with the SQL statement that demonstrates the class file format
problem, then you should log a new request in Jira to track the problem.
thanks,
bryan
Re: Large IN clause produces server error
Posted by Robert Enyedi <ro...@intland.com>.
I'm sorry to tell you, but what a database is supposed to do is to
return data based on syntactically and semantically valid queries. This
means that even if you have an extremely stupid but valid query, it
should execute. One thing to do is to impose some well defined server
limitations, which should be well documented, but when the server
crashes randomly, there's a problem.
Regards,
Robert
derby@segel.com wrote:
>
> 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:robert.enyedi@intland.com]
> *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,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,2
> 64,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,7
> 59,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,10
> 05,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,12
> 03,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,14
> 01,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,15
> 99,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,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:robert.enyedi@intland.com]
>> 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
>>
>
>
>
>
>
>
>
RE: Large IN clause produces server error
Posted by Julian Hyde <ju...@speakeasy.net>.
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:msegel@segel.com] On Behalf Of derby@segel.com
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:robert.enyedi@intland.com]
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:robert.enyedi@intland.com]
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
RE: Large IN clause produces server error
Posted by de...@segel.com.
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:robert.enyedi@intland.com]
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:robert.enyedi@intland.com]
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
Re: Large IN clause produces server error
Posted by Robert Enyedi <ro...@intland.com>.
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,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) 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:robert.enyedi@intland.com]
>> 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
>>
>
>
>
>
RE: Large IN clause produces server error
Posted by Michael Segel <ms...@segel.com>.
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:robert.enyedi@intland.com]
> 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