detele query crash:Item_field::fix_outer_field

sql/item.cc:5927(Item_field::fix_outer_field)

  sql/item.cc:6363(Item_field::fix_fields)

  sql/item_func.cc:394(Item_func::fix_fields)

  sql/sql_base.cc:9057(setup_conds)

  sql/sql_select.cc:1603(JOIN::prepare)

  sql/sql_cte.cc:1309(With_element::prepare_unreferenced)

  sql/sql_cte.cc:945(With_clause::prepare_unreferenced_elements)

-- ============================================================================

-- MariaDB Crash Reproduction Script

-- Date: 2026-01-31 18:29:56

-- Error: 2013 - Lost connection to MySQL server during query

-- Seed: 1769855117 (reproducible)

-- Query Number: 221

-- ============================================================================

-- ============================================================================

-- STEP 1: Environment Setup

-- ============================================================================

SET SQL_MODE = CONCAT(@@sql_mode, ',NO_ENGINE_SUBSTITUTION');

USE test5;

-- ============================================================================

-- STEP 2: Create Base Tables (mysql_1 to mysql_6)

-- ============================================================================

DROP TABLE IF EXISTS mysql_1;

CREATE TABLE `mysql_1` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

DROP TABLE IF EXISTS mysql_2;

CREATE TABLE `mysql_2` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

DROP TABLE IF EXISTS mysql_3;

CREATE TABLE `mysql_3` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

DROP TABLE IF EXISTS mysql_4;

CREATE TABLE `mysql_4` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

DROP TABLE IF EXISTS mysql_5;

CREATE TABLE `mysql_5` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

DROP TABLE IF EXISTS mysql_6;

CREATE TABLE `mysql_6` (

    `col_int` int,

    `col_varchar` varchar(2000),

    `col_date` date,

    `col_timestamp` timestamp,

    `col_numeric` numeric,

    KEY k2 (`col_int`,`col_date`),

    KEY k1 (`col_numeric`)

);

-- ============================================================================

-- STEP 3: Insert Test Data into Base Tables (60 rows total)

-- ============================================================================

-- Data for mysql_1 (10 rows)

INSERT IGNORE INTO mysql_1 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (NULL, 'j', '1991-06-11', '1984-03-11 01:00:33.045530', 7),

    (6, 'zusai', '2009-06-06', '2003-05-20 17:28:05.001240', 6),

    (2, 'english', '2011-08-20', '2008-08-16', 1),

    (4, 'q', '1981-03-26', '2030-05-03 23:43:54.025910', 5),

    (NULL, NULL, '2014-02-05', '2002-02-20', 1),

    (1, 'j', '2031-11-15', '1978-12-21', 2),

    (NULL, 'english', '1998-03-28', '2006-08-16', 4),

    (b'1', NULL, '2031-08-13 18:02:29.002946', '1981-03-21 02:01:30.012711', 2),

    (9, 'w', '2009-07-22 16:35:17.049287', '2000-09-20', 9),

    (2, 'usa', '2029-01-12 08:26:27.063419', '2029-07-23 16:16:27.001813', b'0');

-- Data for mysql_2 (10 rows)

INSERT IGNORE INTO mysql_2 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (6, 'sai', '2033-07-14', '1976-11-24', NULL),

    (b'1', 'a', '1989-09-04 21:45:08.025098', '2031-01-19 12:16:48.032877', NULL),

    (0, 'i', '2032-08-05 05:31:04.023640', '1975-04-20', 4),

    (b'0', 'english', '2034-03-16 21:29:22.061613', '1994-03-17 16:09:33.034099', b'0'),

    (2, NULL, '1977-07-07 23:20:27.062047', '1987-02-21', b'0'),

    (NULL, 'english', '1996-02-06 00:05:32.002400', '1989-03-07', 6),

    (b'1', 't', '1994-11-13', '2000-07-03', b'0'),

    (b'1', 'fxmk', '1999-12-06 23:41:05.000018', '1997-02-12', NULL),

    (8, NULL, '2013-01-26 22:31:37.039138', '2034-09-02 17:21:20.018633', 5),

    (1, 'xmk', '2007-04-11 04:15:17.006654', '1995-11-16', NULL);

