I have a query of 5 subqueries, the results of which I will use next. But making an intermediate request, checking the amount of data, I get this error in phpstorm.
Communications link failure The last packet successfully received from the server was 462 milliseconds ago. The last packet sent successfully to the server was 462 milliseconds ago.
my request:
with group_a as (select distinct p.tag_id
from product_tag_position p
where p.product_id in (10536839, 51807280, 58320758)
and p.create_at between unix_timestamp('2022-07-25') and unix_timestamp('2022-08-26')),
group_b as (select distinct p.tag_id
from product_tag_position p
where p.product_id in (13163479, 66357573, 66360379)
and p.create_at between unix_timestamp('2022-07-25') and unix_timestamp('2022-08-26')),
intersection_groups as (select group_a.tag_id
from group_a
join group_b on group_a.tag_id = group_b.tag_id),
only_group_a as (select distinct tag_id from group_a where tag_id not in (select tag_id from group_b)),
only_group_b as (select distinct tag_id from group_b where tag_id not in (select tag_id from group_a))
select count(*)
from group_a
union
select count(*)
from group_b
union
select count(*)
from intersection_groups
union
select count(*)
from only_group_a
union
select count(*)
from only_group_b
the structure of the table to which the request goes.
create_at - integer
product_id - integer
tag_id - integer
position - smallinteger
if the request is not fully executed, but for example to remove the last two unions, the request will be executed, also if only the last two are executed, but together it gives an error.
what is the problem with this request? the second option works fine
with group_a as (select distinct p.tag_id
from product_tag_position p
where p.product_id in (10536839, 51807280, 58320758)
and p.create_at between unix_timestamp('2022-07-25') and unix_timestamp('2022-08-26')),
group_b as (select distinct p.tag_id
from product_tag_position p
where p.product_id in (13163479, 66357573, 66360379)
and p.create_at between unix_timestamp('2022-07-25') and unix_timestamp('2022-08-26')),
intersection_groups as (select group_a.tag_id
from group_a
join group_b on group_a.tag_id = group_b.tag_id),
only_group_a as (select a.tag_id tag_a, b.tag_id tag_b
from group_a a
left join group_b b on a.tag_id = b.tag_id),
only_group_b as (select b.tag_id tag_b, a.tag_id tag_a
from group_b b
left join group_a a on a.tag_id = b.tag_id)
(select count(*) from group_a)
union
(select count(*) from group_b)
union
(select count(*) from intersection_groups)
union
(select count(*) from only_group_a where tag_b is null)
union
(select count(*) from only_group_b where tag_a is null)