Unexpected exception while inserting rows with query literals disabled

[Michael Diamond]

I'm getting an exception when I attempt to insert a large number of rows into a table with SET ALLOW_LITERALS NONE applied.  This is repeatable with the following code snippet:

    public void analyzeLiteralFailure() throws SQLException {

        Connection conn = DriverManager.getConnection("jdbc:h2:mem:");

        conn.createStatement().execute("SET ALLOW_LITERALS NONE");

        

        conn.prepareStatement("CREATE TABLE test (id INT)").execute();

        for(int i = 0; i < 10000; i++) {

            PreparedStatement ps = conn.prepareStatement("INSERT INTO test (id) VALUES (?)");

            ps.setInt(1, i);

            ps.executeUpdate();

        }

    }

The stack trace I see is:

org.h2.jdbc.JdbcSQLException: Literals of this kind are not allowed; SQL statement:

SELECT SELECTIVITY(ID) FROM PUBLIC.TEST LIMIT 1 SAMPLE_SIZE 1000 [90116-173]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)

at org.h2.message.DbException.get(DbException.java:171)

at org.h2.message.DbException.get(DbException.java:148)

at org.h2.message.DbException.get(DbException.java:137)

at org.h2.command.Parser.checkLiterals(Parser.java:3075)

at org.h2.command.Parser.read(Parser.java:2998)

at org.h2.command.Parser.readIf(Parser.java:2863)

at org.h2.command.Parser.parseEndOfQuery(Parser.java:1658)

at org.h2.command.Parser.parseSelectUnionExtension(Parser.java:1581)

at org.h2.command.Parser.parseSelectUnion(Parser.java:1551)

at org.h2.command.Parser.parseSelect(Parser.java:1538)

at org.h2.command.Parser.parsePrepared(Parser.java:405)

at org.h2.command.Parser.parse(Parser.java:279)

at org.h2.command.Parser.parse(Parser.java:251)

at org.h2.command.Parser.prepare(Parser.java:202)

at org.h2.engine.Session.prepare(Session.java:401)

at org.h2.engine.Session.prepare(Session.java:388)

at org.h2.command.ddl.Analyze.analyzeTable(Analyze.java:100)

at org.h2.table.RegularTable.analyzeIfRequired(RegularTable.java:423)

at org.h2.table.RegularTable.addRow(RegularTable.java:157)

at org.h2.command.dml.Insert.insertRows(Insert.java:127)

at org.h2.command.dml.Insert.update(Insert.java:86)

at org.h2.command.CommandContainer.update(CommandContainer.java:79)

at org.h2.command.Command.executeUpdate(Command.java:235)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140)

at db.H2UsageFunct.analyzeLiteralFailure(H2UsageFunct.java:128)

It appears RegularTable executes an ANALYZE command if enough rows are inserted, but uses hard coded values (LIMIT, SAMPLE_SIZE) rather than populating them with a prepared statement, causing this exception when literals are disabled.  I noticed a similar bug report here: https://groups.google.com/forum/#!msg/h2-database/xwB2jXxMqGE/t44itMbqiXgJ

[Noel Grandin]

Thanks for the test case.

This is fixed in SVN.

[Michael Diamond]

Wow, thanks for the fast turnaround, that was awesome!  Any idea when this will be in a built release?

Also, the fact that this bug hadn't been seen before makes me wonder if I'm doing bulk-inserts with SET ALLOW_LITERALS NONE incorrectly.  I'm not sure how to best insert many rows into a table without manually constructing a large query (which would fail as it contains literals).  I posted http://stackoverflow.com/questions/17985124/multi-row-insert-vs-repeatedly-calling-preparedstatement-executeupdate a few weeks ago, but didn't really get a satisfactory answer.  Could anyone weigh in on how to best bulk-insert into a DB without literals?

[Michael Diamond]

Ran into another issue related to ALLOW_LITERALS=NONE, this time in the H2 Console.  If you try to open a console connection to a database that's been opened previously with ALLOW_LITERALS=NONE, certain behavior breaks, notably the tables list fails with the stack trace below.

I understand this setting applies to all connections, not just the one that executes it (which makes sense, but definitely confused me when I first ran into this), and that it's likely fundamentally incompatible with using the Console, but I would expect the Console to a) fail more clearly or b) override the setting in order to behave correctly.

Literals of this kind are not allowed; SQL statement:

SELECT UPPER(VALUE) FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='MODE' [90116-173] 90116/90116

org.h2.jdbc.JdbcSQLException: Literals of this kind are not allowed; SQL statement:

SELECT UPPER(VALUE) FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='MODE' [90116-173] 

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:171)
    at org.h2.message.DbException.get(DbException.java:148)
    at org.h2.message.DbException.get(DbException.java:137)
    at org.h2.command.Parser.checkLiterals(Parser.java:3075)

    at org.h2.command.Parser.read(Parser.java:3041)
    at org.h2.command.Parser.readCondition(Parser.java:1984)
    at org.h2.command.Parser.readAnd(Parser.java:1876)
    at org.h2.command.Parser.readExpression(Parser.java:1868)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1825)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1707)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1550)

    at org.h2.command.Parser.parseSelect(Parser.java:1538)
    at org.h2.command.Parser.parsePrepared(Parser.java:405)
    at org.h2.command.Parser.parse(Parser.java:279)
    at org.h2.command.Parser.parse(Parser.java:251)

    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:428)
    at org.h2.engine.Session.prepareCommand(Session.java:377)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:72)
    at org.h2.server.web.DbContents.readContents(DbContents.java:111)
    at org.h2.server.web.WebApp.tables(WebApp.java:662)
    at org.h2.server.web.WebApp.process(WebApp.java:219)
    at org.h2.server.web.WebApp.processRequest(WebApp.java:164)
    at org.h2.server.web.WebThread.process(WebThread.java:138)
    at org.h2.server.web.WebThread.run(WebThread.java:94)
    at java.lang.Thread.run(Unknown Source) 

Michael

[Thomas Mueller]

Hi,

Yes, this is a bug as well. Fixed now in the trunk.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.