10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

No.

For id the value of COLUMN_DEFAULT is NULL, meaning, there is no default value.

For editor the value of COLUMN_DEFAULT is a string literal with the value of NULL — a four-character string, not an SQL NULL value. This is how you can distinguish between a column with DEFAULT NULL and a column with no default at all.

Sorry, but you are wrong Sergei. The editor column is defined without a default value so it gets NULL as a default value, if it had been defined with default 'NULL' as a default value the result of the first query would have been:

| editor      | 'NULL'               |

Not the ' characters before and after NULL in the result there.
If I change the definition of editor to

editor varchar(80) NOT NULL,

without a default value again, I get the same result for the first query and a correct result for the second query:

+-------------+------------------------+

| COLUMN_NAME | isnull(COLUMN_DEFAULT) |

+-------------+------------------------+

| id          |                      1 |

| date        |                      0 |

| comment     |                      0 |

| editor      |                      1 |

| editing     |                      0 |

| status      |                      0 |

+-------------+------------------------+

So you have an inconsistent behavior in your way of handling NULL values here.

Consider the table

create table t1 (

  c1 varchar(80) NOT NULL,

  c2 varchar(80),

  c3 varchar(80) DEFAULT NULL,

  c3 varchar(80) DEFAULT 'NULL'

)

The first column has no default value. You cannot do

insert into t1 (c2,c3,c4) values ('a','b','c')

So it'll have NULL value in the COLUMN_DEFAULT column, meaning, "no value". You can find these columns with

select * from information_schema.columns where column_default IS NULL

The second and third columns are the same, the default value is NULL, if you do

insert into t1 (c1,c4) values ('a','b')

columns c2 and c3 will get NULL. The value of the COLUMN_DEFAULT column will be a 4-character string literal NULL. You can find these columns with

select * from information_schema.columns where column_default = 'NULL'

The fourth column has a default value of the literal string NULL, the value of the COLUMN_DEFAULT column will be 'NULL'. You can find these columns with

select * from information_schema.columns where column_default = '\'NULL\''

Works better it the last column is named c4 in your table definition.

In 5.5.68-MariaDB MariaDB Server (default in centos 7) you get the correct result of this query:

select COLUMN_NAME, COLUMN_DEFAULT, isnull(COLUMN_DEFAULT)
from information_schema.columns
where
TABLE_SCHEMA = 'x'
and TABLE_NAME = 't1'
and column_default is NULL;

That is:

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

in 10.6.16-MariaDB you get

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

and for the question:

select COLUMN_NAME, COLUMN_DEFAULT, isnull(COLUMN_DEFAULT)
from information_schema.columns
where
TABLE_SCHEMA = 'x'
and TABLE_NAME = 't1'
and column_default = 'NULL'

you get the correct result in 5.5.68:

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

and the wrong result in 10.6.16-MariaDB:

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

What you get after the insert into t1 (c1,c4) values ('a','b') is NULL values for column c2 and c3 (no 4-character string literal NULL)
The result of the query

select * from t1
where c2 is NULL;

is one row in both 5.5.68 and 10.6.16, that is correct. And the result of

select * from t1
where c2 = NULL;

is no rows in both 5.5.68 and 10.6.16, that is correct, and also the result of:

select * from t1
where c2 = 'NULL';

is no rows both 5.5.68 and 10.6.16, that is correct.