[MDEV-20501] Assertion `maybe_null || !null_value' failed in Item_func_round::date_op Created: 2019-09-04  Updated: 2023-01-13

Status: Stalled
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-10.1, not-10.2, not-10.3

Issue Links:
Relates
relates to MDEV-26536 Assertion `maybe_null() || !null_valu... Closed
relates to MDEV-20816 Assertions `maybe_null || !null_value... Confirmed
relates to MDEV-30339 sql_mode=traditional: assertion about... Open

 Description    

CREATE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE TRUNCATE( t, 1 );
 
 
 
# Cleanup
 
DROP TABLE t1;

10.4 18af13b8 debug

 
mysqld: /data/src/10.4/sql/item_func.cc:2541: virtual bool Item_func_round::date_op(THD*, MYSQL_TIME*, date_mode_t): Assertion `maybe_null || !null_value' failed.
 
190905  0:29:29 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fc204df7f12 in __GI___assert_fail (assertion=0x5652573e4fd0 "maybe_null || !null_value", file=0x5652573e4c28 "/data/src/10.4/sql/item_func.cc", line=2541, function=0x5652573e6c60 <Item_func_round::date_op(THD*, st_mysql_time*, date_mode_t)::__PRETTY_FUNCTION__> "virtual bool Item_func_round::date_op(THD*, MYSQL_TIME*, date_mode_t)") at assert.c:101
 
#8  0x000056525690d62b in Item_func_round::date_op (this=0x7fc1e4014000, thd=0x7fc1e4000b00, to=0x7fc1fafc0ac0, fuzzydate=...) at /data/src/10.4/sql/item_func.cc:2541
 
#9  0x000056525691e3d2 in Item_func_hybrid_field_type::date_op_with_null_check (this=0x7fc1e4014000, thd=0x7fc1e4000b00, ltime=0x7fc1fafc0ac0) at /data/src/10.4/sql/item_func.h:675
 
#10 0x0000565256906155 in Item_func_hybrid_field_type::val_real_from_date_op (this=0x7fc1e4014000) at /data/src/10.4/sql/item_func.cc:864
 
#11 0x000056525673a568 in Type_handler_temporal_result::Item_func_hybrid_field_type_val_real (this=0x565257ce5c20 <type_handler_datetime2>, item=0x7fc1e4014000) at /data/src/10.4/sql/sql_type.cc:4794
 
#12 0x00005652564fb7bb in Item_func_hybrid_field_type::val_real (this=0x7fc1e4014000) at /data/src/10.4/sql/item_func.h:751
 
#13 0x000056525673919b in Type_handler_temporal_result::Item_val_bool (this=0x565257ce5c20 <type_handler_datetime2>, item=0x7fc1e4014000) at /data/src/10.4/sql/sql_type.cc:4378
 
#14 0x00005652563f3038 in Item::val_bool (this=0x7fc1e4014000) at /data/src/10.4/sql/item.h:1458
 
#15 0x00005652565b0ffb in Item::eval_const_cond (this=0x7fc1e4014000) at /data/src/10.4/sql/item.h:1466
 
#16 0x000056525658edfe in Item::remove_eq_conds (this=0x7fc1e4014000, thd=0x7fc1e4000b00, cond_value=0x7fc1e4014d60, top_level_arg=true) at /data/src/10.4/sql/sql_select.cc:17209
 
#17 0x000056525656e874 in make_join_statistics (join=0x7fc1e4014a50, tables_list=..., keyuse_array=0x7fc1e4014d40) at /data/src/10.4/sql/sql_select.cc:5241
 
#18 0x0000565256563849 in JOIN::optimize_inner (this=0x7fc1e4014a50) at /data/src/10.4/sql/sql_select.cc:2193
 
#19 0x0000565256561426 in JOIN::optimize (this=0x7fc1e4014a50) at /data/src/10.4/sql/sql_select.cc:1562
 
#20 0x000056525656c64e in mysql_select (thd=0x7fc1e4000b00, tables=0x7fc1e4013788, wild_num=1, fields=..., conds=0x7fc1e4014000, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc1e4014a28, unit=0x7fc1e4004a30, select_lex=0x7fc1e40131c8) at /data/src/10.4/sql/sql_select.cc:4591
 
#21 0x000056525655c4c7 in handle_select (thd=0x7fc1e4000b00, lex=0x7fc1e4004968, result=0x7fc1e4014a28, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:425
 
#22 0x00005652565227a2 in execute_sqlcom_select (thd=0x7fc1e4000b00, all_tables=0x7fc1e4013788) at /data/src/10.4/sql/sql_parse.cc:6357
 
#23 0x0000565256517d4c in mysql_execute_command (thd=0x7fc1e4000b00) at /data/src/10.4/sql/sql_parse.cc:3899
 
#24 0x00005652565268ec in mysql_parse (thd=0x7fc1e4000b00, rawbuf=0x7fc1e4013118 "SELECT * FROM t1 WHERE TRUNCATE( t, 1 )", length=39, parser_state=0x7fc1fafc2170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7909
 
#25 0x00005652565118ec in dispatch_command (command=COM_QUERY, thd=0x7fc1e4000b00, packet=0x7fc1e4008321 "SELECT * FROM t1 WHERE TRUNCATE( t, 1 )", packet_length=39, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1843
 
#26 0x000056525650ff36 in do_command (thd=0x7fc1e4000b00) at /data/src/10.4/sql/sql_parse.cc:1360
 
#27 0x0000565256699c47 in do_handle_one_connection (connect=0x565259474f20) at /data/src/10.4/sql/sql_connect.cc:1412
 
#28 0x0000565256699996 in handle_one_connection (arg=0x565259474f20) at /data/src/10.4/sql/sql_connect.cc:1316
 
#29 0x00005652570cfda3 in pfs_spawn_thread (arg=0x5652594aa330) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#30 0x00007fc20696c4a4 in start_thread (arg=0x7fc1fafc3700) at pthread_create.c:456
 
#31 0x00007fc204eb4d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

A similar crash happens with these scripts:

CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE TRUNCATE( t, 1 ) = '2001-01-01 10:20:30';


CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE TRUNCATE(t,1));


CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE TRUNCATE(t,1)='2001-01-01 10:20:30');


CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t1 WHERE TRUNCATE(t,1));


CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t1 WHERE TRUNCATE(t,1)='2001-01-01 10:20:30');

No visible effect on a non-debug build.
Not reproducible on 10.3 or, oddly, on 10.5.

 Comments   
Comment by Alexander Barkov [ 2019-09-06 ]

Reproducible from command line with ENGINE=MyISAM and ENGINE=Memory:

CREATE OR REPLACE TABLE t1 (t TIMESTAMP NOT NULL) ENGINE=MyISAM;
 
SELECT * FROM t1 WHERE TRUNCATE( t, 1 );
 
DROP TABLE t1;

Not reproducible with ENGINE=INNODB

Comment by Elena Stepanova [ 2019-09-06 ]

Also reproducible with Aria.
My bad, I have now added MyISAM to the test case in the description, to avoid further confusion.

Comment by Alexander Barkov [ 2019-09-06 ]

The problem happens because Item_func_round::const_item() returns true in case of an empty table, because Item_field::const_item() returns true.

So in this call:

 conds= conds->remove_eq_conds(join->thd, &join->cond_value, true);

it tries to evaluate the WHERE condition on an empty table, which looks wrong.

Comment by Alexander Barkov [ 2019-09-06 ]

Note, WHERE condition is evaluated on empty table in all versions between 5.5 and 10.5, which looks wrong.
The crash happens only in 10.4, because of the recent ROUND/TRUNCATE changes in MDEV-20397.

Comment by Alexander Barkov [ 2019-09-06 ]

Hi igor, please take over this problem. It looks wrong for me that the optimizer tries to evaluate the WHERE condition in this script.

Comment by Elena Stepanova [ 2020-01-07 ]

Now also reproducible with 10.5.

Comment by Roel Van de Paar [ 2020-05-21 ] 

USE test;
 
CREATE TABLE t (a TIMESTAMP,b TIMESTAMP);  # (ENGINE=InnoDB) 
INSERT INTO t VALUES (0,0);
 
SET SQL_MODE='TRADITIONAL';
 
SELECT TRUNCATE(a,b) AS c FROM t;

Leads to:

10.5.3 cfe5ee90c8e4b9dfa98a41fcd299197a59261be7

 
mysqld: /test/10.5_dbg/sql/item_func.cc:2564: virtual bool Item_func_round::date_op(THD*, MYSQL_TIME*, date_mode_t): Assertion `maybe_null || !null_value' failed.

10.5.3 cfe5ee90c8e4b9dfa98a41fcd299197a59261be7

 
Core was generated by `/test/MD110520-mariadb-10.5.3-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
 
[Current thread is 1 (Thread 0x1550414eb700 (LWP 5986))]
 
(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
 
#1  0x000055bf8974ec11 in my_write_core (sig=sig@entry=6) at /test/10.5_dbg/mysys/stacktrace.c:518
 
#2  0x000055bf88ef3f8d in handle_fatal_signal (sig=6) at /test/10.5_dbg/sql/signal_handler.cc:329
 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
 
#5  0x000015503fc2f801 in __GI_abort () at abort.c:79
 
#6  0x000015503fc1f39a in __assert_fail_base (fmt=0x15503fda67d8 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55bf89a6d9d4 "maybe_null || !null_value", file=file@entry=0x55bf89a6dc30 "/test/10.5_dbg/sql/item_func.cc", line=line@entry=2564, function=function@entry=0x55bf89a6c520 <Item_func_round::date_op(THD*, st_mysql_time*, date_mode_t)::__PRETTY_FUNCTION__> "virtual bool Item_func_round::date_op(THD*, MYSQL_TIME*, date_mode_t)") at assert.c:92
 
#7  0x000015503fc1f412 in __GI___assert_fail (assertion=assertion@entry=0x55bf89a6d9d4 "maybe_null || !null_value", file=file@entry=0x55bf89a6dc30 "/test/10.5_dbg/sql/item_func.cc", line=line@entry=2564, function=function@entry=0x55bf89a6c520 <Item_func_round::date_op(THD*, st_mysql_time*, date_mode_t)::__PRETTY_FUNCTION__> "virtual bool Item_func_round::date_op(THD*, MYSQL_TIME*, date_mode_t)") at assert.c:101
 
#8  0x000055bf88f7bd0f in Item_func_round::date_op (this=0x15501e474848, thd=0x15501e415088, to=0x1550414e8fd8, fuzzydate=...) at /test/10.5_dbg/sql/item_func.cc:2564
 
#9  0x000055bf88e1ad08 in Type_handler_temporal_result::Item_func_hybrid_field_type_get_date (this=<optimized out>, thd=<optimized out>, item=<optimized out>, warn=<optimized out>, ltime=0x1550414e8fd8, fuzzydate=...) at /test/10.5_dbg/sql/sql_type.cc:5366
 
#10 0x000055bf88e2f133 in Type_handler::Item_func_hybrid_field_type_get_date_with_warn (this=0x55bf8a4c2d80 <type_handler_datetime2>, thd=thd@entry=0x15501e415088, item=item@entry=0x15501e474848, ltime=ltime@entry=0x1550414e8fd8, mode=mode@entry=...) at /test/10.5_dbg/sql/sql_type.cc:4950
 
#11 0x000055bf88f543f4 in Item_func_hybrid_field_type::get_date (this=this@entry=0x15501e474848, thd=0x15501e415088, to=to@entry=0x1550414e8fd8, mode=...) at /test/10.5_dbg/sql/item_func.h:820
 
#12 0x000055bf88e30224 in Type_handler::Item_send_datetime (this=<optimized out>, item=0x15501e474848, protocol=0x15501e415650, buf=0x1550414e8fd0) at /test/10.5_dbg/sql/sql_type.cc:7221
 
#13 0x000055bf88d84c59 in Type_handler_datetime_common::Item_send (this=<optimized out>, item=<optimized out>, protocol=<optimized out>, buf=<optimized out>) at /test/10.5_dbg/sql/sql_type.h:6186
 
#14 0x000055bf88b5d4b9 in Item::send (this=0x15501e474848, protocol=0x15501e415650, buffer=0x1550414e8fd0) at /test/10.5_dbg/sql/item.h:1054
 
#15 0x000055bf88b5b229 in Protocol::send_result_set_row (this=this@entry=0x15501e415650, row_items=row_items@entry=0x15501e474298) at /test/10.5_dbg/sql/protocol.cc:1082
 
#16 0x000055bf88becce8 in select_send::send_data (this=0x15501e475948, items=...) at /test/10.5_dbg/sql/sql_class.cc:2998
 
#17 0x000055bf88ca0e07 in select_result_sink::send_data_with_check (sent=<optimized out>, u=<optimized out>, items=..., this=<optimized out>) at /test/10.5_dbg/sql/sql_class.h:5237
 
#18 end_send (join=0x15501e475970, join_tab=0x15501e476fb0, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:21809
 
#19 0x000055bf88c890c8 in evaluate_join_record (join=join@entry=0x15501e475970, join_tab=join_tab@entry=0x15501e476c08, error=error@entry=0) at /test/10.5_dbg/sql/sql_select.cc:20838
 
#20 0x000055bf88c90447 in sub_select (join=0x15501e475970, join_tab=0x15501e476c08, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20615
 
#21 0x000055bf88cc81ec in do_select (procedure=0x0, join=0x15501e475970) at /test/10.5_dbg/sql/sql_select.cc:20149
 
#22 JOIN::exec_inner (this=this@entry=0x15501e475970) at /test/10.5_dbg/sql/sql_select.cc:4464
 
#23 0x000055bf88cc8807 in JOIN::exec (this=this@entry=0x15501e475970) at /test/10.5_dbg/sql/sql_select.cc:4245
 
#24 0x000055bf88cc6b1c in mysql_select (thd=thd@entry=0x15501e415088, tables=<optimized out>, fields=..., conds=0x0, og_num=0, order=<optimized out>, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x15501e475948, unit=0x15501e419098, select_lex=0x15501e474148) at /test/10.5_dbg/sql/sql_select.cc:4669
 
#25 0x000055bf88cc6e4b in handle_select (thd=thd@entry=0x15501e415088, lex=lex@entry=0x15501e418fd0, result=result@entry=0x15501e475948, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.5_dbg/sql/sql_select.cc:417
 
#26 0x000055bf88c51867 in execute_sqlcom_select (thd=thd@entry=0x15501e415088, all_tables=0x15501e474978) at /test/10.5_dbg/sql/sql_parse.cc:6172
 
#27 0x000055bf88c4a993 in mysql_execute_command (thd=thd@entry=0x15501e415088) at /test/10.5_dbg/sql/sql_parse.cc:3901
 
#28 0x000055bf88c57804 in mysql_parse (thd=thd@entry=0x15501e415088, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1550414ea3e0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:7957
 
#29 0x000055bf88c43ffd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x15501e415088, packet=packet@entry=0x15501e467089 "", packet_length=packet_length@entry=32, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:1839
 
#30 0x000055bf88c428cc in do_command (thd=0x15501e415088) at /test/10.5_dbg/sql/sql_parse.cc:1358
 
#31 0x000055bf88d9c99d in do_handle_one_connection (connect=<optimized out>, connect@entry=0x1550200433a8, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1411
 
#32 0x000055bf88d9d0b9 in handle_one_connection (arg=arg@entry=0x1550200433a8) at /test/10.5_dbg/sql/sql_connect.cc:1313
 
#33 0x000055bf891fb10a in pfs_spawn_thread (arg=0x15503f045888) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
 
#34 0x00001550409126db in start_thread (arg=0x1550414eb700) at pthread_create.c:463
 
#35 0x000015503fd1088f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.13 (dbg), 10.5.2 (dbg), 10.5.3 (dbg)

Bug confirmed not present in:
MariaDB: 10.1.45 (dbg), 10.1.45 (opt), 10.2.32 (dbg), 10.2.32 (opt), 10.3.23 (dbg), 10.3.23 (opt), 10.4.13 (opt), 10.5.2 (opt), 10.5.3 (opt), 10.5.4 (dbg), 10.5.4 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.47 (dbg), 5.6.47 (opt), 5.7.29 (dbg), 5.7.29 (opt), 8.0.19 (dbg), 8.0.19 (opt)

Oddly, alike to similar situation earlier in the comments, not reproducible on 10.5.4

10.5.4 69077dea25f6e7cab4ff8927e4429ad62af9de49

 
10.5.4>USE test;
 
Database changed
 
10.5.4>CREATE TABLE t (a TIMESTAMP,b TIMESTAMP);  # (ENGINE=InnoDB) 
Query OK, 0 rows affected (0.008 sec)
 
10.5.4>INSERT INTO t VALUES (0,0);
 
Query OK, 1 row affected (0.001 sec)
 
10.5.4>SET SQL_MODE='TRADITIONAL';
 
Query OK, 0 rows affected (0.000 sec)
 
10.5.4>SELECT TRUNCATE(a,b) AS c FROM t;
 
+---+
 
| c |
 
+---+
 
| NULL |
 
+---+
 
1 row in set (0.000 sec)

Comment by Roel Van de Paar [ 2021-05-07 ] 

SET sql_mode='';
 
CREATE TABLE t5 (c TIMESTAMP KEY,c2 INT NOT NULL,c3 CHAR(1) NOT NULL);
 
INSERT INTO t5 (c) VALUES (1);
 
SET sql_mode=traditional;
 
SELECT ROUND (c,c2),TRUNCATE (c,c2) FROM t5;

Comment by Roel Van de Paar [ 2021-09-04 ]

Affects 10.6-10.7 also.

Comment by Sergei Petrunia [ 2022-12-21 ]

Looking at parts of the problem

1. A constant (or system) table where record is not found is marked as having NULL-complemented row.

See the call to mark_as_null_row() in join_read_system().

The call will be made even if the table has table->maybe_null=0, which forms a contradicting state:

table->maybe_null==0 && table->null_row=true

2. Items assume the table columns are not NULL-able

When Item objects are created and resolved, the table columns are NOT NULL, so Item objects take that attribute.

3. Optimizer continues after it found empty const tables.

For some reason, query optimization doesn't stop as soon as we've learned that we have empty constant tables.

The first thing it does is to build equality classes. This is where the testcase for this MDEV fails.
It calls Item::remove_eq_conds which calls Item::eval_const_cond which hits the "non-NULLable item has a NULL value" situation.

The check for empty constant tables is done at a much later phase in JOIN::optimize_stage2, in two steps: :

    if (const_table_map != found_const_table_map &&
 
        !(select_options & SELECT_DESCRIBE))
 
    {
 
      // There is at least one empty const table
 
      zero_result_cause= "no matching row in const table";
 
      DBUG_PRINT("error",("Error: %s", zero_result_cause));
 
      error= 0;
 
      handle_implicit_grouping_with_window_funcs();
 
      goto setup_subq_exit;
 
    }

...

1   if (conds && const_table_map != found_const_table_map &&                                                    
        (select_options & SELECT_DESCRIBE))
 
    {
 
      conds=new (thd->mem_root) Item_bool(thd, false); // Always false
 
    }

Comment by Sergei Petrunia [ 2022-12-21 ]

Possible solutions:

S1: Avoid evaluating items that will see the not-nullable-is-null.

This is initial suggestion by bar

Don't call eval_const_cond() if the condition has references to tables with NULL-complemented rows.

The weak point: There are at least two places where eval_const_cond() is called.
There may be other places where the code will try to evaluate items.

S2: Do not mark constant tables as having null-complemented rows.

Basically 

@@ -21324,7 +21348,8 @@ join_read_system(JOIN_TAB *tab)
 
       if (error != HA_ERR_END_OF_FILE)
 
        return report_error(table, error);
 
       table->const_table= 1;
 
-      mark_as_null_row(tab->table);
 
+      if (table->maybe_null)
 
+        mark_as_null_row(tab->table);
 
       empty_record(table);                     // Make empty record
 
       return -1;

(I've tried this. Quite many changes in the testsuite result. Some extra warnings).

It is not entirely clear what values will one get when computing Items that refer to table columns.
Some fields get the default value. But what if a column doesn't have the default value?
Among the test result changes I get warnings.

S3: If we see empty constant tables, stop the query optimization at once

Tried implementing this. Hitting many odd failures.

Comment by Sergei Petrunia [ 2022-12-22 ]

A patch implementing solution S3: https://github.com/MariaDB/server/commit/03b2eb14cb6861cf64603c172545815df1c9b82c

Comment by Sergei Petrunia [ 2023-01-04 ]

(CC: serg)
On last sql processor call, there was a question about what exactly different const-row messages mean in EXPLAIN.

When the table should have a single row and that row is not found, it can be:

  • "Const row not found"
  • "Unique row not found"
  • "Impossible WHERE noticed after reading const tables"
  • "no matching row in const table".

Internally, when the table is a system table (has 0 or 1 rows, MyISAM) and we cant find the row, it is marked with "Const row not found".

If the SELECT has no WHERE condition at all, the "Const row not found" makes it to the EXPLAIN output:

explain select * from t_empty;
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
 
| id   | select_type | table   | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
 
|    1 | SIMPLE      | t_empty | system | NULL          | NULL | NULL    | NULL | 0    | Const row not found |
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+


explain select * from t_empty, ten;
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
 
| id   | select_type | table   | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
 
|    1 | SIMPLE      | t_empty | system | NULL          | NULL | NULL    | NULL | 0    | Const row not found |
 
|    1 | SIMPLE      | ten     | ALL    | NULL          | NULL | NULL    | NULL | 10   |                     |
 
+------+-------------+---------+--------+---------------+------+---------+------+------+---------------------+

A more typical case is when the query has a WHERE clause. The optimizer will attempt to compute it for NULL-complemented row and
the user will see "Impossible WHERE noticed ...":

explain select * from t_empty, ten where ten.a<10;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

When table $TBL is a "const" table (because there is table.primary_key=const restriction), but it is not a system table, it is marked with "Unique row not found". However, a restriction "table.primary_key=const" can only be inferred from the WHERE clause. That is, the WHERE clause is present, and EXPLAIN will show "Impossible WHERE noticed ...". It seems it's not possible to construct a query such that "Unique row not found" is shown to the user.

In some cases, EXPLAIN will also show "no matching row in const table". Note that the message is per-select, it won't specify which table it was.

The message is shown by ANALYZE commands where EXPLAIN would show "Impossible WHERE ...":

analyze select * from t_empty, ten where ten.a<10;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                          |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL   |     NULL |       NULL | no matching row in const table |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+

It is also frequently (always?) shown for subqueries:

explain select (select count(*) from t_empty, ten where ten.a<10) as SUBQ from ten;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
 
|    1 | PRIMARY     | ten   | ALL  | NULL          | NULL | NULL    | NULL | 10   |                                |
 
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | no matching row in const table |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

The reason is SELECT_DESCRIBE check in JOIN::optimize_stage2:

{
 
  if (const_table_map != found_const_table_map &&
 
      !(select_options & SELECT_DESCRIBE))
 
  {
 
    // There is at least one empty const table
 
    zero_result_cause= "no matching row in const table";

SELECT_DESCRIBE is not set for ANALYZE. Also it is not set when this code is executed for subqueries.

Comment by Sergei Petrunia [ 2023-01-04 ]

.. considering the above, changed the patch to print "no matching row in const table" when it finds the no-matching-rows-in-const-table condition and short-cuts the optimization.

https://github.com/MariaDB/server/commit/88caeb420e5d725e6a87cda23e5edcb7ca42aefb

Comment by Sergei Petrunia [ 2023-01-04 ]

Updated commit with also tests from a linked MDEV: https://github.com/MariaDB/server/commit/0bef50e50b59ea1d85fea027f45cc51ca74e4074

Comment by Sergei Petrunia [ 2023-01-04 ]

serg please review.

Generated at Thu Feb 08 08:59:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.