Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.5.0b4

Bug #14828 unixtimestamp() generates invalid SQL for non-constant argument
Submitted: 2008-10-19 09:28 UTC Modified: 2008-11-09 14:47 UTC
From: hschletz Assigned: quipo
Status: Closed Package: MDB2_Driver_pgsql (version 1.5.0b1)
PHP Version: Irrelevant OS:
Roadmaps: (Not assigned)    
Subscription  


 [2008-10-19 09:28 UTC] hschletz (Holger Schletz)
Description: ------------ The current implementation of unixtimestamp() only works with literal timestamp values (i.e. a constant value encapsulated in single quotes). If the argument is a non-constant expression (like the name of a timestamp column), the generated SQL code is invalid. The attached patch adds the TIMESTAMP prefix only if the argument looks like a constant (i.e. it begins and ends with a single quote). Note that this assumption might be wrong if the argument is a more complex expression that just happens to begin and end with the quotes. Encapsulating the expression in parentheseses should help in this case.

Comments

 [2008-10-25 01:38 UTC] doconnor (Daniel O'Connor)
Any chance of a test script to show the broken behaviour? At the moment the docblock says: "return string to call a function to get the unix timestamp from a iso timestamp"; so I can't tell at a glance if its reasonable to expect you can pass in column names or not.
 [2008-10-26 13:37 UTC] hschletz (Holger Schletz)
Assume we want an expression that gives the UNIX timestamp of the column "foo". We call unixtimestamp('foo') and get: EXTRACT(EPOCH FROM DATE_TRUNC('seconds', TIMESTAMP foo)) This will give a syntax error because the "<type> <value>" syntax only allows literal values. I have updated the patch so that it no longer guesses the type of expression. Instead it uses the "CAST (<value> AS <type>)" syntax which is equivalent to the above except that it allows <value> to be an arbitrary expression (see http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS-GENERIC). This resolves to: EXTRACT(EPOCH FROM DATE_TRUNC('seconds', CAST ((foo) AS TIMESTAMP))) You are right about the docblock description. On the other hand, limiting the argument to literal values would make this function fairly useless because the same could be achieved at PHP level via mktime(), although unixtimestamp() might be more convenient in some cases. I had a look at the unixtimestamp() implementations in the other MDB2 drivers and they all seem to allow arbitrary expressions (tested and confirmed with MySQL and SQLite). Either this is an accidental feature or the "iso timestamp" is a documentation bug.
 [2008-11-09 14:47 UTC] quipo (Lorenzo Alberton)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.