ZF-11613: Selecting from ,,dual'' impossible under mysql.

Issue Type: Bug Created: 2011-07-27T10:12:43.000+0000 Last Updated: 2011-09-04T18:31:41.000+0000 Status: Open Fix version(s): Reporter: Artur Łabudziński (seti) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: Attachments:


  • I initiate the selection bq. $select = $db->select()->from(new Zend_Db_Expr('dual'),null);
  • then i make some stuff, and i add columns with specified selections bg. $select -> columns($array);

That $array have several '('.$another_select.') as alias'. I thought i would get sql like: select (......) as alias1, (.....) as alias2, ..... from dual, but i instead of dual i have got dual as t, and then from database: ````

If i try to use from->('dual') i have got error saying table actual_db.dual not exists....


Posted by Artur Łabudziński (seti) on 2011-07-27T10:17:02.000+0000

For now i have created empty one row max ,,dual'' table in my db.

Posted by Adam Lundrigan (adamlundrigan) on 2011-07-27T13:25:41.000+0000

Could you provide a SQL dump with the structure of your tables and a larger sample of your ZF code where you create the SELECT query?

Posted by David Fuhr (davidfuhr) on 2011-07-27T16:23:21.000+0000

The "dual" table is specific to Oracle and MySQL supports this as well for compatibility reasons:

<pre class="literal">
-- works in MySQL:

-- works only if you have a table called "dual"

-- works NOT in MySQL:

The _renderFrom() method in Zend_Db_Select already uses the dummy table for oracle automatically if no from part is set:

<pre class="literal">
         * If no table specified, use RDBMS-dependent solution
         * for table-less query.  e.g. DUAL in Oracle.
        if (empty($this->_parts[self::FROM])) {
            $this->_parts[self::FROM] = $this->_getDummyTable();

So i suggest you omit the FROM part when creating the select statement and everything should work fine for Oracle and MySQL as well.

Posted by David Fuhr (davidfuhr) on 2011-07-27T16:38:47.000+0000

Oh, i just saw, that the _getDummyTable() method does actually nothing :)

I assume it should contain something like:

<pre class="literal">
return $this->_adapter->getDummyTable();

The abstract db adapter should return an empty array (like _getDummyTable() currently does) and the oracle and oci adapters should return "DUAL".

Posted by Artur Łabudziński (seti) on 2011-07-27T17:41:26.000+0000

I provided FROM... as new Zend_Db_Expr('dual'). What i should get needs to be: ```` sql.... FROM 'dual' of sql witch is normal, but i want the 1st one to work. Without that strange aliacing something that is good, to something that cannot even validate.

Posted by Artur Łabudziński (seti) on 2011-07-27T17:45:26.000+0000

Forgot to tell.. That sql of mine (as almost any under mysql that can use dual - would work totally ok without using from at all - but that is strangely prohibited by select class when using for example: ````

PS: $db used everywhere is just: $db = $this->getAdapter();

Posted by Artur Łabudziński (seti) on 2011-09-04T18:19:18.000+0000

Fixed it by editing Zend/Db/Select.php, at line 1125 oryginally containing bq. $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName); to lines

<pre class="literal">
             if ($table['tableName'] instanceof Zend_Db_Expr)
                $tmp .= $this->_getQuotedTable($table['tableName']);
                $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);

or to single line

<pre class="literal">
                $tmp .= $this->_getQuotedTable($table['tableName'], ($table['tableName'] instanceof Zend_Db_Expr) ? null : $correlationName);

Posted by Artur Łabudziński (seti) on 2011-09-04T18:31:41.000+0000

And much more changes. Do anyone found better fix?

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.