[OJB-developers] bug in sql generation

[Matthew b. <mat...@ya...>]

I think the   appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.

since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this:

(((SELECT ....

obviously those parens should go after the FROM clause. 

the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't working right) and I wanted to make sure this wasn't a symptom of another problem.

protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf)
    {
        int stmtFromPos = 0;

        if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
        {
            stmtFromPos = buf.length(); // BRJ : point after FROM
        }
        if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root))
        {
            buf.append(table.table);
            buf.append(" ");
            buf.append(table.alias);
        }
        if (!table.hasJoins())
        {
            return;
        }
        for (Iterator it = table.iterateJoins(); it.hasNext();)
        {
            Join join = (Join) it.next();
            if (joinSyntaxType == SQL92_JOIN_SYNTAX)
            {
                if (join.isOuter)
                {
                    buf.append(" LEFT OUTER JOIN ");
                }
                else
                {
                    buf.append(" INNER JOIN ");
                }
                if (join.right.hasJoins())
                {
                    buf.append("(");
                    appendTableWithJoins(join.right, where, buf);
                    buf.append(")");
                }
                else
                {
                    appendTableWithJoins(join.right, where, buf);
                }
                buf.append(" ON ");
                join.appendJoinEqualities(buf);
                if (it.hasNext())
                {
                    buf.insert(stmtFromPos, "(");
                    buf.append(")");
                }
            }
            else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
            {
                if (join.isOuter)
                {
                    buf.append(" LEFT OUTER JOIN ");
                }
                else
                {
                    buf.append(" INNER JOIN ");
                }
                buf.append(join.right.table);
                buf.append(" ");
                buf.append(join.right.alias);
                buf.append(" ON ");
                join.appendJoinEqualities(buf);
                appendTableWithJoins(join.right, where, buf);
            }
            else
            {
                buf.append(",");
                appendTableWithJoins(join.right, where, buf);
                if (where.length() > 0)
                {
                    where.append(" AND ");
                }
                join.appendJoinEqualities(where);
            }
        }
    }
The SQL being generated for my complex case is:
SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date,
A0.deactivation_date,A0.matrix_calc_version_uuid FROM 
(((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3 
ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid) 
INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid) 
ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid) 
ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid
 
 WHERE (((( ( (  A0.activation_date <= ? 
 AND  A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) 
 AND  A0.begin_effective_date <= ?) 
 AND  A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) 
 AND  A3.participant_uuid IS NULL ) AND  A4.job_uuid IS NULL ) 
 AND  A5.hierarchy_object_uuid IS NULL ) AND  A7.unit_uuid = ?
which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM.
thoughts?



---------------------------------
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup