pengxianggui commented on issue #23128:
URL:
https://github.com/apache/shardingsphere/issues/23128#issuecomment-1367062228
> You may set `sql-show: true` to log SQL and check whether the actual SQL
perform cartesian product.
Hi @TeslaCN , I turned on sql printing and It showed a very interesting
phenomenon. The sql looks like the follow:
```
select o.* from t_order o
left join t_order_item oi on o.order_id = oi.order_id
left join t_order_item1 oi1 on o.order_id = oi1.order_id
where o.order_time > xxx and o.order_time < yyyy
```
> order_time is the only sharding field
**The result shows that the first left join still associates all the real
tables, and the second left join avoids the Cartesian product**
The above sql is for the convenience of describing the simplified results.
In the following log, you can see the actual sql:
```
2022-12-29 11:56:15.534 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625
ShardingSphere-SQL -Logic SQL: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre,
a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type,
h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url,
h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code,
sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code,
sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code,
sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm a
left join t_sensor_alarm sa on a.id = sa.alarm_id
left join t_alarm_handle h on a.id = h.alarm_id
where 1 = 1 and a.alarm_time >= ? and a.alarm_time <= ? and a.if_pre =
? and (a.origin_data -> '$.normalDate' is not null)
order by a.alarm_time desc LIMIT ?
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625
ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre,
a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type,
h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url,
h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code,
sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code,
sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code,
sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2022 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where 1 = 1 and a.alarm_time >= ? and a.alarm_time <= ? and a.if_pre =
? and (a.origin_data -> '$.normalDate' is not null)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437,
2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625
ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre,
a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type,
h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url,
h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code,
sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code,
sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code,
sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2023 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437,
2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625
ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre,
a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type,
h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url,
h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code,
sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code,
sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code,
sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2024 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437,
2022-12-29T11:56:10.437, false, 10]
2022-12-29 11:56:15.535 [[TID: N/A]] [http-nio-8082-exec-1] INFO 56625
ShardingSphere-SQL -Actual SQL: db0 ::: select
a.id id, a.report_time, a.alarm_time, a.level, a.type,
a.name, a.remark, a.longitude, a.latitude, a.address, a.origin_data,
a.responsible, a.responsible_phone, a.contact, a.contact_phone,
a.from_where, a.from_where_code, a.from_where_remark, a.if_pre,
a.domain_code,
h.id handle_id, h.handler, h.handle_time, h.handle_type,
h.handle_advice,
h.if_enable_emergency_res, h.emergency_res_review, h.attachment_url,
h.handle_source,
sa.origin_id, sa.alarm_id, sa.sensor_name, sa.sensor_target_code,
sa.alarm_value,
sa.upper_value, sa.lower_value, sa.sensor_unit, sa.credit_code,
sa.enterprise_name,
sa.device, sa.materials, sa.hazard_source_code,
sa.hazard_source_name, sa.hazard_source_level,
sa.hazard_source_location
from t_alarm_2022 a
left join t_sensor_alarm_2025 sa on a.id = sa.alarm_id
left join t_alarm_handle_2022 h on a.id = h.alarm_id
where
1 = 1
and a.alarm_time >= ?
and a.alarm_time <= ?
and a.if_pre = ?
and
(
a.origin_data -> '$.normalDate' is not null
)
order by a.alarm_time desc LIMIT ? ::: [2022-06-29T11:56:10.437,
2022-12-29T11:56:10.437, false, 10]
```
The Actual sql displays that t_sensor_alarm exist cartesian product problem
and t_alarm_handle avoids cartesian product problem. I guess if I left join the
third table, it turns out that only the third table avoids the Cartesian
product problem. I don't know if this is a bug or an expected result?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