-- Data for mysql_3 (10 rows)

INSERT IGNORE INTO mysql_3 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (4, 'b', '2020-09-18 17:51:28.054362', '1976-02-07 10:20:32.053932', 0),

    (6, NULL, '2030-11-07 15:13:52.045823', '1972-12-01', 5),

    (8, 'a', '1986-08-10 11:26:38.050309', '1980-09-21', 6),

    (1, 'g', '1972-03-27', '2030-02-26', NULL),

    (2, 'k', '2027-03-01', '2008-02-09 03:59:31.011550', 3),

    (5, 'o', '2034-06-10', '2014-10-27', 7),

    (4, 'mkfwds', '1985-05-17', '1972-04-23 11:13:27.051498', 3),

    (NULL, 'k', '2014-02-04 14:16:18.009533', '2004-10-23 07:35:45.045633', 4),

    (7, 'english', '1998-12-03', '2010-02-23 01:16:54.058174', 3),

    (NULL, 'f', '1973-01-02', '1977-08-05 07:45:02.013927', NULL);

-- Data for mysql_4 (10 rows)

INSERT IGNORE INTO mysql_4 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (b'0', 'fwd', '1973-08-17 12:47:49.032756', '2026-04-14', 3),

    (0, 'english', '2033-04-02 20:44:57.048180', '2010-10-01', 3),

    (NULL, NULL, '1992-03-14 11:49:59.043841', '1973-06-04', b'0'),

    (b'0', NULL, '2034-05-27', '2005-06-27 10:28:53.017338', NULL),

    (2, 'w', '2033-07-24 04:42:14.040050', '2034-02-23 03:09:45.059204', 5),

    (2, NULL, '2023-02-18', '1995-08-27 00:45:32.037239', b'0'),

    (b'0', 'english', '1999-09-01', '2028-11-17 09:14:03.035751', NULL),

    (9, 'g', '2013-09-26 17:04:06.056656', '2033-05-13', 6),

    (3, NULL, '2021-02-17 12:01:40.039305', '2000-10-12 21:58:15.015186', b'0'),

    (6, 'english', '1997-12-27 01:34:53.025944', '1972-08-14 07:56:10.046951', 6);

-- Data for mysql_5 (10 rows)

INSERT IGNORE INTO mysql_5 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (4, 'wdsdyf', '2003-12-23', '1983-03-10', b'0'),

    (b'0', NULL, '2025-03-04 06:52:24.040271', '1972-01-08 11:59:19.049444', 1),

    (9, NULL, '1971-06-18', '2016-04-18', NULL),

    (0, 'english', '2006-03-12 13:13:55.016224', '2026-02-11', 9),

    (b'0', NULL, '2000-12-10', '2028-11-24', 7),

    (b'1', NULL, '2001-10-16 02:24:52.018791', '2015-05-21 10:52:43.030670', b'0'),

    (NULL, 'dsdy', '2013-10-17', '2025-04-27', b'1'),

    (NULL, 'j', '2001-12-25', '2028-05-14 20:22:26.043428', b'0'),

    (8, 'k', '2009-03-03', '2032-04-08 08:25:07.044825', b'0'),

    (b'0', 'd', '2035-08-10', '1991-05-02 16:06:08.032792', 1);

-- Data for mysql_6 (10 rows)

INSERT IGNORE INTO mysql_6 (col_int,col_varchar,col_date,col_timestamp,col_numeric) VALUES

    (NULL, 'x', '2024-04-07', '1992-01-13', 5),

    (b'1', 'sd', '2018-06-15', '1975-07-05 00:32:19.036944', NULL),

    (5, 'x', '1973-03-06', '1980-05-06', b'1'),

    (9, 'd', '2033-03-15', '2030-09-16 23:18:11.009927', NULL),

    (NULL, 'y', '2006-01-28', '2000-04-27 10:40:14.041203', 6),

    (0, 'english', '2013-07-07', '1983-04-06', 2),

    (3, 'fhuf', '2016-04-26 03:53:58.013779', '2024-04-13 01:38:41.013264', NULL),

    (NULL, 'hufjkb', '1998-10-18 23:53:48.041812', '2003-01-03 10:56:27.044447', 5),

    (5, NULL, '2031-04-16 05:30:13.029755', '2022-03-20', NULL),

    (1, NULL, '2012-04-22', '2022-01-14 21:57:10.054541', 7);

