Bug 180407
| Summary: | Numeric columns fail in WHERE clause when using tablename qualifier | ||
|---|---|---|---|
| Product: | Red Hat Enterprise Linux 4 | Reporter: | Stephen Cuppett <steve> |
| Component: | postgresql | Assignee: | Tom Lane <tgl> |
| Status: | CLOSED NOTABUG | QA Contact: | David Lawrence <dkl> |
| Severity: | medium | Docs Contact: | |
| Priority: | medium | ||
| Version: | 4.0 | CC: | hhorak |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | All | ||
| OS: | Linux | ||
| URL: | http://pear.php.net/bugs/bug.php?id=6694 | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2006-02-08 06:38:30 UTC | Type: | --- |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
USER is a reserved word according to both the SQL standard and the Postgres grammar. You can use it as a column name only if you always surround it in double quotes. The fact that the 8.1 grammar lets you use it as the second name in a qualified identifier without quotes is a bit interesting, but it does not make 7.4's behavior a bug ... and in fact you'd be pretty foolish to assume that releases after 8.1 will still let you do that. (The example without the qualified name isn't doing what you think at all ... it's invoking the CURRENT_USER function.) Bottom line: choose a different name for your column, or get used to using double quotes. |
Description of problem: SQL query problem originally identified using PEAR DB_DataObject library; however, library can be removed from equation and same error for valid SQL query occurs utilizing command line tools. Latest version of PostgreSQL from postgresql.org does not have this problem; however, fix is needed for enterprise customers not willing to deviate and go off-distro. Assuming the following table definition: CREATE TABLE owners ( id bigserial NOT NULL, "user" bigint NOT NULL, "name" varchar(25) NOT NULL ); The following query: SELECT * FROM owners WHERE ( owners.user = 1 ) produces the following SQL error: ERROR: syntax error at or near "user" at character 39 While the following query: SELECT * FROM owners WHERE (user = 1) does not. Also, queries against the varchar do not produce errors: SELECT * FROM owners WHERE ( owners.name = 'steve' ) would not produce an error. Quotes around the number and removing/adding the parentheses have no effect on the error message. The problem seems to stem from the inclusion of the owners. qualifier on numeric types, not any other factor. The inconsistent behavior of the string identified the problem. Version-Release number of selected component (if applicable): PostgreSQL 7.4.8 from distribution/updates has this problem. PostgreSQL 8.1.2 (latest) built from SRPM does not How reproducible: Extremely. Steps to Reproduce: See Description section above. Actual results: See error up in the description. Expected results: Should be considered valid query as is the case in latest PostgreSQL 8.1.2 from postgresql.org Additional info: Originally reported on PEAR web-site: http://pear.php.net/bugs/bug.php?id=6694