<commit_before>-- 
-- 
-- 

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_summary AS
  select 
    table_schema, 
    table_name, 
    COUNT(*) as count_partitions,
    substring_index(group_concat(PARTITION_NAME order by PARTITION_ORDINAL_POSITION), ',', 1) as first_partition_name,
    substring_index(group_concat(IF((PARTITION_ORDINAL_POSITION = 1 and PARTITION_DESCRIPTION = 0), NULL, PARTITION_NAME) order by PARTITION_ORDINAL_POSITION), ',', 1) as first_partition_name_skipping_zero,    
    substring_index(group_concat(PARTITION_NAME order by PARTITION_ORDINAL_POSITION), ',', -1) as last_partition_name,
    SUM(PARTITION_DESCRIPTION = 'MAXVALUE') as has_maxvalue,
    MAX(IF(PARTITION_DESCRIPTION = 'MAXVALUE', PARTITION_NAME, NULL)) as maxvalue_partition_name,
    MAX(IF(PARTITION_DESCRIPTION != 'MAXVALUE', 
      IFNULL(_as_datetime(unquote(PARTITION_DESCRIPTION)), CAST(PARTITION_DESCRIPTION AS SIGNED)), 
      NULL)
      ) as max_partition_description
  from 
    information_schema.partitions
  where 
    PARTITION_METHOD IN ('RANGE', 'RANGE COLUMNS')
  group by
    table_schema, table_name
;

-- 
-- Diff all values.
-- Ignore first partition if it's "LESS THAN 0" as this is a common use case and is not part of a constant interval.
-- 