COMMIT;

-- ============================================================================

-- STEP 4: Create Temporary Tables (tmp1 to tmp10)

-- ============================================================================

DROP TABLE IF EXISTS tmp1;

CREATE TABLE tmp1 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

INSERT INTO tmp1 SELECT * FROM mysql_6;

DROP TABLE IF EXISTS tmp2;

CREATE TABLE tmp2 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

INSERT INTO tmp2 SELECT * FROM mysql_2;

DROP TABLE IF EXISTS tmp3;

CREATE TABLE tmp3 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB ROW_FORMAT=COMPACT;

INSERT INTO tmp3 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_5;

DROP TABLE IF EXISTS tmp4;

CREATE TABLE tmp4 (

    col_int INT NOT NULL,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB PARTITION BY RANGE (col_int) (

    PARTITION p0 VALUES LESS THAN (0),

    PARTITION p1 VALUES LESS THAN (100),

    PARTITION p2 VALUES LESS THAN (1000),

    PARTITION p3 VALUES LESS THAN MAXVALUE

);

INSERT INTO tmp4 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_6 WHERE col_int IS NOT NULL;

DROP TABLE IF EXISTS tmp5;

CREATE TABLE tmp5 (

    col_int INT NOT NULL,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB PARTITION BY LIST (ABS(col_int) MOD 10) (

    PARTITION p0 VALUES IN (0,1,2),

    PARTITION p1 VALUES IN (3,4,5),

    PARTITION p2 VALUES IN (6,7,8,9)

);

INSERT INTO tmp5 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_6 WHERE col_int IS NOT NULL;

DROP TABLE IF EXISTS tmp6;

CREATE TABLE tmp6 (

    col_int INT NOT NULL,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB PARTITION BY HASH(col_int) PARTITIONS 4;

INSERT INTO tmp6 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_3 WHERE col_int IS NOT NULL;

DROP TABLE IF EXISTS tmp7;

CREATE TABLE tmp7 (

    col_int INT PRIMARY KEY,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB PARTITION BY KEY(col_int) PARTITIONS 4;

INSERT IGNORE INTO tmp7 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_2 WHERE col_int IS NOT NULL;

DROP TABLE IF EXISTS tmp8;

CREATE TABLE tmp8 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO tmp8 SELECT * FROM mysql_4;

DROP TABLE IF EXISTS tmp9;

CREATE TABLE tmp9 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC

) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

INSERT INTO tmp9 SELECT * FROM mysql_4;

DROP TABLE IF EXISTS tmp10;

CREATE TABLE tmp10 (

    col_int INT,

    col_varchar VARCHAR(2000),

    col_date DATE,

    col_timestamp TIMESTAMP,

    col_numeric NUMERIC,

    INDEX idx_int(col_int),

    INDEX idx_varchar(col_varchar(100))

) ENGINE=InnoDB;

INSERT INTO tmp10 SELECT col_int, col_varchar, col_date, col_timestamp, col_numeric FROM mysql_5;

-- ============================================================================

-- STEP 5: Create Views

-- ============================================================================

DROP VIEW IF EXISTS view1chqin;

CREATE VIEW view1chqin AS SELECT * FROM mysql_5 WHERE col_int > 0;

DROP VIEW IF EXISTS view2chqin;

CREATE VIEW view2chqin AS SELECT t1.* FROM mysql_4 t1 WHERE t1.col_int > 0;

-- ============================================================================

-- STEP 6: Execute Crash-Inducing Query

-- WARNING: This query will crash the MariaDB server!

-- ============================================================================

DELETE LOW_PRIORITY FROM t11 USING tmp1 t11

RIGHT OUTER JOIN mysql_5 t12 ON NOT EXISTS (

    SELECT DISTINCT a2.col_int, a1.col_varchar, a1.col_date, a2.col_timestamp, a2.col_numeric

    FROM tmp4 a1

    LEFT OUTER JOIN (

        WITH cte1 AS (

            SELECT a1.col_int, a1.col_varchar, a2.col_date, a2.col_timestamp, a2.col_numeric

            FROM (tmp5 a1 JOIN tmp4 a2 ON (t12.col_timestamp = a1.col_timestamp AND t12.col_numeric IS NULL))

            JOIN tmp9 a3 ON (t12.col_int NOT IN (a2.col_int, 4, a1.col_int, a1.col_int) AND a2.col_numeric <= -123456789123456789123456789123456789123456789)

            WHERE a2.col_numeric NOT BETWEEN '0.464736938476562' AND '0.464736938476562' + 10

               OR a2.col_date NOT BETWEEN '2033-11-08' AND '2033-11-08' + 1

        ),

        cte2 AS (

            SELECT a1.col_int, a2.col_varchar, a1.col_date, a1.col_timestamp, a1.col_numeric

            FROM tmp10 a1

            NATURAL LEFT JOIN tmp9 a2

            NATURAL RIGHT JOIN view2chqin a3

            NATURAL LEFT JOIN view2chqin a4

            WHERE a2.col_timestamp NOT BETWEEN '1984-11-08 20:45:36.039569' AND '1984-11-08 20:45:36.039569' + interval '1' day

               OR a3.col_int NOT BETWEEN 8 AND 8 + 10

        )

        SELECT DISTINCT * FROM cte2 WHERE cte2.col_varchar LIKE 'k' GROUP BY col_int

    ) a2 ON (

        a2.col_int NOT BETWEEN 8 AND 8 + 10

        AND (a1.col_numeric NOT BETWEEN '0.464736938476562' AND '0.464736938476562' + 10)

        AND (t11.col_int IS NULL)

    )

    WHERE t12.col_timestamp = a2.col_timestamp

)

RIGHT OUTER JOIN view1chqin t13 ON 0 < LEAST(LEAST(0, t11.col_numeric), NULLIF(t11.col_numeric, acos(LEAST(GREATEST(t11.col_numeric, -1), 1))))

STRAIGHT_JOIN tmp8 t14 ON (

    NULLIF(

        cast(GREATEST(LEAST(t14.col_int,10),20) as SIGNED),

        cast(GREATEST(LEAST(-138805248,10),20) as SIGNED)

    ),

    CASE WHEN FALSE THEN LEAST('ojftzgr', 'jf') ELSE CASE WHEN TRUE THEN t13.col_varchar ELSE 'ftzgrhosrh' END END,

    t12.col_date,

    '1994-03-26 15:12:32.043993',

    0

) NOT IN (

    SELECT DISTINCT a2.col_int, a2.col_varchar, a2.col_date, a1.col_timestamp, a2.col_numeric

    FROM mysql_4 a1

    NATURAL RIGHT JOIN tmp9 a2

    NATURAL LEFT JOIN tmp6 a3

    NATURAL RIGHT JOIN mysql_3 a4

    WHERE t14.col_varchar IN ('t','zgrhosrhozxroh') OR a1.col_timestamp IS NULL

    EXCEPT

    SELECT a2.col_int, a2.col_varchar, a2.col_date, a2.col_timestamp, a1.col_numeric

    FROM mysql_3 a1

    RIGHT OUTER JOIN mysql_4 a2 ON (

        a1.col_int = a2.col_int + 1

        OR NOT (a2.col_int BETWEEN 8 AND 8 + 10)

        AND t13.col_int IS NULL

    )

    WHERE (t13.col_numeric BETWEEN '0.464736938476562' AND '0.464736938476562' + 10 OR t14.col_int IS NULL)

       OR (a1.col_int NOT BETWEEN 8 AND 8 + 10)

       AND t14.col_varchar LIKE 'C%'

);