[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Johnathon Lee updated TEIID-2138: --------------------------------- Description: Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata. For instance: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\\_alterab__' and n.oid = relnamespace 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... was: Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata. For instance: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Johnathon Lee updated TEIID-2138: --------------------------------- Description: Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata. For instance: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace 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... was: Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata. For instance: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\\_alterab__' and n.oid = relnamespace 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Johnathon Lee updated TEIID-2138: --------------------------------- Description: 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... was: Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata. For instance: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace 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... Steps to Reproduce: 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'\_alterab__' and n.oid = relnamespace {code} 1 Record: ddl_alterable was: Querying ModeShape on a fresh 5.3 deploy. select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace 0 Records select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl_alterable' and n.oid = relnamespace 1 Record: ddl_alterable select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace 1 Record: ddl_alterable -- 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

[ 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. -- 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-2138: --------------------------------------- I should also mention that the last example {code}E'ddl\_alterab__'{code} "works" in Teiid because (like pg) we'll just drop the '\' from an unrecognized escape, which means that the pattern becomes {code}'ddl_alterab__'{code}, which is not quite the intent as it will match any character after ddl. To sum up, the root issue is with '_', which the clients recognize as a like pattern character that they want to escape and match as a character. They then are assuming some form of non-standard pg handling - the default like escape character of '\' and/or that older pg releases default to treating '\' in character strings as an escape by default - which is also against the spec. On that latter one, either the client can use an E string literal (which is why first example is using the double '\\') or possibly check the pg standard_conforming_strings setting (which defaults to true in later releases). If these are generated queries I doubt that we'll be able to influence their creation to something more ANSI compliant. The possible workaround being yet another setting similar to org.teiid.iso8601Week property to further emulate pg semantics. -- 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2138: ------------------------------------- I probably took it as default with out much further investigation. We also had crude implementation for escapes that you cleaned up, it may make more sense to turn on now. -- 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Johnathon Lee edited comment on TEIID-2138 at 8/10/12 2:00 PM: --------------------------------------------------------------- So the customer should check to see if "standard_conforming_strings" can be set to "on" in the client configuration utilizing the ODBC DSN or is this something that is set in defining the DSN itself? -- 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Johnathon Lee commented on TEIID-2138: -------------------------------------- Ok, wanted to confirm that there's nothing the client could do at this point to resolve prior to a teiid change. -- 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

[ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-2138: ---------------------------------- Summary: PG DSN for for ODBC metadata query LIKE issues (was: Use of the PG DSN for for ODBC metadata queries can result in improperly escaped SQL) Issue Type: Quality Risk (was: Bug) Affects: Documentation (Ref Guide, User Guide, etc.) -- 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