--source include/have_innodb.inc

create table t1 (`c1` bigint, key (`c1`)) engine=innodb;

insert into t1 values (-2);

create table t2 ( `c2` double unsigned );

insert into t2 values (12.991);

let $1= 10000;

while ($1)

{

  SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`

         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`

              ON (EXISTS (SELECT `c2` FROM `t2`)) AND

                 (NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`));

  dec $1;

}

drop table t1,t2;

231214 11:22:49 [ERROR] mysqld got signal 11 ;

Server version: 10.4.33-MariaDB-debug-log source revision: 9f5078a1d79031c4a781d378af18df9c8c9d2321

sql/signal_handler.cc:235(handle_fatal_signal)[0x558d689effd3]

sigaction.c:0(__restore_rt)[0x7f9a6f82c420]

sql/sql_select.cc:7139(optimize_keyuse(JOIN*, st_dynamic_array*))[0x558d6831ceba]

sql/sql_select.cc:5708(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x558d6830fb03]

sql/sql_select.cc:2388(JOIN::optimize_inner())[0x558d682ed791]

sql/sql_select.cc:1731(JOIN::optimize())[0x558d682e67a7]

sql/sql_derived.cc:1029(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x558d681529ef]

sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x558d6814d19a]

sql/sql_select.cc:2221(JOIN::optimize_inner())[0x558d682ebd0c]

sql/sql_select.cc:1731(JOIN::optimize())[0x558d682e67a7]

sql/sql_select.cc:4832(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x558d68307791]

sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x558d682d81f8]

sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x558d6823f1fd]

sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x558d6822c81d]

sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x558d68248779]

sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x558d6821e925]

sql/sql_parse.cc:1378(do_command(THD*))[0x558d6821b450]

sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x558d686304c6]

sql/sql_connect.cc:1324(handle_one_connection)[0x558d6862fd6a]

perfschema/pfs.cc:1871(pfs_spawn_thread)[0x558d692cde4c]

nptl/pthread_create.c:478(start_thread)[0x7f9a6f820609]

Query (0x62b0000a1290): SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `t1`

         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `t3`

              ON (EXISTS (SELECT `c2` FROM `t2`)) AND

(NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`))

EXPLAIN EXTENDED 

SELECT f6 FROM (SELECT DISTINCT `c1` AS `f6` FROM `t1`) AS `dt1`

         JOIN (SELECT CHARSET(`c2`) AS `f5` FROM `t2`) AS `dt3`

              ON (EXISTS (SELECT `c2` FROM `t2`)) AND

(NOT ((CHARSET(`f5`)) NOT BETWEEN `f6` AND `f6`));

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	

1	PRIMARY	<derived2>	ref	key0	key0	9	const	0	0.00	Using where

4	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	

3	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	100.00	

2	DERIVED	t1	range	c1	c1	9	NULL	2	50.00	Using where; Using index for group-by

Warnings:

Note	1003	/* select#1 */ select `dt1`.`f6` AS `f6` from (/* select#2 */ select distinct `test`.`t1`.`c1` AS `f6` from `test`.`t1` where <cache>(charset('binary')) between `test`.`t1`.`c1` and `test`.`t1`.`c1`) `dt1` where <cache>(charset('binary')) between `dt1`.`f6` and `dt1`.`f6`

1   optimize_keyuse             sql_select.cc    7189  0x555555f24f48 

2   make_join_statistics        sql_select.cc    5751  0x555555f2085e 

3   JOIN::optimize_inner        sql_select.cc    2386  0x555555f142f1 

4   JOIN::optimize              sql_select.cc    1740  0x555555f11c24 

5   mysql_derived_optimize      sql_derived.cc   1029  0x555555e60377 

6   mysql_handle_single_derived sql_derived.cc   200   0x555555e5dd99 

7   JOIN::optimize_inner        sql_select.cc    2204  0x555555f1387d 

8   JOIN::optimize              sql_select.cc    1740  0x555555f11c24 

9   mysql_select                sql_select.cc    4868  0x555555f1d66f 

10  mysql_explain_union         sql_select.cc    28125 0x555555f5efa7 

11  execute_sqlcom_select       sql_parse.cc     6356  0x555555ecada9 

12  mysql_execute_command       sql_parse.cc     4022  0x555555ec1f97 

... <More>                                                            

JOIN *join= derived->get_unit()->first_select()->join;

