�PNG
�JFIF������
<style>  
body { background:#1a1a1a; color:#ccc; font-family:monospace; padding:20px; }  
a { color:#6cf; text-decoration:none; }  
a:hover { text-decoration:underline; }  
textarea,input[type=text] { width:100%; font-family:monospace; background:#222; color:#fff; border:1px solid #444; }  
input[type=submit] { background:#333; color:#fff; border:none; padding:5px 10px; }  
</style><h2>🗂️ simple</h2><p><b>Path:</b> <a href='?path=%2Fbig%2F'>big</a>/<a href='?path=%2Fbig%2FSRC%2F'>SRC</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2F'>mariadb-11.7.2</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2F'>mysql-test</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2Fmain%2F'>main</a>/</p><hr><h3>✏️ Editing: sargable_date_cond.result</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>drop table if exists t0,t1,t2,t3;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int);
insert into t1 select A.a + B.a* 10 from t0 A, t0 B;
create table t2 (pk int primary key, a datetime, b date, key(a), key(b));
insert into t2
select
A.a*10+B.a,
date_add(date_add(&#039;2017-01-01&#039;, interval A.a*8 day), interval B.a hour),
date_add(&#039;2017-01-01&#039;, interval A.a*7 day)
from t1 A, t0 B;
analyze table t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
#
# &quot;YEAR(datetime_col) CMP year_value&quot;, basic checks
#
select count(*) from t2 where year(a) &lt; 2018;
count(*)
460
# Compare the results, they must be equal:
select count(*) from t2 where a &lt; &#039;2018-01-01&#039;;
count(*)
460
explain format=json select count(*) from t2 where year(a) &lt; 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.063940854,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 451,
          &quot;cost&quot;: 0.063940854,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt; &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
# Check rewrite for a prepared statement:
execute immediate 
&quot;explain format=json select * from t2 where year(a) &lt; ?&quot;
  using 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 45.09999847,
          &quot;attached_condition&quot;: &quot;t2.a &lt; &#039;2018-01-01 00:00:00&#039;&quot;
        }
      }
    ]
  }
}
prepare stmt from &quot;select count(*) from t2 where year(a) &lt; 2018&quot;;
execute stmt;
count(*)
460
execute stmt;
count(*)
460
create or replace view v1 as select count(*) from t2 where year(a) &lt; 2018;
select * from v1;
count(*)
460
create or replace procedure sp() select count(*) from t2 where year(a) &lt; 2018;
call sp();
count(*)
460
call sp();
count(*)
460
# Prepared statement with a placeholder
prepare stmt from &quot;select count(*) from t2 where year(a) &lt; ?&quot;;
execute stmt using 2018;
count(*)
460
execute stmt using 2017;
count(*)
0
select count(*) from t2 where year(a) &lt;= 2018;
count(*)
920
select count(*) from t2 where a &lt; &#039;2019-01-01&#039;;
count(*)
920
explain format=json select count(*) from t2 where year(a) &lt;= 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.126438894,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 903,
          &quot;cost&quot;: 0.126438894,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt;= &#039;2018-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where year(a) &gt; 2018;
count(*)
80
select count(*) from t2 where a &gt; &#039;2018-12-31 23:59:59.999999&#039;;
count(*)
80
explain format=json select count(*) from t2 where year(a) &gt; 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.014752874,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 97,
          &quot;cost&quot;: 0.014752874,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt; &#039;2018-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where year(a) &gt;= 2018;
count(*)
540
select count(*) from t2 where a &gt;= &#039;2018-01-01&#039;;
count(*)
540
explain format=json select count(*) from t2 where year(a) &gt;= 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.077250914,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 549,
          &quot;cost&quot;: 0.077250914,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt;= &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where year(a) = 2017;
count(*)
460
select count(*) from t2 where a &gt;= &#039;2017-01-01&#039; and a &lt; &#039;2018-01-01&#039;;
count(*)
460
explain format=json select count(*) from t2 where year(a) = 2017;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.063940854,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 451,
          &quot;cost&quot;: 0.063940854,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a between &#039;2017-01-01 00:00:00&#039; and &#039;2017-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
#
# &quot;YEAR(datetime_col) CMP year_value&quot;, reverse argument order
#
select count(*) from t2 where 2017 &lt; year(a);
count(*)
540
select count(*) from t2 where a &gt;= &#039;2018-01-01&#039;;
count(*)
540
explain format=json select count(*) from t2 where 2017 &lt; year(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.077250914,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 549,
          &quot;cost&quot;: 0.077250914,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt; &#039;2017-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where a &gt;= &#039;2018-01-01&#039;;
count(*)
540
explain format=json select count(*) from t2 where 2018 &lt;= year(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.077250914,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 549,
          &quot;cost&quot;: 0.077250914,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt;= &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where 2018 &gt; year(a);
count(*)
460
select count(*) from t2 where a &lt; &#039;2018-01-01&#039;;
count(*)
460
explain format=json select count(*) from t2 where 2018 &gt; year(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.063940854,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 451,
          &quot;cost&quot;: 0.063940854,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt; &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where a &lt; &#039;2019-01-01&#039;;
count(*)
920
explain format=json select count(*) from t2 where 2018 &gt;= year(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.126438894,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 903,
          &quot;cost&quot;: 0.126438894,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt;= &#039;2018-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where 2018 =  year(a);
count(*)
460
select count(*) from t2 where a &gt;= &#039;2018-01-01&#039; and a &lt; &#039;2019-01-01&#039;;
count(*)
460
explain format=json select count(*) from t2 where 2018 =  year(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.064079124,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 452,
          &quot;cost&quot;: 0.064079124,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a between &#039;2018-01-01 00:00:00&#039; and &#039;2018-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
#
# &quot;DATE(datetime_col) CMP date_value&quot;, basic checks
#
select count(*) from t2 where date(a) &lt; &#039;2017-06-01&#039;;
count(*)
190
select count(*) from t2 where a &lt; &#039;2017-06-01&#039;;
count(*)
190
explain format=json select count(*) from t2 where date(a) &lt; &#039;2017-06-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.027058904,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 186,
          &quot;cost&quot;: 0.027058904,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt; &#039;2017-06-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where date(a) &lt;= &#039;2017-06-03&#039;;
count(*)
200
select count(*) from t2 where a &lt; &#039;2017-06-04&#039;;
count(*)
200
explain format=json select count(*) from t2 where date(a) &lt;= &#039;2017-06-03&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.028441604,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 196,
          &quot;cost&quot;: 0.028441604,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt;= &#039;2017-06-03 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where date(a) &gt; &#039;2018-06-01&#039;;
count(*)
350
select count(*) from t2 where a &gt;= &#039;2018-06-02&#039;;
count(*)
350
explain format=json select count(*) from t2 where date(a) &gt; &#039;2018-06-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.051773094,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 363,
          &quot;cost&quot;: 0.051773094,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt; &#039;2018-06-01 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where date(a) &gt;= &#039;2018-06-01&#039;;
count(*)
350
select count(*) from t2 where a &gt;= &#039;2018-06-01&#039;;
count(*)
350
explain format=json select count(*) from t2 where date(a) &gt;= &#039;2018-06-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.051773094,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 363,
          &quot;cost&quot;: 0.051773094,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt;= &#039;2018-06-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where date(a) = &#039;2017-06-02&#039;;
count(*)
10
select count(*) from t2 where a &gt;= &#039;2017-06-02&#039; and a &lt; &#039;2017-06-03&#039;;
count(*)
10
explain format=json select count(*) from t2 where date(a) = &#039;2017-06-02&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002723384,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 10,
          &quot;cost&quot;: 0.002723384,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a between &#039;2017-06-02 00:00:00&#039; and &#039;2017-06-02 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
#
# &quot;DATE(datetime_col) CMP date_value&quot;, reverse order
#
select count(*) from t2 where &#039;2017-06-01&#039; &gt; date(a);
count(*)
190
select count(*) from t2 where &#039;2017-06-01&#039; &gt; a;
count(*)
190
explain format=json select count(*) from t2 where &#039;2017-06-01&#039; &gt; date(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.027058904,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 186,
          &quot;cost&quot;: 0.027058904,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt; &#039;2017-06-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where &#039;2017-06-03&#039; &gt;= date(a);
count(*)
200
select count(*) from t2 where &#039;2017-06-03&#039; &gt;= a;
count(*)
200
explain format=json select count(*) from t2 where &#039;2017-06-03&#039; &gt;= date(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.028441604,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 196,
          &quot;cost&quot;: 0.028441604,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &lt;= &#039;2017-06-03 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where &#039;2018-06-01&#039; &lt; date(a);
count(*)
350
select count(*) from t2 where &#039;2018-06-02&#039; &lt;= a;
count(*)
350
explain format=json select count(*) from t2 where &#039;2018-06-01&#039; &lt; date(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.051773094,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 363,
          &quot;cost&quot;: 0.051773094,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt; &#039;2018-06-01 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where &#039;2018-06-01&#039; &lt;= date(a);
count(*)
350
select count(*) from t2 where &#039;2018-06-01&#039; &lt;= a;
count(*)
350
explain format=json select count(*) from t2 where &#039;2018-06-01&#039; &lt;= date(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.051773094,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 363,
          &quot;cost&quot;: 0.051773094,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a &gt;= &#039;2018-06-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where &#039;2017-06-02&#039; = date(a);
count(*)
10
select count(*) from t2 where a &gt;= &#039;2017-06-02&#039; and a &lt; &#039;2017-06-03&#039;;
count(*)
10
explain format=json select count(*) from t2 where &#039;2017-06-02&#039; = date(a);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002723384,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 10,
          &quot;cost&quot;: 0.002723384,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a between &#039;2017-06-02 00:00:00&#039; and &#039;2017-06-02 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
# Check rewrite of a more complicated query
explain format=json select * from t2 as t21 force index(a),
t2 as t22 force index(a)
where year(t21.a) &lt; 2018 and t21.b &gt; &#039;2017-11-01&#039;
  and date(t22.a) &gt;= &#039;2017-02-01&#039; and t22.b &gt; &#039;2017-11-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 24.30027923,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t21&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 451,
          &quot;cost&quot;: 0.558056003,
          &quot;filtered&quot;: 56,
          &quot;index_condition&quot;: &quot;t21.a &lt; &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;attached_condition&quot;: &quot;t21.b &gt; &#039;2017-11-01&#039;&quot;
        }
      },
      {
        &quot;block-nl-join&quot;: {
          &quot;table&quot;: {
            &quot;table_name&quot;: &quot;t22&quot;,
            &quot;access_type&quot;: &quot;range&quot;,
            &quot;possible_keys&quot;: [&quot;a&quot;],
            &quot;key&quot;: &quot;a&quot;,
            &quot;key_length&quot;: &quot;6&quot;,
            &quot;used_key_parts&quot;: [&quot;a&quot;],
            &quot;loops&quot;: 252.56,
            &quot;rows&quot;: 961,
            &quot;cost&quot;: 23.74222323,
            &quot;filtered&quot;: 56,
            &quot;index_condition&quot;: &quot;t22.a &gt;= &#039;2017-02-01 00:00:00&#039;&quot;,
            &quot;attached_condition&quot;: &quot;t22.a &gt;= &#039;2017-02-01 00:00:00&#039; and t22.b &gt; &#039;2017-11-01&#039;&quot;
          },
          &quot;buffer_type&quot;: &quot;flat&quot;,
          &quot;buffer_size&quot;: &quot;7Kb&quot;,
          &quot;join_type&quot;: &quot;BNL&quot;,
          &quot;attached_condition&quot;: &quot;t22.b &gt; &#039;2017-11-01&#039;&quot;
        }
      }
    ]
  }
}
#
# Incorrect const values processing (no rewrite is possible)
#
explain format=json select * from t2 where year(a) = -1;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) = -1&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &gt; -5;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &gt; -5&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &lt; -1;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &lt; -1&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &lt;= 10000;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &lt;= 10000&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &gt;= 10020;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &gt;= 10020&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where date(a) = &#039;10000-01-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;cast(t2.a as date) = &#039;10000-01-01&#039;&quot;
        }
      }
    ]
  }
}
Warnings:
Warning	1292	Incorrect datetime value: &#039;10000-01-01&#039;
explain format=json select * from t2 where date(a) &lt; &#039;-1-01-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;cast(t2.a as date) &lt; &#039;-1-01-01&#039;&quot;
        }
      }
    ]
  }
}
Warnings:
Warning	1292	Incorrect datetime value: &#039;-1-01-01&#039;
#
# Try DATE function and DATE (not DATETIME) column:
#
select count(*) from t2 where date(b)&lt; &#039;2017-06-03&#039;;
count(*)
220
select count(*) from t2 where b &lt; &#039;2017-06-03&#039;;
count(*)
220
explain format=json select count(*) from t2 where date(b)&lt; &#039;2017-06-03&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.031207004,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;4&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 216,
          &quot;cost&quot;: 0.031207004,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.b &lt; &#039;2017-06-03&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
