_createLimitSubquery generates a query with error ORA-00904

Details

Type: Bug
Status: Closed
Priority: 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

Ascending order - Click to sort in descending order

Jonathan H. Wage made changes - 02/Nov/09 11:00 PM

Field	Original Value	New Value
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; } } }	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: {code} --- 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; } } } {code}

Hide

Permalink

Jonathan H. Wage added a comment - 02/Nov/09 11:02 PM

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

Show

Jonathan H. Wage added a comment - 02/Nov/09 11:02 PM This is already fixed in all versions. 1.0, 1.1 and 1.2

Jonathan H. Wage made changes - 02/Nov/09 11:02 PM

Status	Open [ 1 ]	Resolved [ 5 ]
Resolution		Fixed [ 1 ]

Hide

Permalink

Igor D'Astolfo added a comment - 03/Nov/09 9:02 AM

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 - 03/Nov/09 9:02 AM 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

Igor D'Astolfo made changes - 03/Nov/09 9:02 AM

Resolution	Fixed [ 1 ]
Status	Resolved [ 5 ]	Reopened [ 4 ]

Hide

Permalink

Jonathan H. Wage added a comment - 03/Nov/09 5:06 PM

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 - 03/Nov/09 5:06 PM 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

Permalink

Igor D'Astolfo added a comment - 06/Nov/09 2:03 PM

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 - 06/Nov/09 2:03 PM 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.

Igor D'Astolfo made changes - 06/Nov/09 2:03 PM

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:

{code}
--- 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;
                 }
             }
         }
{code}

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:

{code}
--- 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;
                 }
             }
         }
{code}

Hide

Permalink

Jonathan H. Wage added a comment - 18/Nov/09 1:24 AM

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 - 18/Nov/09 1:24 AM 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

Permalink

Jonathan H. Wage added a comment - 18/Nov/09 1:26 AM

Ok. I understand now.

Show

Jonathan H. Wage added a comment - 18/Nov/09 1:26 AM Ok. I understand now.

Jonathan H. Wage made changes - 18/Nov/09 1:44 AM

Status	Reopened [ 4 ]	Closed [ 6 ]
Fix Version/s		1.2.0-RC1 [ 10041 ]
Resolution		Fixed [ 1 ]

This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-140, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

Assignee:

Jonathan H. Wage

Reporter:

Igor D'Astolfo

Vote (0)

Watch (0)

Dates

Created:

26/Oct/09 4:07 PM

Updated:

18/Nov/09 1:44 AM

Resolved:

18/Nov/09 1:44 AM