db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde" <julianh...@speakeasy.net>
Subject RE: Large IN clause produces server error
Date Wed, 15 Nov 2006 22:18:17 GMT
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
    

 
 
 
  

 


Mime
View raw message