Issues

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

Description

  • 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....

Comments

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

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?

The "dual" table is specific to Oracle and MySQL supports this as well for compatibility reasons: http://en.wikipedia.org/wiki/DUAL_table

-- works in MySQL:
SELECT NOW() FROM DUAL;

-- works only if you have a table called "dual"
SELECT NOW() FROM `DUAL`;

-- works NOT in MySQL:
SELECT NOW() FROM DUAL as t;

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

        /*
         * 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.

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

I assume it should contain something like:

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".

I provided FROM... as new Zend_Db_Expr('dual'). What i should get needs to be: ```` sql.... FROM 'dual' ...rest 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.

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();

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

             if ($table['tableName'] instanceof Zend_Db_Expr)
                $tmp .= $this->_getQuotedTable($table['tableName']);
            else
                $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);

or to single line

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

And much more changes. Do anyone found better fix?