Re: [OJB-developers] bug in sql generation

[Jakob B. <jbr...@ho...>]

hi matthew,

the sql looks quite ok.=20
i first introduced stmtFromPos as an instance variable and i think oleg =
made it a local variable.=20

jakob
  ----- Original Message -----=20
  From: Matthew baird=20
  To: obj...@li...=20
  Sent: Thursday, May 30, 2002 8:19 AM
  Subject: [OJB-developers] bug in sql generation


  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.=20

  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 =3D 0;

          if (joinSyntaxType =3D=3D SQL92_JOIN_SYNTAX && table =3D=3D =
root)
          {
              stmtFromPos =3D buf.length(); // BRJ : point after FROM
          }=20

          if (!(joinSyntaxType =3D=3D SQL92_NOPAREN_JOIN_SYNTAX && table =
!=3D root))
          {
              buf.append(table.table);
              buf.append(" ");
              buf.append(table.alias);
          }=20

          if (!table.hasJoins())
          {
              return;
          }=20

          for (Iterator it =3D table.iterateJoins(); it.hasNext();)
          {
              Join join =3D (Join) it.next();
              if (joinSyntaxType =3D=3D 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);=20

                  if (it.hasNext())
                  {
                      buf.insert(stmtFromPos, "(");
                      buf.append(")");
                  }
              }
              else if (joinSyntaxType =3D=3D 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);
              }
          }
      }=20

  The SQL being generated for my complex case is:=20

  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=20
  (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN =
(statement_VAR A2 INNER JOIN PARTICIPANT A3=20
  ON A2.participant_uuid=3DA3.participant_uuid) INNER JOIN =
HIERARCHY_OBJECT A5 ON A2.organization_uuid=3DA5.hierarchy_object_uuid)=20
  INNER JOIN JOB A4 ON A2.job_uuid=3DA4.job_uuid) INNER JOIN (statement =
A6 INNER JOIN UNIT A7 ON A6.unit_uuid=3DA7.unit_uuid)=20
  ON A2.statement_uuid=3DA6.statement_uuid) ON =
A1.statement_var_uuid=3DA2.statement_var_uuid)=20
  ON A0.matrix_calc_version_uuid=3DA1.matrix_calc_version_uuid
  =20
   WHERE (((( ( (  A0.activation_date <=3D ?=20
   AND  A0.deactivation_date > ? OR A0.deactivation_date IS NULL )=20
   AND  A0.begin_effective_date <=3D ?)=20
   AND  A0.end_effective_date > ? OR A0.end_effective_date IS NULL )=20
   AND  A3.participant_uuid IS NULL ) AND  A4.job_uuid IS NULL )=20
   AND  A5.hierarchy_object_uuid IS NULL ) AND  A7.unit_uuid =3D ?=20

  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.=20

  thoughts?





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