spetrunia

MariaDB [test]> analyze format=json select * from t2 where b  between 300 and 500 and (b+1) >= (b+2)\G
*************************** 1. row ***************************
ANALYZE: {
  "query_optimization": {
    "r_total_time_ms": 0.341122797
  },
  "query_block": {
    "select_id": 1,
    "cost": 0.28158088,
    "r_loops": 1,

spetrunia

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t1 (a int, b int, c int, key(b)) partition by hash(a) partitions 10;
Query OK, 0 rows affected (0.025 sec)

MariaDB [test]> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,

spetrunia

diff --git a/storage/innobase/rem/rem0rec.cc b/storage/innobase/rem/rem0rec.cc
index f489669b408..ea87750e5bc 100644
--- a/storage/innobase/rem/rem0rec.cc
+++ b/storage/innobase/rem/rem0rec.cc
@@ -437,7 +437,7 @@ rec_init_offsets_comp_ordinary(
                        if (UNIV_UNLIKELY(len & 0x80) && DATA_BIG_COL(col)) {
                                /* 1exxxxxxx xxxxxxxx */
                                len <<= 8;
-                               len |= *lens--;
+                               len |= (rec_offs)*lens--;

spetrunia

> commit a49d2e4bc62507496b85c3499a423b5b4e321354
> Author: Monty <monty@mariadb.org>
> Date:   Fri Mar 31 19:35:04 2023 +0300
>
>     MDEV-6768 Wrong result with aggregate with join with no result set
>     
>     What happens is that end_send_group() is called with a const row but
>     without any rows matching the WHERE clause.
>     This last part is shown by 'join->first_record' not being set.
>     

spetrunia

diff -urp 10.5-review2/sql/sql_select.cc 10.5-review2-cl/sql/sql_select.cc
--- 10.5-review2/sql/sql_select.cc      2023-04-04 12:32:42.636312847 +0300
+++ 10.5-review2-cl/sql/sql_select.cc   2023-04-04 13:11:28.732023482 +0300
@@ -26998,7 +26998,7 @@ void inline JOIN::clear_sum_funcs()
 /*
   Prepare for returning 'empty row' when there is no matching row.
 
-  - Mark all tables with mark_as_null_with()
+  - Mark all tables with mark_as_null_row()
   - Make a copy of of all simple SELECT items

spetrunia

create table t1 (a date, b date, c date, key(a),key(b), key(c));
insert into t1 
select 
  date_add('2023-01-01', interval seq day),
  date_add('2023-01-01', interval seq day),
  date_add('2023-01-01', interval seq day)
from 
  seq_1_to_1000;

spetrunia

./sql/mysql_tzinfo_to_sql  /usr/share/zoneinfo/right/Europe/London right/Europe/London > london1.sql
./sql/mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/Europe/London > london2.sql

use mysql;
source london1.sql
source london2.sql

<restart the server>;

set time_zone='right/Europe/London';

spetrunia

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index b971c96cda2..3b6ec949ea3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3964,6 +3964,12 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
       Json_writer_object semijoin_strategy(thd);
       semijoin_strategy.add("semi_join_strategy","FirstMatch");
       Json_writer_array semijoin_plan(thd, "join_order");
+
+      bool one_table_and_buffer_allowed= false;

spetrunia

commit 432a4ebe5cd2ebf4d0fad79092e82e5d1a9f53ba
Author: Michael Widenius <monty@mariadb.org>
Date:   Wed May 18 22:17:32 2022 +0300

    Improve table pruning in optimizer with up to date key_dependent map

    Part of:
    MDEV-28073 Slow query performance in MariaDB when using many tables

    s->key_dependent has a list of tables that are compared with key fields

spetrunia

MariaDB [test]> explain SELECT SUM( l_partkey ) FROM region JOIN nation ON ( r_regionkey = n_regionkey ) LEFT JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) RIGHT JOIN lineitem ON ( ps_partkey = l_partkey AND ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) WHERE o_comment IS NOT NULL OR n_comment IS NOT NULL AND ps_availqty = l_quantity\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL