-- ==================== --
-- Database preparation --
-- ==================== --
drop database if exists dummy_database;

create schema dummy_database collate utf8_general_ci;
use dummy_database;

-- records table
create table if not exists records
(
    id             varchar(64) collate ascii_bin          not null
        primary key,
    published_date datetime   default current_timestamp() not null,
    enabled        tinyint(1) default 0                   not null
) collate = utf8_czech_ci;

create index if not exists record_idx_published_date
    on records (published_date);

-- parameter table
create table if not exists parameter
(
    id       varchar(128) collate ascii_bin not null
        primary key,
    computed datetime                       not null
) collate = utf8_czech_ci;

create index if not exists parameter_idx_algorithm_computed
    on parameter (computed);

-- records_array table
create table if not exists records_array
(
    record_id    varchar(64) collate ascii_bin  not null,
    parameter_id varchar(128) collate ascii_bin not null,
    array        varchar(4096)                  not null,
    primary key (record_id, parameter_id),
    constraint records_array_fk_parameter_id
        foreign key (parameter_id) references parameter (id),
    constraint records_array_ibfk_record_id
        foreign key (record_id) references records (id)
            on delete cascade
) collate = utf8_czech_ci;


-- data insertion
insert into dummy_database.records (id, published_date, enabled)
values ('1-a', '2020-01-24 08:50:00', 1);

insert into dummy_database.records (id, published_date, enabled)
values ('2-b', '2017-11-20 18:59:00', 1);


insert into dummy_database.parameter (id, computed)
values ('par-1', '2020-01-24 08:50:00');

insert into dummy_database.parameter (id, computed)
values ('par-2', '2020-01-24 08:50:00');


insert into dummy_database.records_array (record_id, parameter_id, array)
values ('1-a', 'par-1', '"[0.1, 0.2, 0.3, 0.4]"');

insert into dummy_database.records_array (record_id, parameter_id, array)
values ('2-b', 'par-1', '"[0.1, 0.2, 0.3, 0.4]"');

-- =========================== --
-- Query returning bad results --
-- =========================== --
-- Result set is empty

-- Explain
# +--+---------------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+
# |id|select_type    |table        |type  |possible_keys                        |key                          |key_len|ref                      |rows|Extra                             |
# +--+---------------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+
# |1 |PRIMARY        |r            |range |PRIMARY,record_idx_published_date    |record_idx_published_date    |5      |NULL                     |1   |Using index condition; Using where|
# |1 |PRIMARY        |a            |eq_ref|PRIMARY,records_array_fk_parameter_id|PRIMARY                      |196    |dummy_database.r.id,const|1   |Using where                       |
# |1 |PRIMARY        |<derived3>   |ref   |key0                                 |key0                         |66     |dummy_database.r.id      |2   |Using where                       |
# |3 |LATERAL DERIVED|records_array|ref   |PRIMARY,records_array_fk_parameter_id|records_array_fk_parameter_id|130    |const                    |2   |Using where; Using index          |
# +--+---------------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+

-- Explain (JSON)
# [
#   {
#     "EXPLAIN": "{\n  \"query_block\": {\n    \"select_id\": 1,\n    \"table\": {\n      \"table_name\": \"r\",\n      \"access_type\": \"range\",\n      \"possible_keys\": [\"PRIMARY\", \"record_idx_published_date\"],\n      \"key\": \"record_idx_published_date\",\n      \"key_length\": \"5\",\n      \"used_key_parts\": [\"published_date\"],\n      \"rows\": 1,\n      \"filtered\": 100,\n      \"index_condition\": \"r.published_date between '2017-11-20 18:59:01.000000' and '2020-01-24 08:50:00.000000'\",\n      \"attached_condition\": \"r.enabled = 1\"\n    },\n    \"table\": {\n      \"table_name\": \"a\",\n      \"access_type\": \"eq_ref\",\n      \"possible_keys\": [\"PRIMARY\", \"records_array_fk_parameter_id\"],\n      \"key\": \"PRIMARY\",\n      \"key_length\": \"196\",\n      \"used_key_parts\": [\"record_id\", \"parameter_id\"],\n      \"ref\": [\"dummy_database.r.id\", \"const\"],\n      \"rows\": 1,\n      \"filtered\": 100,\n      \"attached_condition\": \"a.parameter_id = 'par-1'\"\n    },\n    \"table\": {\n      \"table_name\": \"<derived3>\",\n      \"access_type\": \"ref\",\n      \"possible_keys\": [\"key0\"],\n      \"key\": \"key0\",\n      \"key_length\": \"66\",\n      \"used_key_parts\": [\"record_id\"],\n      \"ref\": [\"dummy_database.r.id\"],\n      \"rows\": 2,\n      \"filtered\": 100,\n      \"attached_condition\": \"cnts.parameter_id = 'par-1' and cnts.cnt = 1\",\n      \"materialized\": {\n        \"lateral\": 1,\n        \"query_block\": {\n          \"select_id\": 3,\n          \"having_condition\": \"cnt = 1\",\n          \"table\": {\n            \"table_name\": \"records_array\",\n            \"access_type\": \"ref\",\n            \"possible_keys\": [\"PRIMARY\", \"records_array_fk_parameter_id\"],\n            \"key\": \"records_array_fk_parameter_id\",\n            \"key_length\": \"130\",\n            \"used_key_parts\": [\"parameter_id\"],\n            \"ref\": [\"const\"],\n            \"rows\": 2,\n            \"filtered\": 100,\n            \"attached_condition\": \"records_array.parameter_id = 'par-1'\",\n            \"using_index\": true\n          }\n        }\n      }\n    }\n  }\n}"
#   }
# ]