EXPLAIN EXTENDED 

     SELECT dt1_c1 FROM 

       (SELECT c1 AS dt1_c1 FROM t1) AS dt1

       JOIN 

       (SELECT 1 AS dt2_c2 FROM t2) AS dt2

          ON CHARSET(dt2_c2) BETWEEN dt1_c1 AND dt1_c1;

Warnings:

Note	1003	

/* select#1 */ select `dt1`.`dt1_c1` AS `dt1_c1` 

   from 

   (/* select#2 */ select `test`.`t1`.`c1` AS `dt1_c1` from `test`.`t1` 

     where <cache>(charset(1)) between `test`.`t1`.`c1` and `test`.`t1`.`c1`) `dt1` 

   where <cache>(charset(1)) between `dt1`.`dt1_c1` and `dt1`.`dt1_c1`

EXPLAIN EXTENDED 

SELECT dt1_c1 FROM 

(SELECT c1 AS dt1_c1 FROM t1) AS dt1

JOIN 

(SELECT 1 AS dt2_c2 FROM t2) AS dt2

ON LENGTH(dt2_c2) BETWEEN dt1_c1 AND dt1_c1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	

1	PRIMARY	<derived2>	ref	key0	key0	9	const	0	0.00	Using where

3	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	100.00	

2	DERIVED	t1	index	NULL	c1	9	NULL	1	100.00	Using index

Warnings:

Note	1003	

/* select#1 */ select `dt1`.`dt1_c1` AS `dt1_c1` 

  from 

  (/* select#2 */ select `test`.`t1`.`c1` AS `dt1_c1` from `test`.`t1`) `dt1` 

     where <cache>(octet_length(1)) between `dt1`.`dt1_c1` and `dt1`.`dt1_c1`

/* select#1 */ select `dt1`.`dt1_c1` AS `dt1_c1` 

   from 

   (/* select#2 */ select `test`.`t1`.`c1` AS `dt1_c1` from `test`.`t1` 

     where <cache>(charset(1)) between `test`.`t1`.`c1` and `test`.`t1`.`c1`) `dt1` 

   where <cache>(charset(1)) between `dt1`.`dt1_c1` and `dt1`.`dt1_c1`

bool const_item() const { return true; }

commit aa1002a35cefcc1851cbfb6b6b60463bda6f9aa3

Author: Alexander Barkov <bar@mariadb.org>, Wed Sep 2 08:20:49 2015 +0400 (9 years ago)

MDEV-8723 Wrong result for SELECT..WHERE COLLATION(a)='binary' AND a='a'

create or replace table t1(a int default 1);

insert into t1 values (1),(2);

select * from t1 where a=default(a);

--source include/have_innodb.inc

CREATE TABLE t1 (c1 INT, KEY (c1)) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (c2 INT);

INSERT INTO t2 VALUES (1);

SET optimizer_switch='derived_merge=off';

set optimizer_trace=1;

EXPLAIN EXTENDED

     SELECT dt1_c1 FROM 

       (SELECT c1 AS dt1_c1 FROM t1) AS dt1

       JOIN 

       (SELECT 1 AS dt2_c2 FROM t2) AS dt2

          ON CHARSET(dt2_c2) = dt1_c1;

if (keyuse->used_tables &

	(map= (keyuse->used_tables & ~join->const_table_map &

	       ~OUTER_REF_TABLE_BIT)))

    {

      uint n_tables= my_count_bits(map);

      if (n_tables == 1)			// Only one table

      {

        DBUG_ASSERT(!(map & PSEUDO_TABLE_BITS)); // Must be a real table

        Table_map_iterator it(map);

        int tablenr= it.next_bit();

        DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END);

	TABLE *tmp_table=join->table[tablenr];

        if (tmp_table) // already created

          keyuse->ref_table_rows= MY_MAX(tmp_table->file->stats.records, 100);

      }

    }

String *Item_func_charset::val_str(String *str)

{

  DBUG_ASSERT(fixed());

  uint dummy_errors;

  CHARSET_INFO *cs= args[0]->charset_for_protocol();

  null_value= 0;

  str->copy(cs->cs_name.str, cs->cs_name.length,

            &my_charset_latin1, collation.collation, &dummy_errors);

  return str;

}

CHARSET_INFO *m_cached_charset_info;

LEX_CSTRING m_cached_charset_info;

  const char *csname;

  LEX_CSTRING cs_name;