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:

    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

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

    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:

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

    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

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

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

    And much more changes. Do anyone found better fix?