set optimizer_switch = 'split_materialized=on';
select r.*,
       a2.*
from records r
         inner join (
    select a.*
    from records_array a
             inner join (
        select record_id, parameter_id, count(*) as cnt
        from records_array
        group by record_id, parameter_id) cnts
                        on a.record_id = cnts.record_id and
                           a.parameter_id = cnts.parameter_id
    where a.parameter_id = 'par-1'
      and cnts.cnt = 1) a2
                    on r.id = a2.record_id
where r.enabled = 1
  and r.published_date between '2017-11-20 18:59:01' and '2020-01-24 08:50:00';

-- ============================================= --
-- The same query with custom optimizer settings --
-- ============================================= --

-- Correct result:
# +---+-------------------+-------+---------+------------+----------------------+
# |id |published_date     |enabled|record_id|parameter_id|array                 |
# +---+-------------------+-------+---------+------------+----------------------+
# |1-a|2020-01-24 08:50:00|1      |1-a      |par-1       |"[0.1, 0.2, 0.3, 0.4]"|
# +---+-------------------+-------+---------+------------+----------------------+

-- Explain
# +--+-----------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+
# |id|select_type|table        |type  |possible_keys                        |key                          |key_len|ref                      |rows|Extra                             |
# +--+-----------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+
# |1 |PRIMARY    |r            |range |PRIMARY,record_idx_published_date    |record_idx_published_date    |5      |NULL                     |1   |Using index condition; Using where|
# |1 |PRIMARY    |a            |eq_ref|PRIMARY,records_array_fk_parameter_id|PRIMARY                      |196    |dummy_database.r.id,const|1   |Using where                       |
# |1 |PRIMARY    |<derived3>   |ref   |key0                                 |key0                         |66     |dummy_database.r.id      |2   |Using where                       |
# |3 |DERIVED    |records_array|ref   |records_array_fk_parameter_id        |records_array_fk_parameter_id|130    |const                    |2   |Using where; Using index          |
# +--+-----------+-------------+------+-------------------------------------+-----------------------------+-------+-------------------------+----+----------------------------------+

-- Explain (JSON)
# [
#   {
#     "EXPLAIN": "{\n  \"query_block\": {\n    \"select_id\": 1,\n    \"table\": {\n      \"table_name\": \"r\",\n      \"access_type\": \"range\",\n      \"possible_keys\": [\"PRIMARY\", \"record_idx_published_date\"],\n      \"key\": \"record_idx_published_date\",\n      \"key_length\": \"5\",\n      \"used_key_parts\": [\"published_date\"],\n      \"rows\": 1,\n      \"filtered\": 100,\n      \"index_condition\": \"r.published_date between '2017-11-20 18:59:01.000000' and '2020-01-24 08:50:00.000000'\",\n      \"attached_condition\": \"r.enabled = 1\"\n    },\n    \"table\": {\n      \"table_name\": \"a\",\n      \"access_type\": \"eq_ref\",\n      \"possible_keys\": [\"PRIMARY\", \"records_array_fk_parameter_id\"],\n      \"key\": \"PRIMARY\",\n      \"key_length\": \"196\",\n      \"used_key_parts\": [\"record_id\", \"parameter_id\"],\n      \"ref\": [\"dummy_database.r.id\", \"const\"],\n      \"rows\": 1,\n      \"filtered\": 100,\n      \"attached_condition\": \"a.parameter_id = 'par-1'\"\n    },\n    \"table\": {\n      \"table_name\": \"<derived3>\",\n      \"access_type\": \"ref\",\n      \"possible_keys\": [\"key0\"],\n      \"key\": \"key0\",\n      \"key_length\": \"66\",\n      \"used_key_parts\": [\"record_id\"],\n      \"ref\": [\"dummy_database.r.id\"],\n      \"rows\": 2,\n      \"filtered\": 100,\n      \"attached_condition\": \"cnts.parameter_id = 'par-1' and cnts.cnt = 1\",\n      \"materialized\": {\n        \"query_block\": {\n          \"select_id\": 3,\n          \"having_condition\": \"cnt = 1\",\n          \"table\": {\n            \"table_name\": \"records_array\",\n            \"access_type\": \"ref\",\n            \"possible_keys\": [\"records_array_fk_parameter_id\"],\n            \"key\": \"records_array_fk_parameter_id\",\n            \"key_length\": \"130\",\n            \"used_key_parts\": [\"parameter_id\"],\n            \"ref\": [\"const\"],\n            \"rows\": 2,\n            \"filtered\": 100,\n            \"attached_condition\": \"records_array.parameter_id <=> 'par-1' and records_array.parameter_id = 'par-1'\",\n            \"using_index\": true\n          }\n        }\n      }\n    }\n  }\n}"
#   }
# ]

set optimizer_switch = 'split_materialized=off';
select r.*,
       a2.*
from records r
         inner join (
    select a.*
    from records_array a
             inner join (
        select record_id, parameter_id, count(*) as cnt
        from records_array
        group by record_id, parameter_id) cnts
                        on a.record_id = cnts.record_id and
                           a.parameter_id = cnts.parameter_id
    where a.parameter_id = 'par-1'
      and cnts.cnt = 1) a2
                    on r.id = a2.record_id
where r.enabled = 1
  and r.published_date between '2017-11-20 18:59:01' and '2020-01-24 08:50:00';
