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.

enter image description here

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.

enter image description here

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 ...
  • is there a good cause why you didn't post the voew here, NULL come usually when you left/right join, so look at the view and not the result
    – nbk
    Jun 29 '20 at 9:19
  • is it view or table? Looks like data corruption or bug Jun 29 '20 at 9:19
  • @Nikita, well this is slightly embarrassing. For performance reasons I made a local copy of the view data, as a table. I loaded the data via a 'load data local ...' statement. The result I am looking at now comes from a table. Question remain though. This shouldn't be possible, should it? Jun 29 '20 at 9:24
  • 1
    did yoi look at the id SELET * W'HERE id =xyz, is there also a NULL
    – nbk
    Jun 29 '20 at 9:35
  • 1
    I think this is the answer: dba.stackexchange.com/questions/129694/… Jun 29 '20 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.