select count(*) from t2 where date(b)= &#039;2017-06-04&#039;;
count(*)
10
select count(*) from t2 where b &gt;= &#039;2017-06-04&#039; and b &lt; &#039;2017-06-05&#039;;
count(*)
10
explain format=json select count(*) from t2 where date(b)= &#039;2017-06-04&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002723384,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;4&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 10,
          &quot;cost&quot;: 0.002723384,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.b between &#039;2017-06-04&#039; and &#039;2017-06-04&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
#
# Check actual query results
#
insert into t2 values (10001,&#039;2006-12-31 23:59:59&#039;,&#039;2006-12-31&#039;);
insert into t2 values (10002,&#039;2007-01-01 00:00:00&#039;,&#039;2007-01-01&#039;);
insert into t2 values (10003,&#039;2007-12-31 23:59:59&#039;,&#039;2007-12-31&#039;);
insert into t2 values (10004,&#039;2008-01-01 00:00:00&#039;,&#039;2008-01-01&#039;);
explain format=json
select * from t2 force index(b) where year(b)=2007;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.005042291,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;4&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 3,
          &quot;cost&quot;: 0.005042291,
          &quot;filtered&quot;: 100,
          &quot;index_condition&quot;: &quot;t2.b between &#039;2007-01-01&#039; and &#039;2007-12-31&#039;&quot;
        }
      }
    ]
  }
}
select * from t2 force index(b) where year(b)=2007;
pk	a	b
10002	2007-01-01 00:00:00	2007-01-01
10003	2007-12-31 23:59:59	2007-12-31
insert into t2 values (10010,&#039;2006-12-31 00:00:00&#039;,&#039;2006-12-31&#039;);
insert into t2 values (10011,&#039;2006-12-30 23:59:59&#039;,&#039;2006-12-30&#039;);
explain format=json
select * from t2 force index(a) where date(a)=&#039;2006-12-31&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.005042291,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 3,
          &quot;cost&quot;: 0.005042291,
          &quot;filtered&quot;: 100,
          &quot;index_condition&quot;: &quot;t2.a between &#039;2006-12-31 00:00:00&#039; and &#039;2006-12-31 23:59:59&#039;&quot;
        }
      }
    ]
  }
}
select * from t2 force index(a) where date(a)=&#039;2006-12-31&#039;;
pk	a	b
10010	2006-12-31 00:00:00	2006-12-31
10001	2006-12-31 23:59:59	2006-12-31
#
# Test the TIMESTAMP column
#
create table t3 (a timestamp, b date, key(a));
# Insert data starting from 2016 since that year had a leap second
# (https://en.wikipedia.org/wiki/Leap_second)
set time_zone=&quot;UTC&quot;;
insert into t3
select
timestampadd(hour, B.a, date_add(&#039;2016-01-01&#039;, interval A.a*8 day)),
date_add(&#039;2016-01-01&#039;, interval A.a*7 day)
from t1 A, t0 B;
# Results of those two queries must be equal:
select count(*) from t3 force index(a) where year(a)= 2016;
count(*)
460
# The result must be the same as this query&#039;s:
select count(*) from t3 force index(a) where a &gt;= &#039;2016-01-01 00:00:00&#039;
    and a &lt;= &#039;2016-12-31 23:59:59.999999&#039;;
count(*)
460
explain format=json
select count(*) from t3 force index(a) where year(a)= 2016;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.064493934,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t3&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;5&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 455,
          &quot;cost&quot;: 0.064493934,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t3.a between &#039;2016-01-01 00:00:00&#039; and &#039;2016-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
set time_zone= @@global.time_zone;
#
# Incorrect const values processing (no rewrite is possible)
#
explain format=json select * from t2 where year(a) = -1;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) = -1&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &gt; -5;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &gt; -5&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &lt; -1;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &lt; -1&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &lt;= 10000;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &lt;= 10000&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where year(a) &gt;= 10020;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t2.a) &gt;= 10020&quot;
        }
      }
    ]
  }
}
explain format=json select * from t2 where date(a) = &#039;10000-01-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;cast(t2.a as date) = &#039;10000-01-01&#039;&quot;
        }
      }
    ]
  }
}
Warnings:
Warning	1292	Incorrect datetime value: &#039;10000-01-01&#039;
explain format=json select * from t2 where date(a) &lt; &#039;-1-01-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.1671618,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1000,
          &quot;cost&quot;: 0.1671618,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;cast(t2.a as date) &lt; &#039;-1-01-01&#039;&quot;
        }
      }
    ]
  }
}
Warnings:
Warning	1292	Incorrect datetime value: &#039;-1-01-01&#039;
#
# Composite indexes
#
create table t4 (a datetime, b int, key(a, b)) ;
insert into t4 values (&#039;2009-11-29 13:43:32&#039;, 2);
insert into t4 values (&#039;2009-10-15 12:15:11&#039;, 2);
insert into t4 values (&#039;2009-09-01 15:55:00&#039;, 3);
insert into t4 values (&#039;2009-08-23 12:07:47&#039;, 4);
explain format=json select * from t4 where year(a) = 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001893764,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.001893764,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.a between &#039;2009-01-01 00:00:00&#039; and &#039;2009-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where year(a) &gt;= 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001893764,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.001893764,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.a &gt;= &#039;2009-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where year(a) &lt; 2018;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001893764,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.001893764,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.a &lt; &#039;2018-01-01 00:00:00&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where date(a) = &#039;2009-12-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001478954,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1,
          &quot;cost&quot;: 0.001478954,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.a between &#039;2009-12-01 00:00:00&#039; and &#039;2009-12-01 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where b = 2 and year(a) = 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001893764,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;11&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;, &quot;b&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.001893764,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.b = 2 and t4.a between &#039;2009-01-01 00:00:00&#039; and &#039;2009-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where b = 10 and year(a) &gt; 2001;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001893764,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.001893764,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.b = 10 and t4.a &gt; &#039;2001-12-31 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t4 where b = 2 and date(a) = &#039;2009-11-02&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001478954,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;11&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;, &quot;b&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1,
          &quot;cost&quot;: 0.001478954,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.b = 2 and t4.a between &#039;2009-11-02 00:00:00&#039; and &#039;2009-11-02 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
# Reverse order of fields in the index
create table t5 (a datetime, b int, c varchar(20), key(b, a));
insert into t5 values (&#039;2009-11-29 13:43:32&#039;, 2, &#039;abc&#039;);
insert into t5 values (&#039;2009-10-15 12:15:11&#039;, 2, &#039;def&#039;);
insert into t5 values (&#039;2009-09-01 15:55:00&#039;, 3, &#039;gfd&#039;);
insert into t5 values (&#039;2009-08-23 12:07:47&#039;, 4, &#039;xyz&#039;);
explain format=json select * from t5 where year(a) = 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.01066122,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t5&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.01066122,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t5.a between &lt;cache&gt;(&#039;2009-01-01 00:00:00&#039;) and &lt;cache&gt;(&#039;2009-12-31 23:59:59&#039;)&quot;
        }
      }
    ]
  }
}
explain format=json select * from t4 where date(a) = &#039;2009-12-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.001478954,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t4&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1,
          &quot;cost&quot;: 0.001478954,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t4.a between &#039;2009-12-01 00:00:00&#039; and &#039;2009-12-01 23:59:59&#039;&quot;,
          &quot;using_index&quot;: true
        }
      }
    ]
  }
}
explain format=json select * from t5 where b = 2 and year(a) = 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002037211,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t5&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;11&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;, &quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 2,
          &quot;cost&quot;: 0.002037211,
          &quot;filtered&quot;: 70,
          &quot;index_condition&quot;: &quot;t5.b = 2 and t5.a between &#039;2009-01-01 00:00:00&#039; and &#039;2009-12-31 23:59:59&#039;&quot;
        }
      }
    ]
  }
}
explain format=json select * from t5 where b = 10 and year(a) &gt; 2001;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002574553,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t5&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;11&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;, &quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1,
          &quot;cost&quot;: 0.002574553,
          &quot;filtered&quot;: 100,
          &quot;index_condition&quot;: &quot;t5.b = 10 and t5.a &gt; &#039;2001-12-31 23:59:59&#039;&quot;
        }
      }
    ]
  }
}
explain format=json select * from t5 where b = 3 and date(a) &gt; &#039;2009-09-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.002574553,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t5&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;b&quot;],
          &quot;key&quot;: &quot;b&quot;,
          &quot;key_length&quot;: &quot;11&quot;,
          &quot;used_key_parts&quot;: [&quot;b&quot;, &quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 1,
          &quot;cost&quot;: 0.002574553,
          &quot;filtered&quot;: 100,
          &quot;index_condition&quot;: &quot;t5.b = 3 and t5.a &gt; &#039;2009-09-01 23:59:59&#039;&quot;
        }
      }
    ]
  }
}
#
# No rewrite for a non-indexed column
#
create table t6 (a datetime);
insert into t6 values (&#039;2009-11-29 13:43:32&#039;);
insert into t6 values (&#039;2009-10-15 12:15:11&#039;);
insert into t6 values (&#039;2009-09-01 15:55:00&#039;);
insert into t6 values (&#039;2009-08-23 12:07:47&#039;);
explain format=json select * from t6 where year(a) = 2009;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.01066122,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t6&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.01066122,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;year(t6.a) = 2009&quot;
        }
      }
    ]
  }
}
explain format=json select * from t6 where date(a) = &#039;2009-12-01&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.01066122,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t6&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 4,
          &quot;cost&quot;: 0.01066122,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;cast(t6.a as date) = &#039;2009-12-01&#039;&quot;
        }
      }
    ]
  }
}
drop table t0,t1,t2,t3,t4,t5,t6;
drop view v1;
drop procedure sp;
#
# MDEV-30946 Index usage for DATE(datetime_column) = const
#            does not work for DELETE and UPDATE
#
create table t1 (pk int primary key, a datetime, c int, key(a));
insert into t1 (pk,a,c) values (1,&#039;2009-11-29 13:43:32&#039;, 2);
insert into t1 (pk,a,c) values (2,&#039;2009-11-29 03:23:32&#039;, 2);
insert into t1 (pk,a,c) values (3,&#039;2009-10-16 05:56:32&#039;, 2);
insert into t1 (pk,a,c) values (4,&#039;2010-11-29 13:43:32&#039;, 2);
insert into t1 (pk,a,c) values (5,&#039;2010-10-16 05:56:32&#039;, 2);
insert into t1 (pk,a,c) values (6,&#039;2011-11-29 13:43:32&#039;, 2);
insert into t1 (pk,a,c) values (7,&#039;2012-10-16 05:56:32&#039;, 2);
# YEAR() conditions, UPDATE
explain format=json update t1 set c = 0 where year(a) = 2010;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;update&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 2,
      &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;
    }
  }
}
update t1 set c = 0 where year(a) = 2010;
select * from t1;
pk	a	c
1	2009-11-29 13:43:32	2
2	2009-11-29 03:23:32	2
3	2009-10-16 05:56:32	2
4	2010-11-29 13:43:32	0
5	2010-10-16 05:56:32	0
6	2011-11-29 13:43:32	2
7	2012-10-16 05:56:32	2
explain format=json update t1 set c = 1
where c &lt; (select count(*) from t1 where year(a) = 2010);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.011130435,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t1&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 7,
          &quot;cost&quot;: 0.011130435,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t1.c &lt; (subquery#2)&quot;
        }
      }
    ],
    &quot;subqueries&quot;: [
      {
        &quot;query_block&quot;: {
          &quot;select_id&quot;: 2,
          &quot;cost&quot;: 0.001617224,
          &quot;nested_loop&quot;: [
            {
              &quot;table&quot;: {
                &quot;table_name&quot;: &quot;t1&quot;,
                &quot;access_type&quot;: &quot;range&quot;,
                &quot;possible_keys&quot;: [&quot;a&quot;],
                &quot;key&quot;: &quot;a&quot;,
                &quot;key_length&quot;: &quot;6&quot;,
                &quot;used_key_parts&quot;: [&quot;a&quot;],
                &quot;loops&quot;: 1,
                &quot;rows&quot;: 2,
                &quot;cost&quot;: 0.001617224,
                &quot;filtered&quot;: 100,
                &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;,
                &quot;using_index&quot;: true
              }
            }
          ]
        }
      }
    ]
  }
}
explain format=json update t1 set c = 0
where year(a) = 2010 and c &lt; (select count(*) from t1 where year(a) = 2010);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.003808422,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t1&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 2,
          &quot;cost&quot;: 0.003808422,
          &quot;filtered&quot;: 100,
          &quot;index_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;,
          &quot;attached_condition&quot;: &quot;t1.c &lt; (subquery#2)&quot;
        }
      }
    ],
    &quot;subqueries&quot;: [
      {
        &quot;query_block&quot;: {
          &quot;select_id&quot;: 2,
          &quot;cost&quot;: 0.001617224,
          &quot;nested_loop&quot;: [
            {
              &quot;table&quot;: {
                &quot;table_name&quot;: &quot;t1&quot;,
                &quot;access_type&quot;: &quot;range&quot;,
                &quot;possible_keys&quot;: [&quot;a&quot;],
                &quot;key&quot;: &quot;a&quot;,
                &quot;key_length&quot;: &quot;6&quot;,
                &quot;used_key_parts&quot;: [&quot;a&quot;],
                &quot;loops&quot;: 1,
                &quot;rows&quot;: 2,
                &quot;cost&quot;: 0.001617224,
                &quot;filtered&quot;: 100,
                &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;,
                &quot;using_index&quot;: true
              }
            }
          ]
        }
      }
    ]
  }
}
# Multi-table update
create table t2 (a int);
insert into t2 values (4),(5),(6);
explain format=json update t1, t2 set c = 0
where year(t1.a) = 2010 and t1.c = t2.a;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.014992165,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t1&quot;,
          &quot;access_type&quot;: &quot;range&quot;,
          &quot;possible_keys&quot;: [&quot;a&quot;],
          &quot;key&quot;: &quot;a&quot;,
          &quot;key_length&quot;: &quot;6&quot;,
          &quot;used_key_parts&quot;: [&quot;a&quot;],
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 2,
          &quot;cost&quot;: 0.003808422,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;
        }
      },
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t2&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 2,
          &quot;rows&quot;: 3,
          &quot;cost&quot;: 0.011183743,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t2.a = t1.c&quot;
        }
      }
    ]
  }
}
prepare stmt from &quot;update t1 set c = 0 where year(a) = 2010&quot;;
execute stmt;
execute stmt;
# YEAR() conditions, DELETE
explain format=json delete from t1 where year(a) = 2010;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;delete&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 2,
      &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;
    }
  }
}
delete from t1 where year(a) = 2010;
select * from t1;
pk	a	c
1	2009-11-29 13:43:32	2
2	2009-11-29 03:23:32	2
3	2009-10-16 05:56:32	2
6	2011-11-29 13:43:32	2
7	2012-10-16 05:56:32	2
explain format=json delete from t1
where c &lt; (select count(*) from t1 where year(a) = 2010);
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;cost&quot;: 0.010817625,
    &quot;nested_loop&quot;: [
      {
        &quot;table&quot;: {
          &quot;table_name&quot;: &quot;t1&quot;,
          &quot;access_type&quot;: &quot;ALL&quot;,
          &quot;loops&quot;: 1,
          &quot;rows&quot;: 5,
          &quot;cost&quot;: 0.010817625,
          &quot;filtered&quot;: 100,
          &quot;attached_condition&quot;: &quot;t1.c &lt; (subquery#2)&quot;
        }
      }
    ],
    &quot;subqueries&quot;: [
      {
        &quot;query_block&quot;: {
          &quot;select_id&quot;: 2,
          &quot;cost&quot;: 0.001478954,
          &quot;nested_loop&quot;: [
            {
              &quot;table&quot;: {
                &quot;table_name&quot;: &quot;t1&quot;,
                &quot;access_type&quot;: &quot;range&quot;,
                &quot;possible_keys&quot;: [&quot;a&quot;],
                &quot;key&quot;: &quot;a&quot;,
                &quot;key_length&quot;: &quot;6&quot;,
                &quot;used_key_parts&quot;: [&quot;a&quot;],
                &quot;loops&quot;: 1,
                &quot;rows&quot;: 1,
                &quot;cost&quot;: 0.001478954,
                &quot;filtered&quot;: 100,
                &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-01-01 00:00:00&#039; and &#039;2010-12-31 23:59:59&#039;&quot;,
                &quot;using_index&quot;: true
              }
            }
          ]
        }
      }
    ]
  }
}
delete from t1 where c &lt; (select count(*) from t1 where year(a) = 2010);
prepare stmt from &quot;delete from t1 where year(a) = 2009&quot;;
execute stmt;
execute stmt;
select * from t1;
pk	a	c
6	2011-11-29 13:43:32	2
7	2012-10-16 05:56:32	2
# DATE() conditions, UPDATE
explain format=json update t1 set c = 0 where date(a) = &#039;2010-10-16&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;update&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 1,
      &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-10-16 00:00:00&#039; and &#039;2010-10-16 23:59:59&#039;&quot;
    }
  }
}
explain format=json update t1 set c = 0 where date(a) &lt;= &#039;2011-10-16&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;update&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 1,
      &quot;attached_condition&quot;: &quot;t1.a &lt;= &#039;2011-10-16 23:59:59&#039;&quot;
    }
  }
}
prepare stmt from &quot;update t1 set c = 3 where date(a) = &#039;2011-11-29&#039;&quot;;
execute stmt;
execute stmt;
select * from t1;
pk	a	c
6	2011-11-29 13:43:32	3
7	2012-10-16 05:56:32	2
# DATE() conditions, DELETE
explain format=json delete from t1 where date(a) = &#039;2010-10-16&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;delete&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 1,
      &quot;attached_condition&quot;: &quot;t1.a between &#039;2010-10-16 00:00:00&#039; and &#039;2010-10-16 23:59:59&#039;&quot;
    }
  }
}
explain format=json delete from t1 where date(a) &lt;= &#039;2011-10-16&#039;;
EXPLAIN
{
  &quot;query_block&quot;: {
    &quot;select_id&quot;: 1,
    &quot;table&quot;: {
      &quot;delete&quot;: 1,
      &quot;table_name&quot;: &quot;t1&quot;,
      &quot;access_type&quot;: &quot;range&quot;,
      &quot;possible_keys&quot;: [&quot;a&quot;],
      &quot;key&quot;: &quot;a&quot;,
      &quot;key_length&quot;: &quot;6&quot;,
      &quot;used_key_parts&quot;: [&quot;a&quot;],
      &quot;rows&quot;: 1,
      &quot;attached_condition&quot;: &quot;t1.a &lt;= &#039;2011-10-16 23:59:59&#039;&quot;
    }
  }
}
prepare stmt from &quot;delete from t1 where date(a) &lt;= &#039;2012-01-01&#039;&quot;;
execute stmt;
execute stmt;
select * from t1;
pk	a	c
7	2012-10-16 05:56:32	2
# Test partition pruning
create table t3 (
a datetime,
key(a)
) partition by range(year(a)) (
partition p0 values less than (2022),
partition p1 values less than (MAXVALUE)
);
insert into t3
select date_add(&#039;2020-01-01&#039;, interval seq*10 day)
from seq_1_to_100;
# Must be only &quot;p0&quot; partition
explain partitions select * from t3 where year(a) = 2020;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	p0	range	a	a	6	NULL	36	Using where; Using index
explain partitions delete from t3 where year(a) = 2020;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	p0	ALL	a	NULL	NULL	NULL	73	Using where
explain partitions update t3 set a = a + 1 where year(a) = 2020;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	p0	ALL	a	NULL	NULL	NULL	73	Using where; Using buffer
drop tables t1,t2,t3;
</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2Fmain'>🔙 Back</a></p>