Issue Type: Bug Created: 2008-06-11T13:54:31.000+0000 Last Updated: 2012-08-31T09:07:49.000+0000 Status: Open Fix version(s): Reporter: David Rogers (al_the_x) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db
Related issues: Attachments:
The logic for quoting fields in Zend_Db_Adapter_Abstract::quote() is insufficient to address fields of type ENUM that employ numeric values, even when the explicit data type is passed as the second argument. For example, a field in SQL with type "ENUM('1','0','-1')" should always quote values, particularly numeric values, before assembling a query, but this never occurs for numeric values, resulting with inaccurate queries. Casting the value of the field to string within PHP is a useful workaround for this issue, as Zend_Db_Adapter_Abstract no longer treats it as a numeric value.
Posted by Ashley Kitson (akzincdig) on 2008-06-28T08:59:12.000+0000
Zend_Db doesn't appear to properly handle enumerated types at all!
In a call to db->update($table,$data,$where) any enumerated values are not written to the database even though the $data array contains values for the enumerated type fields. (the data values have been previously passed through db->quote() )
Same holds true for db->insert(), no enumerated types are written to the database
Database = MySql Driver/Adapter = Pdo_Mysql
Posted by David Rogers (al_the_x) on 2008-06-28T09:09:02.000+0000
You're right to include your comment on this issue, then... MySQL silently ignores values for fields that it doesn't understand or don't match its field definitions... Try setting a too-long value to a VARCHAR field to see what I'm talking about...
Does this only affect ENUM's that contain numeric values for you? I don't notice the same behavior with TINYINT. I have a patch to extract and submit, if it will be reviewed.
Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:10:28.000+0000
Additional note. Actually a blank value is written to the database for an enumerated value. Create a table with a enum value that defaults to one of its allowed values. Insert a record to the table, without setting a value for the enum field. It will default to whatever you set it on the database. Now update the record, this time passing a value for the enum field. It will get overwritten with a blank.
Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:14:56.000+0000
David said "Does this only affect ENUM's that contain numeric values for you"
I tend not to use numeric values for enums so don't experience that particular issue. I use string values e.g.
CREATE TABLE IF NOT EXISTS
pid int(11) NOT NULL auto_increment,
uVal int(11) NOT NULL,
strVal varchar(30) default NULL,
dateVal date default NULL,
floatVal float default NULL,
enumVal enum('one','two') default NULL,
rowSts enum('active','suspended','defunct') NOT NULL default 'active' COMMENT 'Row Status',
rowUid int(11) NOT NULL default '0' COMMENT 'Last Edited By',
rowDt timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Edit Datetime', PRIMARY KEY (
pid), UNIQUE KEY
uVal) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:21:23.000+0000
A little further delving seems to reveal that the Zend layer is doing everything you would expect. It properly puts quotes around an enum value. It is when the pdo layer is called to do the update that the value disappears. So it my be PDO, not Zend_Db at fault. Can't debug PDO in Zend Studio, so can get no further :-(
Posted by David Rogers (al_the_x) on 2008-06-28T09:57:16.000+0000
We haven't had the issue you're describing with string values and ENUM fields at all, Ashley, only those fields that contain a numeric ENUM value. Our solution has just been to cast the number to a string value, ie :
<pre class="highlight"> -- Sample Schema in Database 'test' CREATE TABLE IF NOT EXISTS 'enum_test' ( 'one_or_zero' ENUM('1','0') DEFAULT '1', 'true_or_false' ENUM('true','false) DEFAULT 'false', ) ENGINE=InnoDB DEFAULT CHARSET=utf8-bin ; INSERT INTO 'enum_test' VALUES ( '1', 'true' ), ( '1', 'false' ), ( '0', 'true' ), ( '1', 'false' ); <pre class="highlight"> $Adapter = Zend_Db::factory('Pdo_Mysql', array('host' => 'localhost', 'user' => 'root', 'dbname' => 'test'); // Expected - integer: 2 Zend_Debug::dump( $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)')) ->where('one_or_zero = ?', 1) ->query()->fetchColumn() ); // Actual - integer: 0 // Expected - integer: 2 Zend_Debug::dump( $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)')) ->where('one_or_zero = ?', (string) 1) ->query()->fetchColumn() ); // Actual - integer: 2
What you're describing - correct me if wrong - is something like the following:
<pre class="highlight"> // Same as above... // Expected - integer 1 (number of rows affected) Zend_Debug::dump( $Adapter->insert(array('true_or_false' => 'true')) ); // Actual - integer 1 // Expected - integer 3 (2 originals plus newly inserted value) Zend_Debug::dump( $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)')) ->where('true_or_false = ?', 'true') ->query()->fetchColumn() ); // Actual - integer 0 (query got messed up somewhere...!)
What's version of PHP you're using, as this may be the culprit instead.
Posted by Ashley Kitson (akzincdig) on 2008-06-28T10:06:24.000+0000
I had a spare minute so I checked to see if anything different happened by changing the adapter to Mysqli. It didn't - still doen't work.
However just to prove it isn't MySql, I ran the following bare bones mysql query:
$db = mysql_connect('localhost','MyDb','MyDb'); $sql = "update syst_test set enumVal = 'two' where pid = 10"; $ret = mysql_db_query('MyDb',$sql,$db);
That works just fine, the enum value gets updated.
So it's not SQL. If changing adapters proves anything, its not the adapter. Ergo it's Zend_Db.
Posted by Ashley Kitson (akzincdig) on 2008-06-28T10:13:32.000+0000
I'm using PHP 5.2.3
"$Adapter->insert(array('true_or_false' => 'true'))" Yep, that's about right
(got t go now - will catch up 2morrow.)
Posted by Wil Sinclair (wil) on 2008-12-04T13:17:30.000+0000
Reassigning to Ralph since he's the new maintainer of Zend_Db
Posted by David Rogers (al_the_x) on 2009-03-02T12:21:52.000+0000
Can I just submit a patch for this issue?
Posted by Ralph Schindler (ralph) on 2009-12-21T12:55:19.000+0000
Yes, I would like to see a patch- do you have one that addresses this issue?
Posted by David Rogers (al_the_x) on 2010-04-01T13:51:27.000+0000
I have a patch for this issue, but as there are no tests for the Abstract class, I haven't run the tests on every driver. With some direction there, I could do so, however. Please advise. Thanks.
Posted by Ralph Schindler (ralph) on 2011-02-17T15:32:24.000+0000
Can you attache the issue to this ticket?
Posted by David Rogers (al_the_x) on 2011-02-17T16:58:48.000+0000
I don't quite follow, Ralph. What do you mean by "attach the issue to this ticket"...?
Posted by Ralph Schindler (ralph) on 2011-02-17T17:34:11.000+0000
Ha, sorry. I mean patch. You said you had a patch for this issue.
Posted by Leopold Chmielewski (poldekch) on 2011-03-07T06:00:16.000+0000
David, can you post a patch for this issue, I urgently need it...
Posted by David Rogers (al_the_x) on 2011-03-07T09:11:11.000+0000
I'll try to dig that up and send it your way. I think that I isolated the issue to Zend_Db_Table when I last worked on this, since only Table instances have the metadata about the column to know how to coerce the values. I originally tried to mod Zend_Db_Adapter_Mysql, but without the metadata... Anyway. I'll put a patch together this week.
Posted by Kim Blomqvist (kblomqvist) on 2011-03-28T19:18:21.000+0000
Any progress on the patch?
Posted by Stephane Mennesson (smennesson) on 2011-04-26T15:32:58.000+0000
Hello I'm experiencing the same problem with Zend 1.11.4 A temporary solution for users that can't wait for the patch is to use Zend_Db_Expr: $Adapter->insert(array('true_or_false' => new Zend_Db_Expr("'true'")))
Have you found an issue?
See the Overview section for more details.