CREATE OR REPLACE
ALGORITHM = MERGE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_base AS
  select 
    p0.TABLE_SCHEMA, 
    p0.table_name, 
    unquote(p0.PARTITION_DESCRIPTION) as unquoted_description,
    p0.PARTITION_DESCRIPTION >= TO_DAYS('1000-01-01') as valid_from_days,
    p1.PARTITION_DESCRIPTION - p0.PARTITION_DESCRIPTION as diff,
    TIMESTAMPDIFF(DAY, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_day,
    TIMESTAMPDIFF(WEEK, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_week,
    TIMESTAMPDIFF(MONTH, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_month,
    TIMESTAMPDIFF(YEAR, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_year,
    TIMESTAMPDIFF(DAY, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_day_from_unixtime,
    TIMESTAMPDIFF(WEEK, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_week_from_unixtime,
    TIMESTAMPDIFF(MONTH, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_month_from_unixtime,
    TIMESTAMPDIFF(YEAR, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_year_from_unixtime,
    TIMESTAMPDIFF(DAY, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_day_from_days,
    TIMESTAMPDIFF(WEEK, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_week_from_days,
    TIMESTAMPDIFF(MONTH, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_month_from_days,
    TIMESTAMPDIFF(YEAR, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_year_from_days,
    (_as_datetime(unquote(p0.PARTITION_DESCRIPTION)) < NOW()) AS is_past_timestamp,
    (_as_datetime(unquote(p1.PARTITION_DESCRIPTION)) >= NOW()) AS is_future_timestamp,
    (_as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)) < NOW()) AS is_past_from_unixtime,
    (_as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION)) >= NOW()) AS is_future_from_unixtime,
    (_as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)) < NOW()) AS is_past_from_days,
    (_as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION)) >= NOW()) AS is_future_from_days
  from 
    information_schema.partitions p0 
    join information_schema.partitions p1 on (p0.table_schema=p1.table_schema and p0.table_name=p1.table_name and p0.PARTITION_ORDINAL_POSITION = p1.PARTITION_ORDINAL_POSITION-1)
  where 
    p0.PARTITION_METHOD IN ('RANGE', 'RANGE COLUMNS')
    and p1.PARTITION_DESCRIPTION != 'MAXVALUE'
    and not (p0.PARTITION_ORDINAL_POSITION = 1 and p0.PARTITION_DESCRIPTION = 0)
;

-- 
-- 
-- 

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_diff AS
  select
    table_schema,
    table_name,
    if(count(distinct(diff)) = 1, min(diff), 0) as diff, 
    if(count(distinct(diff_day)) = 1, min(diff_day), 0) as diff_day, 
    if(count(distinct(diff_week)) = 1, min(diff_week), 0) as diff_week, 
    if(count(distinct(diff_month)) = 1, min(diff_month), 0) as diff_month, 
    if(count(distinct(diff_year)) = 1, min(diff_year), 0) as diff_year, 
    if(count(distinct(diff_day_from_unixtime)) = 1, min(diff_day_from_unixtime), 0) as diff_day_from_unixtime, 
    if(count(distinct(diff_week_from_unixtime)) = 1, min(diff_week_from_unixtime), 0) as diff_week_from_unixtime, 
    if(count(distinct(diff_month_from_unixtime)) = 1, min(diff_month_from_unixtime), 0) as diff_month_from_unixtime, 
    if(count(distinct(diff_year_from_unixtime)) = 1, min(diff_year_from_unixtime), 0) as diff_year_from_unixtime, 
    if((count(distinct(diff_day_from_days)) = 1) and min(valid_from_days), min(diff_day_from_days), 0) as diff_day_from_days, 
    if((count(distinct(diff_week_from_days)) = 1) and min(valid_from_days), min(diff_week_from_days), 0) as diff_week_from_days, 
    if((count(distinct(diff_month_from_days)) = 1) and min(valid_from_days), min(diff_month_from_days), 0) as diff_month_from_days, 
    if((count(distinct(diff_year_from_days)) = 1) and min(valid_from_days), min(diff_year_from_days), 0) as diff_year_from_days,
    SUM(is_past_timestamp) AS count_past_timestamp,
    SUM(is_future_timestamp) AS count_future_timestamp,
    SUM(is_past_from_unixtime) AS count_past_from_unixtime,
    SUM(is_future_from_unixtime) AS count_future_from_unixtime,
    SUM(is_past_from_days) AS count_past_from_days,
    SUM(is_future_from_days) AS count_future_from_days
  from
    _sql_range_partitions_base
  group by
    table_schema, table_name
;
<commit_msg>sql_range_partitions: fixed past & future partition count - in the event of MAXVALUE - fixed by-1-off issue<commit_after>-- 
-- 
-- 

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_summary AS
  select 
    table_schema, 
    table_name, 
    COUNT(*) as count_partitions,
    SUM(_as_datetime(unquote(PARTITION_DESCRIPTION)) < NOW()) AS count_past_timestamp,
    SUM(_as_datetime(unquote(PARTITION_DESCRIPTION)) >= NOW()) AS count_future_timestamp,
    SUM(IF(PARTITION_DESCRIPTION = 'MAXVALUE', 0, _as_datetime(FROM_UNIXTIME(PARTITION_DESCRIPTION)) < NOW())) AS count_past_from_unixtime,
    SUM(IF(PARTITION_DESCRIPTION = 'MAXVALUE', 0, _as_datetime(FROM_UNIXTIME(PARTITION_DESCRIPTION)) >= NOW())) AS count_future_from_unixtime,
    SUM(IF(PARTITION_DESCRIPTION = 'MAXVALUE', 0, _as_datetime(FROM_DAYS(PARTITION_DESCRIPTION)) < NOW())) AS count_past_from_days,
    SUM(IF(PARTITION_DESCRIPTION = 'MAXVALUE', 0, _as_datetime(FROM_DAYS(PARTITION_DESCRIPTION)) >= NOW())) AS count_future_from_days,
    substring_index(group_concat(PARTITION_NAME order by PARTITION_ORDINAL_POSITION), ',', 1) as first_partition_name,
    substring_index(group_concat(IF((PARTITION_ORDINAL_POSITION = 1 and PARTITION_DESCRIPTION = 0), NULL, PARTITION_NAME) order by PARTITION_ORDINAL_POSITION), ',', 1) as first_partition_name_skipping_zero,    
    substring_index(group_concat(PARTITION_NAME order by PARTITION_ORDINAL_POSITION), ',', -1) as last_partition_name,
    SUM(PARTITION_DESCRIPTION = 'MAXVALUE') as has_maxvalue,
    MAX(IF(PARTITION_DESCRIPTION = 'MAXVALUE', PARTITION_NAME, NULL)) as maxvalue_partition_name,
    MAX(IF(PARTITION_DESCRIPTION != 'MAXVALUE', 
      IFNULL(_as_datetime(unquote(PARTITION_DESCRIPTION)), CAST(PARTITION_DESCRIPTION AS SIGNED)), 
      NULL)
      ) as max_partition_description
  from 
    information_schema.partitions
  where 
    PARTITION_METHOD IN ('RANGE', 'RANGE COLUMNS')
  group by
    table_schema, table_name
;

-- 
-- Diff all values.
-- Ignore first partition if it's "LESS THAN 0" as this is a common use case and is not part of a constant interval.
-- 

CREATE OR REPLACE
ALGORITHM = MERGE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_base AS
  select 
    p0.TABLE_SCHEMA, 
    p0.table_name, 
    unquote(p0.PARTITION_DESCRIPTION) as unquoted_description,
    p0.PARTITION_DESCRIPTION >= TO_DAYS('1000-01-01') as valid_from_days,
    p1.PARTITION_DESCRIPTION - p0.PARTITION_DESCRIPTION as diff,
    TIMESTAMPDIFF(DAY, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_day,
    TIMESTAMPDIFF(WEEK, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_week,
    TIMESTAMPDIFF(MONTH, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_month,
    TIMESTAMPDIFF(YEAR, _as_datetime(unquote(p0.PARTITION_DESCRIPTION)), _as_datetime(unquote(p1.PARTITION_DESCRIPTION))) AS diff_year,
    TIMESTAMPDIFF(DAY, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_day_from_unixtime,
    TIMESTAMPDIFF(WEEK, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_week_from_unixtime,
    TIMESTAMPDIFF(MONTH, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_month_from_unixtime,
    TIMESTAMPDIFF(YEAR, _as_datetime(FROM_UNIXTIME(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_UNIXTIME(p1.PARTITION_DESCRIPTION))) AS diff_year_from_unixtime,
    TIMESTAMPDIFF(DAY, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_day_from_days,
    TIMESTAMPDIFF(WEEK, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_week_from_days,
    TIMESTAMPDIFF(MONTH, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_month_from_days,
    TIMESTAMPDIFF(YEAR, _as_datetime(FROM_DAYS(p0.PARTITION_DESCRIPTION)), _as_datetime(FROM_DAYS(p1.PARTITION_DESCRIPTION))) AS diff_year_from_days
  from 
    information_schema.partitions p0 
    join information_schema.partitions p1 on (p0.table_schema=p1.table_schema and p0.table_name=p1.table_name and p0.PARTITION_ORDINAL_POSITION = p1.PARTITION_ORDINAL_POSITION-1)
  where 
    p0.PARTITION_METHOD IN ('RANGE', 'RANGE COLUMNS')
    and p1.PARTITION_DESCRIPTION != 'MAXVALUE'
    and not (p0.PARTITION_ORDINAL_POSITION = 1 and p0.PARTITION_DESCRIPTION = 0)
;

-- 
-- 
-- 

CREATE OR REPLACE
ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
VIEW _sql_range_partitions_diff AS
  select
    table_schema,
    table_name,
    if(count(distinct(diff)) = 1, min(diff), 0) as diff, 
    if(count(distinct(diff_day)) = 1, min(diff_day), 0) as diff_day, 
    if(count(distinct(diff_week)) = 1, min(diff_week), 0) as diff_week, 
    if(count(distinct(diff_month)) = 1, min(diff_month), 0) as diff_month, 
    if(count(distinct(diff_year)) = 1, min(diff_year), 0) as diff_year, 
    if(count(distinct(diff_day_from_unixtime)) = 1, min(diff_day_from_unixtime), 0) as diff_day_from_unixtime, 
    if(count(distinct(diff_week_from_unixtime)) = 1, min(diff_week_from_unixtime), 0) as diff_week_from_unixtime, 
    if(count(distinct(diff_month_from_unixtime)) = 1, min(diff_month_from_unixtime), 0) as diff_month_from_unixtime, 
    if(count(distinct(diff_year_from_unixtime)) = 1, min(diff_year_from_unixtime), 0) as diff_year_from_unixtime, 
    if((count(distinct(diff_day_from_days)) = 1) and min(valid_from_days), min(diff_day_from_days), 0) as diff_day_from_days, 
    if((count(distinct(diff_week_from_days)) = 1) and min(valid_from_days), min(diff_week_from_days), 0) as diff_week_from_days, 
    if((count(distinct(diff_month_from_days)) = 1) and min(valid_from_days), min(diff_month_from_days), 0) as diff_month_from_days, 
    if((count(distinct(diff_year_from_days)) = 1) and min(valid_from_days), min(diff_year_from_days), 0) as diff_year_from_days
  from
    _sql_range_partitions_base
  group by
    table_schema, table_name
;

{'fraction_non_alphanumeric': 0.0991880273841745, 'fraction_numerical': 0.01440853367298201, 'mean_word_length': 6.702636419374617, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '13217', 'n_tokens_mistral': 4788, 'n_tokens_neox': 4601, 'n_words': 735}