I am working with a database built by a vendor, which I have remote access to. I noticed that the field
modify_date on one of the views is not null, according to DBeaver.
the tick indicates "not null".
and indeed running this query
select * from list l where l.modify_date is null
produces zero records.
However, running this query
select * from list l order by l.modify_date
produces the following.
As you can see, there is a single NULL record.
Also, this query
select l.modify_date, count(*) from list l group by l.modify_date order by l.modify_date
shows a count of 1 for null records.
Why do some queries return a NULL column here, but the column properties show it as NON NULL and trying to select NULL values returns zero records?
I forgot that I had taken a local copy of the view data. To do this, I examined the information_schema of the remote db and got the column names and properties. I used this to generate a 'create table...' statement locally. I extracted the view data as a csv and loaded the data with 'load data local infile ...'.
My create table statement looks like
CREATE TABLE if not exists Bloom.list ( some columns ... modify_date timestamp NOT NULL, some more columns ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;