spetrunia

--- /tmp/log-on.txt     2024-02-09 13:07:36.292363361 +0300
+++ /tmp/log-off.txt    2024-02-09 13:08:34.117609771 +0300
@@ -1,3 +1,6 @@
+MariaDB [test]> set optimizer_use_condition_selectivity=1;
+Query OK, 0 rows affected (0.000 sec)
+
 MariaDB [test]> SET optimizer_trace='enabled=on';
 Query OK, 0 rows affected (0.000 sec)

@@ -207,12 +210,12 @@

spetrunia

--- 10.6-distinct-review-cp/sql/sql_select.cc	2024-02-06 13:07:15.670758859 +0300
+++ 10.6-distinct-review/sql/sql_select.cc	2024-02-06 13:04:14.656527307 +0300
@@ -27145,8 +27145,22 @@
 }
 
 
+/*
+  @brief
+    Setup aggregate functions.
+

spetrunia

Trying this on MySQL 8.0.36-debug

create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table twenty(a int);
insert into twenty select a from ten;
insert into twenty select a+10 from ten;

The default join order is:

spetrunia

# Tried on 8.0.35, 8.0.36-debug

create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table twenty(a int);
insert into twenty select a from ten;
insert into twenty select a+10 from ten;

explain 

spetrunia

analyze format=json SELECT COUNT(DISTINCT   (c0)) FROM autoinc;
11:26
(gdb) wher
  #0  tree_insert (tree=tree@entry=0x7fff28016d78, key=0x7fff2804c361, key_size=key_size@entry=0, custom_arg=0x7fff28016b18) at /home/psergey/dev-git2/10.6-dbg-review/mysys/tree.c:245
  #1  0x0000555555f675a7 in Unique::unique_add (ptr=<optimized out>, this=0x7fff28016c50) at /home/psergey/dev-git2/10.6-dbg-review/sql/uniques.h:66
  #2  Aggregator_distinct::add (this=0x7fff28016ae0) at /home/psergey/dev-git2/10.6-dbg-review/sql/item_sum.cc:1017
  #3  0x0000555555cb1148 in Item_sum::aggregator_add (this=0x7fff28013570) at /home/psergey/dev-git2/10.6-dbg-review/sql/item_sum.h:571
  #4  Item_sum::reset_and_add (this=0x7fff28013570) at /home/psergey/dev-git2/10.6-dbg-review/sql/item_sum.h:452
  #5  init_sum_functions (func_ptr=0x7fff28014fe0, end_ptr=0x7fff28014fe8) at /home/psergey/dev-git2/10.6-dbg-review/sql/sql_select.cc:27186
  #6  0x0000555555cdb864 in end_send_group (join=0x7fff280147d0, join_tab=<optimized out>, end_of_record

spetrunia

diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 220d2f5432d..3c7ccbb132a 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1220,7 +1220,8 @@ void thd_gmt_sec_to_TIME(MYSQL_THD thd, MYSQL_TIME *ltime, my_time_t t)
 }
 
 
-/**
+/*

spetrunia

diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 220d2f5432d..3c7ccbb132a 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1220,7 +1220,8 @@ void thd_gmt_sec_to_TIME(MYSQL_THD thd, MYSQL_TIME *ltime, my_time_t t)
 }
 
 
-/**
+/*

spetrunia

    "table": {
      "table_name": "lineitem",
      "access_type": "range",
      "possible_keys": ["i_l_shipdate", "i_l_quantity"],
      "key": "i_l_quantity",
      "key_length": "9",
      "used_key_parts": ["l_quantity"],
      "loops": 1,
      "r_loops": 1,

spetrunia

(gdb) wher 1
  #0  Item_subselect::const_item (this=0x7fff0c019a70) at /home/psergey/dev-git2/10.5/sql/item_subselect.cc:1001
  (More stack frames follow...)
(gdb) p this
  $473 = (const Item_singlerow_subselect * const) 0x7fff0c019a70
(gdb) p this->with_recursive_reference
  $474 = false
(gdb) p dbug_print_item(this)
  [Thread 0x7fff3ffff700 (LWP 12798) exited]
  $475 = 0x555557a80420 <dbug_item_print_buf> "(subquery#6)"

spetrunia

Running this testcase:

--echo #
--echo # MDEV-29070 SIGSEGV in my_decimal::operator= and Assertion `0' failed
--echo # in Item_type_holder::val_decimal on SELECT
--echo #
  
...

WITH RECURSIVE x (x) AS (