Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#508 Formatter fails for insert script with multiple subselects

2.5
closed-fixed
Rob Manning
Core (461)
5
2007-05-04
2007-04-13
Anonymous
No

Tested in both 2.3 and 2.5, I notice that the formatter (CTRL+ALT+F) is not able to process an insert statement that uses multiple select statements to provide values.

The error message for the smallest example that reproduces the error is below:
[quote]
Error processing Format SQL command: java.lang.IllegalStateException: Reformat failed, normalized Strings differ
insert into table_name ( SELECT_COL_1 , SELECT_COL2 ) values ( ( select 1 ) , ( select 2 ) )
insert into table_name ( SELECT_COL_1 , SELECT_COL2 ) VALUES ( ( select 1 ) , ) select 2 ) )
------------------------------------------------------------------------------^
[/quote]

Discussion

  • Rob Manning
    Rob Manning
    2007-04-18

    Logged In: YES
    user_id=1287991
    Originator: NO

    "select 1" isn't a valid SQL statement. So I'm guessing your original SQL statement isn't shown above. What was it?

    Rob

     
  • Logged In: NO

    Note: "Select 1" *is* a legal statement on Microsoft SQL Server databases.
    The equivalent on IBM DB2 would be "select 1 from sysibm.sysdummy1"; on Oracle you would use "select 1 from dual".
    Apparently we are both right, since the insert fails on MSSQL ("Subqueries are not allowed in this context. Only scalar expressions are allowed").
    I found the problem when using DB2, but I thought I would create the smallest example possible, using MSSQL syntax.
    I don't know the exact original statement where I found the problem.
    Using the DB2 syntax, the problem occurs as well, as can be seen in
    a real-world example where the formatter fails on the insert statement, but executes without a problem on DB2:
    create table MY_TEST_TABLE (
    SELECT_COL_1 INTEGER,
    SELECT_COL_2 INTEGER
    );
    insert into MY_TEST_TABLE ( SELECT_COL_1 , SELECT_COL_2 ) values ( ( select 1 from sysibm.sysdummy1 ) , ( select 1 from sysibm.sysdummy1 ) );

    Error processing Format SQL command: java.lang.IllegalStateException: Reformat failed, normalized Strings differ
    insert into MY_TEST_TABLE ( SELECT_COL_1 , SELECT_COL_2 ) values ( ( select 1 from sysibm.sysdummy1 ) , ( select 1 from sysibm.sysdummy1 ) ) ;
    insert into MY_TEST_TABLE ( SELECT_COL_1 , SELECT_COL_2 ) VALUES ( ( select 1 from sysibm.sysdummy1 ) , ) select 1 from sysibm.sysdummy1 ) ) ;
    --------------------------------------------------------------------------------------------------------^

     
  • Rob Manning
    Rob Manning
    2007-05-02

    Logged In: YES
    user_id=1287991
    Originator: NO

    When I step through the code with the debugger I can narrow the problem to CodeReformator.reformatInsert(Vector).

    variable values from debugger:

    insertList = [SELECT_COL_1 , SELECT_COL_2]
    valuesList = [( select 1 from sysibm.sysdummy1 ) , ]
    behindInsert = [select, 1, from sysibm.sysdummy1, ), ), ;]
    statementBegin = insert into MY_TEST_TABLE

    It looks like the second value (select 1 from sysibm.sysdummy1) didn't make it into the valuesList for some reason. I need to look at the code a bit more to gain an understanding, but this is what I see at the moment.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-05-04

    Logged In: YES
    user_id=1287991
    Originator: NO

    The fix is in CVS. CodeReformator.trySplit() was returning empty strings in the list of elements which messed up later string processing.

     
  • Rob Manning
    Rob Manning
    2007-05-04

    • labels: 336290 --> Core
    • status: open --> closed-fixed