[DERBY-5981] Derby INSERT Eats Stack Space, Causes java.lang.StackOverflowError

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 10.9.1.0
• Component/s: SQL
• Environment: Ubuntu Linux 12.04.1 64-bit, Sun Java 1.7.0_09, Derby 10.9.1.0
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Tom Deering
• Created: 04/Nov/12 19:40
• Updated: 15/Nov/13 08:15
• Resolved: 05/Nov/12 11:03

Description

Apache Derby 10.9.1.0 causes a java.lang.StackOverflowError with large INSERT statements. Here, "large" means 5000+ rows. I am using Derby in my Java application with the EmbeddedDriver in the following way:

Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection connection = DriverManager.getConnection("jdbc:derby:<db path>;create=true;upgrade=true")

String insertString = buildSQLInsertString();
Statement stmt = connection.createStatement();
stmt.execute(insertString); // StackOverflowError

I have tried calling this in different ways, including with PreparedStatement. I have also tried increasing derby.storage.pageSize, to no effect. If I increase the VM argument -Xss to something insanely big, then after ~30 seconds I get:

SQL Error: 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.

My code works fine for smallish inserts, but overflows the stack for anything modestly large. I can't tell if Derby is using an unreasonably large amount of stack space with INSERT, or if the size of what I am inserting is unreasonable.

I'm a newcomer to database development and also to Apache Derby, so suggestions for details to make this bug report more useful would be welcome.

EDIT: I updated my code to insert in chunks of at most 1000 rows at a time. This seems to be a viable workaround... but I'm still interested in inserts using a single statement and wondering if there is a bug with Derby's INSERT.

Issue Links

• duplicates: DERBY-1735 Large number of rows in a VALUES clause (e.g. for an INSERT) can result in a StackOverflowError