You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Qifan Chen (Jira)" <ji...@apache.org> on 2022/04/27 18:22:00 UTC
[jira] [Comment Edited] (IMPALA-11274) CNF Rewrite causes a regress in join node performance
[ https://issues.apache.org/jira/browse/IMPALA-11274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17528949#comment-17528949 ]
Qifan Chen edited comment on IMPALA-11274 at 4/27/22 6:21 PM:
--------------------------------------------------------------
For the following query
{code:java}
set explain_level = 3;
explain
select * from
p, a1, a4, a5
where
(
( coalesce(CAST(a1.c1 AS string), '') != '' )
OR
(
(
( upper(p.email5153) = '1' )
OR ( upper(p.email5171) = 'WJNS13@YAHOO.COM ' )
OR ( ( upper(p.email5171) LIKE '%GMAI.COM' )
AND ( coalesce(CAST(a4.customerkey AS string), '') = '' ) )
OR ( upper(p.email5171) = 'CLARIANT.COM' )
OR ( upper(p.email5171) = 'YAHOO.COM' )
OR ( upper(p.email5171) LIKE '%ELECTROMAILS.COM' )
)
AND (
( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' ) )
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms5171, 'G')) = 'G' )
)
OR ( upper(p.email5153) = '4' )
OR
( ( upper(p.email5153) = '3' )
AND ( ( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' ) )
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms5171, 'G')) = 'G' ) )
OR ( coalesce(CAST(a5.customerkey3024 AS string), '') != '' )
)
AND ( ( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' )
)
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms, 'G')) = 'G' )
;
{code}
The predicate expansion is as follows.
{code:java}
1 | | predicates: (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
2 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
3 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
4 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
5 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
6 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
7 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
8 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
9 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
10 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
11 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
12 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
13 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
14 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
15 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
16 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
17 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
18 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
19 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
20 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
21 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
22 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
23 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
24 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
25 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
26 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
27 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
28 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
29 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
30 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
31 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
32 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
33 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
34 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
35 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
36 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
37 (coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
38 (coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
39 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
40 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
41 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
42 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
43 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
44 (coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
45 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
46 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
47 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
48 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
49 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
50 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
51 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
52 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
53 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
54 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
55 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
56 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
57 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
58 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
59 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
60 (coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey302 4, '') != ''),
61 (coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
62 (coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey302 4, '') != ''),
63 (coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3 024, '') != ''),
64 (coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3 024, '') != ''),
65 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, ' ') != ''),
66 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
67 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, ' ') != ''),
68 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey302 4, '') != ''),
69 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3 024, '') != ''),
70 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, ' ') != ''),
71 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
72 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
73 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey302 4, '') != ''),
74 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3 024, '') != ''),
75 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, ' ') != ''),
76 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
77 (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper (p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
78 (coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper (p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
79 (coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customer key3024, '') != ''),
80 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
81 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
82 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email517 1) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
83 (coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.glob al5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND ( coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
84 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email517 1) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
85 (coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p. global5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') A ND (coalesce(p.email2dc, '') = '') AND (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
86 (coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce( p.global5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce(p.email2dc, '') = '') AND (coalesce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
87 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((uppe r(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce(p.email2dc, '') = '' ) AND (coalesce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') AND (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
88 (coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email517 1) = 'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((up per(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce(p.email2dc, '') = '') AND (coalesce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') AND (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != '') |
89 | predicates: (coalesce(p.email1dc, '') = ''),
90
91 (coalesce(p.email2dc, '') = ''),
92 (coalesce(p.sms3dc, '') = ''),
93 (upper(coalesce(p.client5171, 'G')) = 'G'),
94 (upper(coalesce(p.clientsms5171, 'G')) = 'G'),
95 (upper(coalesce(p.system5171, 'G')) = 'G'),
96 (upper(coalesce(p.systemsms, 'G')) = 'G'),
97 ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')),
98 ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')),
{code}
was (Author: sql_forever):
For the following query
{code:java}
set explain_level = 3;
explain
select * from
p, a1, a4, a5
where
(
( coalesce(CAST(a1.c1 AS string), '') != '' )
OR
(
(
( upper(p.email5153) = '1' )
OR ( upper(p.email5171) = 'WJNS13@YAHOO.COM ' )
OR ( ( upper(p.email5171) LIKE '%GMAI.COM' )
AND ( coalesce(CAST(a4.customerkey AS string), '') = '' ) )
OR ( upper(p.email5171) = 'CLARIANT.COM' )
OR ( upper(p.email5171) = 'YAHOO.COM' )
OR ( upper(p.email5171) LIKE '%ELECTROMAILS.COM' )
)
AND (
( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' ) )
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms5171, 'G')) = 'G' )
)
OR ( upper(p.email5153) = '4' )
OR
( ( upper(p.email5153) = '3' )
AND ( ( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' ) )
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms5171, 'G')) = 'G' ) )
OR ( coalesce(CAST(a5.customerkey3024 AS string), '') != '' )
)
AND ( ( upper(p.global5170) != 'Y' )
OR ( coalesce(CAST(p.global5170 AS string), '') = '' ) )
AND ( ( upper(p.email5170) != 'Y' )
OR ( coalesce(CAST(p.email5170 AS string), '') = '' ) )
AND ( ( upper(p.sms5171) != 'Y' )
OR ( coalesce(CAST(p.sms5171 AS string), '') = '' )
)
AND ( upper(coalesce(p.client5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.email2dc AS string), '') = '' )
AND ( coalesce(CAST(p.email1dc AS string), '') = '' )
AND ( upper(coalesce(p.system5171, 'G')) = 'G' )
AND ( upper(coalesce(p.clientsms5171, 'G')) = 'G' )
AND ( coalesce(CAST(p.sms3dc AS string), '') = '' )
AND ( upper(coalesce(p.systemsms, 'G')) = 'G' )
;
{code}
The predicate expansion is as follows.
{code:java}
| | predicates: (coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.sms3dc, '') = '') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '
') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '')
!= ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '
') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024,
'') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024,
'') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') !
= ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != '
'),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') !
= ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '
') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3024,
'') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') !
= ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '')
!= ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != '
'),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '
') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (coalesce(a5.customerkey3024,
'') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') !
= ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.system5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (
coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email1dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper(p.e
mail5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (coalesce(p.email2dc, '') = '') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper(p.e
mail5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(coalesce(p.client5171, 'G')) = 'G') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND
((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024,
'') != ''),
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = '
YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = '
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) =
'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (coalesce(p.sms3dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global51
70, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce
(p.email2dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) =
'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(coalesce(p.systemsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.glob
al5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coal
esce(p.email2dc, '') = '') AND (coalesce(p.email1dc, '') = '') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.gl
obal5170, '') = '')) AND ((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (co
alesce(p.email2dc, '') = '') AND (coalesce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (coalesce(a4.customerkey, '') = '') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) = '
YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper(p.emai
l5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce(p.email2dc, '') = '') AND (coale
sce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') AND (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != ''),
(coalesce(a1.c1, '') != '') OR (upper(p.email5153) = '1') OR (upper(p.email5171) = 'WJNS13@YAHOO.COM ') OR (upper(p.email5171) LIKE '%GMAI.COM') OR (upper(p.email5171) = 'CLARIANT.COM') OR (upper(p.email5171) =
'YAHOO.COM') OR (upper(p.email5171) LIKE '%ELECTROMAILS.COM') OR (upper(p.email5153) = '4') OR (upper(p.email5153) = '3') AND ((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')) AND ((upper(p.em
ail5170) != 'Y') OR (coalesce(p.email5170, '') = '')) AND ((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = '')) AND (upper(coalesce(p.client5171, 'G')) = 'G') AND (coalesce(p.email2dc, '') = '') AND (coa
lesce(p.email1dc, '') = '') AND (upper(coalesce(p.system5171, 'G')) = 'G') AND (upper(coalesce(p.clientsms5171, 'G')) = 'G') OR (coalesce(a5.customerkey3024, '') != '') |
| predicates: (coalesce(p.email1dc, '') = ''),
(coalesce(p.email2dc, '') = ''),
(coalesce(p.sms3dc, '') = ''),
(upper(coalesce(p.client5171, 'G')) = 'G'),
(upper(coalesce(p.clientsms5171, 'G')) = 'G'),
(upper(coalesce(p.system5171, 'G')) = 'G'),
(upper(coalesce(p.systemsms, 'G')) = 'G'),
((upper(p.email5170) != 'Y') OR (coalesce(p.email5170, '') = '')),
((upper(p.global5170) != 'Y') OR (coalesce(p.global5170, '') = '')),
((upper(p.sms5171) != 'Y') OR (coalesce(p.sms5171, '') = ''))
{code}
> CNF Rewrite causes a regress in join node performance
> -----------------------------------------------------
>
> Key: IMPALA-11274
> URL: https://issues.apache.org/jira/browse/IMPALA-11274
> Project: IMPALA
> Issue Type: Bug
> Components: Frontend
> Reporter: Qifan Chen
> Assignee: Qifan Chen
> Priority: Major
>
> It appears that cnf rewrite can generate more predicates and presumably cause the same query to execute slower.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org