[JBoss JIRA] (TEIID-2188) When using CONCAT2(x, y), can the source query be changed to not include the CASE statements and let Oracle do the NULL compare
4:42 a.m.
Van Halbert created TEIID-2188:
----------------------------------
Summary: When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Embedded
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE statement
to check for nulls. Can the rewritten query not include the CASE statements and just let
Oracle do the compare, because it can do it more efficiently within the CONCAT call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:07 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-2188:
----------------------------------
Component/s: Query Engine
(was: Embedded)
We can delay the rewrite until source query formation and pushdown support would need to
be added. However are you looking to use concat2 or just concat and have concat mimic
concat2 semantics?
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:47 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Van Halbert commented on TEIID-2188:
------------------------------------
Whenever concat is pushed to oracle, don't wrap with CASE statements, just pass in:
concat(nvl(g_0.f1), ''), nvl(g_0.f2, ''))
After reading oracle doc's, I've revised the pushed down based on their
recommendation to use "nvl" when calling concat.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
7 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2188:
---------------------------------------
I don't follow you. Oracle concat produces null if both values are null, so what you
are showing does not match the behavior of any concat.
Standard concat: returns null if either argument is null.
Oracle concat: returns null if both values are null, otherwise null is treated as the
empty string.
Teiid concat2: matches oracle concat.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:02 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Van Halbert commented on TEIID-2188:
------------------------------------
This is pushing down CONCAT2.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:04 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Van Halbert edited comment on TEIID-2188 at 9/6/12 8:03 AM:
------------------------------------------------------------
This is pushing down CONCAT2.
The behavior of CONCAT2 without the CASE statements.
was (Author: van.halbert):
This is pushing down CONCAT2.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:24 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2188:
---------------------------------------
Ok, so yes we can add logic to delay the concat2 rewrite to support more direct pushdown
handling. However just using concat with nvl as the pushdown is not appropriate.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:53 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Van Halbert commented on TEIID-2188:
------------------------------------
Hm, a paragraph from the oracle docs (last sentence):
Although Oracle treats zero-length character strings as nulls, concatenating a zero-length
character string with another operand always results in the other operand, so null can
result only from the concatenation of two null strings. However, this may not continue to
be true in future versions of Oracle Database. To concatenate an expression that might be
null, use the NVL function to explicitly convert the expression to a zero-length string.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:01 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2188:
---------------------------------------
Yes, that snippet confirms my previous comment stating the behavior of the various
concats.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:10 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
RH Bugzilla Integration updated TEIID-2188:
-------------------------------------------
Bugzilla Update: Perform
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:10 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
RH Bugzilla Integration updated TEIID-2188:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=855016
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:22 p.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-2188.
-----------------------------------
Fix Version/s: 8.2
Resolution: Done
Delayed the rewrite of concat2 to concat until we've reached the source. Currently
only the oracle executionfactory has support for concat2, but it looks it could be added
for pg 9.3+ as well.
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
Fix For: 8.2
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:17 a.m.
[
https://issues.jboss.org/browse/TEIID-2188?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-2188.
---------------------------------
When using CONCAT2(x, y), can the source query be changed to not
include the CASE statements and let Oracle do the NULL compare
-------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-2188
URL: https://issues.jboss.org/browse/TEIID-2188
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Affects Versions: 7.4.4
Reporter: Van Halbert
Assignee: Steven Hawkins
Fix For: 8.2
When CONCAT2(x, y) is used, the rewritten query is very verbose in adding a CASE
statement to check for nulls. Can the rewritten query not include the CASE statements and
just let Oracle do the compare, because it can do it more efficiently within the CONCAT
call?
Example:
select concat2(f1,f2) from table1
results in
SELECT c_0 FROM (SELECT CASE WHEN to_char(g_0.f1) IS NULL AND g_0.f2 IS NULL THEN NULL
ELSE concat(nvl(to_char(g_0.f1), ''), nvl(g_0.f2, '')) END AS c_0 FROM
table1 g_0)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
4416
days inactive
4491
days old
12 comments
3 participants
participants (3)
-
RH Bugzilla Integration (JIRA) -
Steven Hawkins (JIRA)
-
Van Halbert (JIRA)