Re: Optimizer executes inperformant subquery first
Posted by: Martin Kirchner
Date: December 18, 2013 03:04AM
Date: December 18, 2013 03:04AM
Hi Øystein,
I will open a bug report and attach a dump.
Øystein Grøvlen Wrote:
-------------------------------------------------------
> Hi Martin,
>
> It seems to me that there must be some differences
> in the statistics used by the query optimizer on
> your two systems. The output of SHOW INDEX should
> show the statistics that the optimization is based
> on.
>
> One reason for differences in statistics may be
> that one or both systems is using the traditional
> transient statistics that will be recomputed on
> each start-up. MySQL 5.6 introduced persistent
> statistics which will be more stable.
Persistent statistics are enabled on both systems:
[mysql]>show global variables like 'innodb_stat%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
7 rows in set (0.00 sec)
> Is any of the databases upgraded from 5.5 or
> earlier, and if so, did you run the upgrade
> script?
The upgrade happened via mysqldump and restore.
>
> To check if persistent statistics is in use, check
> if the tables mysql.innodb_table_stats and
> mysql.innodb_index_stats contain information about
> this table.
Statistics on TEST:
[mysql]>select * from innodb_table_stats where database_name = 'abc' and table_name in ('address', 'addressorel', 'selectionvaluesrelation')\G
*************************** 1. row ***************************
database_name: abc
table_name: address
last_update: 2013-12-06 13:28:21
n_rows: 295233
clustered_index_size: 16256
sum_of_other_index_sizes: 26762
*************************** 2. row ***************************
database_name: abc
table_name: addressorel
last_update: 2013-12-06 13:29:14
n_rows: 667278
clustered_index_size: 4521
sum_of_other_index_sizes: 14908
*************************** 3. row ***************************
database_name: abc
table_name: selectionvaluesrelation
last_update: 2013-12-06 13:30:37
n_rows: 2169873
clustered_index_size: 14328
sum_of_other_index_sizes: 21115
3 rows in set (0.00 sec)
Statistics on LIVE:
[mysql]>select * from innodb_table_stats where database_name = 'abc' and table_name in ('address', 'addressorel', 'selectionvaluesrelation')\G
*************************** 1. row ***************************
database_name: abc
table_name: address
last_update: 2013-12-09 04:11:12
n_rows: 304138
clustered_index_size: 17088
sum_of_other_index_sizes: 27456
*************************** 2. row ***************************
database_name: abc
table_name: addressorel
last_update: 2013-11-22 09:21:40
n_rows: 663491
clustered_index_size: 4521
sum_of_other_index_sizes: 14907
*************************** 3. row ***************************
database_name: abc
table_name: selectionvaluesrelation
last_update: 2013-11-24 20:11:39
n_rows: 1339209
clustered_index_size: 8431
sum_of_other_index_sizes: 12912
3 rows in set (0,00 sec)
>
> Another issue with the given query in MySQL 5.6 is
> the effect of the new variable
> eq_range_index_dive_limit. This variable affects
> how MySQL estimates the number of matches for a
> large IN-expression. That is, if the number of
> values in the IN-list is greater than
> eq_range_index_dive_limit, in order to reduce the
> cost of optimization, the optimizer will not
> actually check the B-tree for an estimate for
> every given value, but base it plan on the
> precomputed statistics. However, this may give
> bad plans if the general statistics is not
> representative for the values to be looked up
> (Ref. Bug#70586). You can set
> eq_range_index_dive_limit to 0 to make sure it
> collects statistics the same way as in earlier
> versions.
[mysql]>show global variables like 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 10 |
+---------------------------+-------+
>
> With respect to your particular query, you have
> what we call an outer reference in your sub-query
> (ADDRESS.ForeignEditPermission = 65535). This
> limits a bit the kind of sub-query optimization
> that we can do. (E.g., we cannot use
> materialization where the result of the sub-query
> is stored in a temporary table.)
> Is the above condition strictly necessary, or
> could it be pulled out to the main query?
I tried several variants of the query:
On TEST (100 rows in set (1 hour 42 min 4.64 sec)):
On TEST (100 rows in set (2.09 sec)):
On TEST (100 rows in set (0.74 sec)):
>
> We are very interested in understanding more about
> what happens with your query, and I hope you may
> be willing to help us. One option is that you
> provide us with the optimizer trace (See
> http://dev.mysql.com/doc/internals/en/optimizer-tr
> acing.html for how to do this). That would tell
> us why the optimizer ends up with a specific query
> plan. Another option is that you provide us with
> a sample database that we can use to reconstruct
> this ourselves. If the latter, I suggest that you
> create a bug report at bugs.mysql.com and upload
> your data to this bug report.
>
> Thanks,
I created the traces, however they are truncated and I did not find a way around. I hope they'll help anyway.
LIVE:
TEST:
I'll post the link to the bug report as soon as I've created it.
Regards,
Martin
I will open a bug report and attach a dump.
Øystein Grøvlen Wrote:
-------------------------------------------------------
> Hi Martin,
>
> It seems to me that there must be some differences
> in the statistics used by the query optimizer on
> your two systems. The output of SHOW INDEX should
> show the statistics that the optimization is based
> on.
>
> One reason for differences in statistics may be
> that one or both systems is using the traditional
> transient statistics that will be recomputed on
> each start-up. MySQL 5.6 introduced persistent
> statistics which will be more stable.
Persistent statistics are enabled on both systems:
[mysql]>show global variables like 'innodb_stat%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
7 rows in set (0.00 sec)
> Is any of the databases upgraded from 5.5 or
> earlier, and if so, did you run the upgrade
> script?
The upgrade happened via mysqldump and restore.
>
> To check if persistent statistics is in use, check
> if the tables mysql.innodb_table_stats and
> mysql.innodb_index_stats contain information about
> this table.
Statistics on TEST:
[mysql]>select * from innodb_table_stats where database_name = 'abc' and table_name in ('address', 'addressorel', 'selectionvaluesrelation')\G
*************************** 1. row ***************************
database_name: abc
table_name: address
last_update: 2013-12-06 13:28:21
n_rows: 295233
clustered_index_size: 16256
sum_of_other_index_sizes: 26762
*************************** 2. row ***************************
database_name: abc
table_name: addressorel
last_update: 2013-12-06 13:29:14
n_rows: 667278
clustered_index_size: 4521
sum_of_other_index_sizes: 14908
*************************** 3. row ***************************
database_name: abc
table_name: selectionvaluesrelation
last_update: 2013-12-06 13:30:37
n_rows: 2169873
clustered_index_size: 14328
sum_of_other_index_sizes: 21115
3 rows in set (0.00 sec)
Statistics on LIVE:
[mysql]>select * from innodb_table_stats where database_name = 'abc' and table_name in ('address', 'addressorel', 'selectionvaluesrelation')\G
*************************** 1. row ***************************
database_name: abc
table_name: address
last_update: 2013-12-09 04:11:12
n_rows: 304138
clustered_index_size: 17088
sum_of_other_index_sizes: 27456
*************************** 2. row ***************************
database_name: abc
table_name: addressorel
last_update: 2013-11-22 09:21:40
n_rows: 663491
clustered_index_size: 4521
sum_of_other_index_sizes: 14907
*************************** 3. row ***************************
database_name: abc
table_name: selectionvaluesrelation
last_update: 2013-11-24 20:11:39
n_rows: 1339209
clustered_index_size: 8431
sum_of_other_index_sizes: 12912
3 rows in set (0,00 sec)
>
> Another issue with the given query in MySQL 5.6 is
> the effect of the new variable
> eq_range_index_dive_limit. This variable affects
> how MySQL estimates the number of matches for a
> large IN-expression. That is, if the number of
> values in the IN-list is greater than
> eq_range_index_dive_limit, in order to reduce the
> cost of optimization, the optimizer will not
> actually check the B-tree for an estimate for
> every given value, but base it plan on the
> precomputed statistics. However, this may give
> bad plans if the general statistics is not
> representative for the values to be looked up
> (Ref. Bug#70586). You can set
> eq_range_index_dive_limit to 0 to make sure it
> collects statistics the same way as in earlier
> versions.
[mysql]>show global variables like 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 10 |
+---------------------------+-------+
>
> With respect to your particular query, you have
> what we call an outer reference in your sub-query
> (ADDRESS.ForeignEditPermission = 65535). This
> limits a bit the kind of sub-query optimization
> that we can do. (E.g., we cannot use
> materialization where the result of the sub-query
> is stored in a temporary table.)
> Is the above condition strictly necessary, or
> could it be pulled out to the main query?
I tried several variants of the query:
On TEST (100 rows in set (1 hour 42 min 4.64 sec)):
SELECT ADDRESS.KUNDENWERT, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.AGENT_ID, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.ACTIVITY_TYPE_ALLOWED, ADDRESS.DEACTIVATED FROM ADDRESS WHERE ( ( (ADDRESS.DEACTIVATED = 0) AND (ADDRESS.GGUID IN ( SELECT selectionvaluesrelation.TableGUID FROM selectionvaluesrelation WHERE selectionvaluesrelation.ValueGUID IN (0xD95B94336A9946A39CF5B58CFE772D8C) ) ) ) AND ( ADDRESS.GGUID IN ( SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND (OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0) OR (OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407) AND ADDRESS.ForeignEditPermission = 65535)) ) ) ) AND (ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0) ORDER BY ADDRESS.AGENT_ID ASC LIMIT 0, 100;
On TEST (100 rows in set (2.09 sec)):
SELECT ADDRESS.KUNDENWERT, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.AGENT_ID, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.ACTIVITY_TYPE_ALLOWED, ADDRESS.DEACTIVATED FROM ADDRESS0 AS address WHERE ADDRESS.DEACTIVATED = 0 AND ADDRESS.GGUID IN (SELECT selectionvaluesrelation.TableGUID FROM selectionvaluesrelation WHERE selectionvaluesrelation.ValueGUID IN (0xD95B94336A9946A39CF5B58CFE772D8C)) AND ( ADDRESS.GGUID IN (SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0)) OR ( ADDRESS.GGUID IN (SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407)) AND ADDRESS.ForeignEditPermission = 65535 ) ) AND ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0 ORDER BY ADDRESS.AGENT_ID ASC LIMIT 0, 100;
On TEST (100 rows in set (0.74 sec)):
SELECT ADDRESS.KUNDENWERT, ADDRESS.MONITORING_STATE, ADDRESS.ISORGANISATION, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.AGENT_ID, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORPORATE_CUSTOMER_ID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_CAR, ADDRESS.CORP_ASSOC_PREMIUM_CAR, ADDRESS.SQ_GESAMT_VKDNR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.DEACTIVATED FROM SVG.ADDRESS0 AS ADDRESS INNER JOIN (SELECT TableGUID FROM SVG.selectionvaluesrelation AS svr WHERE svr.VALUEGUID = 0xA8B127D1235B4975838C5D41ED053ABC) AS q ON ADDRESS.GGUID = q.TABLEGUID WHERE ((ADDRESS.DEACTIVATED = 0) AND (ADDRESS.GGUID IN (SELECT TABLEGUID FROM SVG.ADDRESSORel WHERE EIMRight >= 64 AND (OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0) OR (OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407) AND ADDRESS.ForeignEditPermission = 65535))))) AND (ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0) LIMIT 100
>
> We are very interested in understanding more about
> what happens with your query, and I hope you may
> be willing to help us. One option is that you
> provide us with the optimizer trace (See
> http://dev.mysql.com/doc/internals/en/optimizer-tr
> acing.html for how to do this). That would tell
> us why the optimizer ends up with a specific query
> plan. Another option is that you provide us with
> a sample database that we can use to reconstruct
> this ourselves. If the latter, I suggest that you
> create a bug report at bugs.mysql.com and upload
> your data to this bug report.
>
> Thanks,
I created the traces, however they are truncated and I did not find a way around. I hope they'll help anyway.
LIVE:
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"join_preparation": {
"select#": 3,
"steps": [
{
"expanded_query": "/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))))"
},
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` where ((`address`.`DEACTIVATED` = 0) and `address`.`GGUID` in (/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)) and `address`.`GGUID` in (/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))))) and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0)) order by `address`.`AGENT_ID` limit 0,100"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` semi join (`addressorel`) semi join (`selectionvaluesrelation`) where ((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`)) order by `address`.`AGENT_ID` limit 0,100"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`address0` `address`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`addressorel`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`selectionvaluesrelation`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
TEST:
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"join_preparation": {
"select#": 3,
"steps": [
{
"expanded_query": "/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`ForeignEditPermission` = 65535))))"
},
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` where ((`address`.`DEACTIVATED` = 0) and `address`.`GGUID` in (/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)) and `address`.`GGUID` in (/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`ForeignEditPermission` = 65535))))) and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0)) order by `address`.`AGENT_ID` limit 0,100"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` semi join (`addressorel`) semi join (`selectionvaluesrelation`) where ((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`ForeignEditPermission` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`)) order by `address`.`AGENT_ID` limit 0,100"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`ForeignEditPermission` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`ForeignEditPermission`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`ForeignEditPermission`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`ForeignEditPermission`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`address0` `address`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`addressorel`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`selectionvaluesrelation`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
I'll post the link to the bug report as soon as I've created it.
Regards,
Martin
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.