Doctrine 1
  1. Doctrine 1
  2. DC-140

_createLimitSubquery generates a query with error ORA-00904

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.4
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Connection
    • Labels:
      None
    • Environment:
      Oracle database 10 or higher

      Description

      If I create a limited query with Oracle sometimes it generates an invalid query, giving ORA-00904 errror.

      A simple DQL query like this:
      $q = Doctrine_Query::create()
      ->select("$i.nome, $s.denominazione")
      ->from("Istituto $i")
      ->leftJoin("$i.Sedi $s");

      creates this intermediate sql:
      SELECT
      "i"."id" AS "i__id",
      "i"."nome" AS "i__nome",
      "s"."id" AS "s__id",
      "s"."denominazione" AS "s__denominazione"
      FROM "istituto" "i"
      LEFT JOIN "sede" "s" ON "i"."id" = "s"."id_istituto"

      that passed to Doctrine_Pager generates this query:
      SELECT "i"."id" AS "i__id",
      "i"."nome" AS "i__nome",
      "s"."id" AS "s__id",
      "s"."denominazione" AS "s__denominazione"
      FROM "istituto" "i"
      LEFT JOIN "sede"
      "s" ON "i"."id" = "s"."id_istituto"

      WHERE "i"."id" IN (SELECT a."id" FROM (SELECT DISTINCT "i2"."id" FROM "istituto" "i") a WHERE ROWNUM <= 10)

      The problem is in "i2" referenced in the inner SELECT statement. This inner query hasn't been correctly parsed, while the column has been aliased, the table name has an incorrect alias.

      The problem is that the injected sql code necessary to pagination has some space missing inside the parenthesis, thus the tokenizer misses to alias the table name.
      Here it follows a simple patch:

      --- Oracle.php  2009-10-26 17:04:17.000000000 +0100
      +++ Oracle-orig.php     2009-09-25 19:12:10.000000000 +0200
      @@ -103,13 +103,13 @@
                       $column = $column === null ? '*' : $this->quoteIdentifier($column);
                       if ($offset > 0) {
                           $min = $offset + 1;
      +                    $query = 'SELECT b.'.$column.' FROM ( '.
      +                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ( '
      +                                   . $query . ' ) a '.
      +                              ' ) b '.
      -                    $query = 'SELECT b.'.$column.' FROM ('.
      -                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
      -                                   . $query . ') a '.
      -                              ') b '.
                                     'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
                       } else {
      +                    $query = 'SELECT a.'.$column.' FROM ( ' . $query .' ) a WHERE ROWNUM <= ' . $max;
      -                    $query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
                       }
                   }
               }
      

        Activity

        Hide
        Jonathan H. Wage added a comment -

        This is already fixed in all versions. 1.0, 1.1 and 1.2

        Show
        Jonathan H. Wage added a comment - This is already fixed in all versions. 1.0, 1.1 and 1.2
        Hide
        Igor D'Astolfo added a comment -

        Sorry, maybe I didn't explain well the bug, but it still exist in version 1.1.4 and I think also in 1.2.0
        Maybe you were thinking to a previous bug that gave ORA-00904, but this is a different one.

        It shows up only when using _createLimitSubquery, the same query without limit works fine. In the first post I described the problem, I traced the Doctrine_Query::parseSelect function, when it parses a query modified by the _createLimitSubquery it aliases the field name but not the table name in the inner subquery:

        SELECT "i"."id" AS "i__id",
        "i"."nome" AS "i__nome",
        "s"."id" AS "s__id",
        "s"."denominazione" AS "s__denominazione"
        FROM "istituto" "i"
        LEFT JOIN "sede"
        "s" ON "i"."id" = "s"."id_istituto"
        WHERE "i"."id" IN (SELECT a."id" FROM (SELECT DISTINCT "i2"."id" FROM "istituto" "i" ) a WHERE ROWNUM <= 10)

        if you look in the subuery (the last line), you'll see that the field name is "i2"."id", but the table name is "i" (FROM "istituto" "i" means FROM "istituto" AS "i"), so Oracle gives error since the query references an "i2" table that isn't defined anywhere.

        I also found why Doctrine_Query::parseSelect doesn't parse correctly the table alias, it's because the tokenization does not split the parenthesis after the table name, so the table name pass unchanged. My patch adds a blank after the end of the subquery in the Doctrine_Connection_Oracle::_createLimitSubquery, so the parenthesis does not interfere anymore with the parseSelect.

        Regards

        Show
        Igor D'Astolfo added a comment - Sorry, maybe I didn't explain well the bug, but it still exist in version 1.1.4 and I think also in 1.2.0 Maybe you were thinking to a previous bug that gave ORA-00904, but this is a different one. It shows up only when using _createLimitSubquery, the same query without limit works fine. In the first post I described the problem, I traced the Doctrine_Query::parseSelect function, when it parses a query modified by the _createLimitSubquery it aliases the field name but not the table name in the inner subquery: SELECT "i"."id" AS "i__id", "i"."nome" AS "i__nome", "s"."id" AS "s__id", "s"."denominazione" AS "s__denominazione" FROM "istituto" "i" LEFT JOIN "sede" "s" ON "i"."id" = "s"."id_istituto" WHERE "i"."id" IN (SELECT a."id" FROM (SELECT DISTINCT "i2"."id" FROM "istituto" "i" ) a WHERE ROWNUM <= 10) if you look in the subuery (the last line), you'll see that the field name is "i2"."id", but the table name is "i" (FROM "istituto" "i" means FROM "istituto" AS "i"), so Oracle gives error since the query references an "i2" table that isn't defined anywhere. I also found why Doctrine_Query::parseSelect doesn't parse correctly the table alias, it's because the tokenization does not split the parenthesis after the table name, so the table name pass unchanged. My patch adds a blank after the end of the subquery in the Doctrine_Connection_Oracle::_createLimitSubquery, so the parenthesis does not interfere anymore with the parseSelect. Regards
        Hide
        Jonathan H. Wage added a comment -

        I understand, but what I am telling you is that your patch is already applied. When I look at the code in 1.0, 1.1, and 1.2. The changes you've made in your patch already exist. So someone must have fixed this bug already. Please make sure your Doctrine libs are up to date.

        Show
        Jonathan H. Wage added a comment - I understand, but what I am telling you is that your patch is already applied. When I look at the code in 1.0, 1.1, and 1.2. The changes you've made in your patch already exist. So someone must have fixed this bug already. Please make sure your Doctrine libs are up to date.
        Hide
        Igor D'Astolfo added a comment -

        Sorry... I made a mistake with the diff, inverted the original and the patched version :|
        I updated the patch, now you could apply it correctly.

        Show
        Igor D'Astolfo added a comment - Sorry... I made a mistake with the diff, inverted the original and the patched version :| I updated the patch, now you could apply it correctly.
        Hide
        Jonathan H. Wage added a comment -

        Ok. Now I see the patch, but I don't understand. This is the SQL that is being passed to Oracle. Oracle has a problem without the extra spacing that you've added?

        Show
        Jonathan H. Wage added a comment - Ok. Now I see the patch, but I don't understand. This is the SQL that is being passed to Oracle. Oracle has a problem without the extra spacing that you've added?
        Hide
        Jonathan H. Wage added a comment -

        Ok. I understand now.

        Show
        Jonathan H. Wage added a comment - Ok. I understand now.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Igor D'Astolfo
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: