ZF-884: SQLite: associative array result set contains correlation names in keys

Issue Type: Improvement Created: 2007-02-08T18:08:41.000+0000 Last Updated: 2013-03-14T09:32:16.000+0000 Status: Closed Fix version(s): - 1.0.0 (30/Jun/07)

Reporter: Bill Karwin (bkarwin) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:


SQLite is unlike other PDO drivers in that when the result set is returned from a SELECT statement as an associative array of columns => values, and the query contained correlation names, the correlation names are included in the array keys:

<pre class="highlight">
SELECT f.column1 FROM table1 AS f

Returns a result set where the key is "f.column1" whereas in other RDBMS's the key is "column1".

ADODB solves this by creating a distinct driver called "sqlitepo" that strips the leading prefix from the array key during the fetch operation.…

We need to do something similar, by extending the following functions in Zend_Db_Adapter_Pdo_Sqlite: - fetchAssoc() - fetchAll() when fetch mode is FETCH_ASSOC


Posted by Bill Karwin (bkarwin) on 2007-05-02T12:18:56.000+0000

SQLite supports pragma statements to control this behavior. See

For example, the following statements should help:

<pre class="highlight">
PRAGMA full_column_names=0
PRAGMA short_column_names=1

However, there are bug reports that these pragmas don't have the desired effect. And in fact that's what I'm seeing too in the unit tests.

I'm going to implement the pragma statements in the _connect() method of the Pdo_Sqlite adapter class, knowing that at least in the current version of SQLite, it does not work. Perhaps in a future version, it will be fixed.

I'm adding unit tests to tests/Zend/Db/Adapter/Pdo/SqliteTest.php for the cases that fail because they insist on returning result sets with keys like "table"."column".

We can't strip the table portion out of the keys as they do in ADODB, because this is a PDO Adapter. The result set is returned in a PDOStatement, not a Zend_Db_Statement.

Posted by Bill Karwin (bkarwin) on 2007-05-02T12:20:40.000+0000

Resolved insofar as we can resolve it, in revision 4667.

Posted by Bill Karwin (bkarwin) on 2007-05-25T11:37:20.000+0000

With ZF-1425, we do have a Zend_Db_Statement_Pdo class. In theory, we could now strip correlation names from the keys in result sets, if the driver's pdoType is 'sqlite'.

Posted by Bill Karwin (bkarwin) on 2007-06-28T21:16:15.000+0000

After upgrading to PHP 5.2.3 the PRAGMA commands are now working. So I presume that a more recent version of the PDO_SQLite driver is working better with respect to PRAGMA.

Posted by John Coggeshall (coogle) on 2009-06-22T12:19:59.000+0000

This bug still exists in the latest version of Zend Core using the latest version of Zend Framework (HEAD)...

It looks like SQLite has gone back and forth on this issue, where different versions of the library behave differently to these PRAGMAs -- we need to figure out which versions do what and correct it.…

As far as I can tell if you simply remove this line from the Pdo_Sqlite::_connect() method it works, at least for ZC:

$retval = $this->_connection->exec('PRAGMA short_column_names=1');

Based on the bug reports I read from we should be able to simply execute full_column_names=0 and achieve the result we are looking for... but I'm sure there is a BC issue here too.

Posted by Dolf Schimmel (Freeaqingme) (freak) on 2009-09-24T17:44:33.000+0000

I removed the fix version as for we obviously won't make fix version 1.0.0 (or is anybody in the house who can actually go back in time and fix the issue? If so, I got a few other ones laying around that I'd prefer having fixed a long time ago :D)

Posted by Adam Lundrigan (adamlundrigan) on 2012-02-26T18:50:03.000+0000

Does this issue still exist? If so, could [~coogle]'s suggestion (PRAGMA full_column_names=0) be safely implemented to resolve this issue?

Posted by Frank Brückner (frosch) on 2013-03-14T09:31:44.000+0000

No response for over a year. I close this issue.

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.