9:20 a.m.
[
https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin...
]
Steven Hawkins edited comment on TEIID-2138 at 8/10/12 10:19 AM:
-----------------------------------------------------------------
Was there something in the pg escape reference that you thought seemed significant? When
in doubt just check the value using "SELECT E'...'"
All three of your examples are returning the correct results (according to Teiid).
{code}E'ddl\\_alterable'{code} means {code}'ddl\_alterable'{code} which
without specifying the like escape character must match something with 'ddl',
followed by '\', followed by any character, followed by 'alterable'
That is inline with the sql spec, but on some platforms they default to a like escape
character of '\', which changes the meaning of the first match pattern.
Checking with pg, they do indeed default their like escape to '\' (which is not
conforming, but common). The best resolution is to explicitly specify the escape
character e.g. 'x' like ... escape '\'. However if these are clients that
cannot change their sql, then we'll have to offer yet another pg emulation option.
was (Author: shawkins):
Was there something in the pg escape reference that you thought seemed significant?
When in doubt just check the value using "SELECT E'...'"
All three of your examples are returning the correct results (according to Teiid).
E'ddl\\_alterable' means 'ddl\_alterable' which without specifying the
like escape character must match something with 'ddl', followed by '\',
followed by any character, followed by 'alterable'
That is inline with the sql spec, but on some platforms they default to a like escape
character of '\', which changes the meaning of the first match pattern.
Checking with pg, they do indeed default their like escape to '\' (which is not
conforming, but common). The best resolution is to explicitly specify the escape
character e.g. 'x' like ... escape '\'. However if these are clients that
cannot change their sql, then we'll have to offer yet another pg emulation option.
Use of the PG DSN for for ODBC metadata queries can result in
improperly escaped SQL
------------------------------------------------------------------------------------
Key: TEIID-2138
URL: https://issues.jboss.org/browse/TEIID-2138
Project: Teiid
Issue Type: Bug
Components: ODBC, Query Engine
Affects Versions: 7.7
Reporter: Johnathon Lee
Assignee: Steven Hawkins
Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying
metadata.
For instance:
Querying ModeShape on a fresh 5.3 deploy.
{code}
select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like
E'ddl\\_alterable' and n.oid = relnamespace
{code}
0 Records
{code}
select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like
E'ddl_alterable' and n.oid = relnamespace
{code}
1 Record: ddl_alterable
{code}
select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like
E'ddl\_alterab__' and n.oid = relnamespace
{code}
1 Record: ddl_alterable
Looking at [1] per [2]. It seems proper escaping of the E'literal' syntax is not
always being performed. The Third example above shows that a wildcard is being escaped
and happens to match to a literal value it expects.
[1]
PostgreSQL also accepts "escape" string constants, which are an extension to
the SQL standard. An escape string constant is specified by writing the letter E (upper or
lower case) just before the opening single quote, e.g. E'foo'. (When continuing an
escape string constant across lines, write E only before the first opening quote.) Within
an escape string, a backslash character (\) begins a C-like backslash escape sequence, in
which the combination of backslash and following character(s) represents a special byte
value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t
is a tab. Also supported are \digits, where digits represents an octal byte value, and
\xhexdigits, where hexdigits represents a hexadecimal byte value. (It is your
responsibility that the byte sequences you create are valid characters in the server
character set encoding.) Any other character following a backslash is taken literally.
Thus, to include a backslash character, write two backslashes (\\). Also, a single quote
can be included in an escape string by writing \', in addition to the normal way of
''.
[2]
http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQ...
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira