Query with 2 NOT LIKE conditions returns 0 results

please try to use NOT IN and see if it provides results

SELECT
*
FROM table1 as x
WHERE
operationcontextid = 74
AND autoTag NOT IN ('%ismaintenance%' , '%false_alarm%') ;

@SruthiA said:
please try to use NOT IN and see if it provides results

SELECT
*
FROM table1 as x
WHERE
operationcontextid = 74
AND autoTag NOT IN ('%ismaintenance%' , '%false_alarm%') ;

Unfortunately no, it still outputs 0 results

The original query is on 2 different columns, autoTag and alarmTag.
I've tried with NOT IN but no luck

SELECT
*
FROM table1 as x
WHERE
operationcontextid = 74
AND autoTag NOT IN ('ismaintenance')
AND alarmTag NOT IN ('false_alarm')

I thought it was same column. with the condition you have it should return rows which don't have both ismaintenance and false_alarm set..

Hi, could you post your Vertica version? This will help us track down any possible issue.

What is the data type of the tag columns? What is the expected result? Two possible issues: if it is a varchar column, then matching is case-sensitive so different case or padding/trim may affect matching, and second, check whether there are NULL values since this also affects (NOT) LIKE and (NOT) IN matching.

@angelompcunha : Could you please share table DDL and some sample rows having ismaintenance and false_alarm

Unfortunately, I am not able to share the table DDL because I only have access to this database via Splunk DB Connect. This is in my company's server so I am very limited in what I can do and at the moment I don't have the credentials available to log in through a DB client which was my initial intent so I could have a look at the query execution plan.
I've attached a CSV with all the possible combinations of values that I may have in this table.
Basically, autoTag column may have null, empty string ( '' ) or #IsMaintenance values and the alarmTag column may have nulls, #false_alarm or #managed values.
Because I'm exporting this to a CSV I don't know if the autoTag column will have the nulls and empty string interpreted as being the same.
Just to avoid any confusion in my initial question I was looking ismaintenance instead of IsMaintenance but that was just to make things simpler when posting the question, I have tested using ILIKE to be case insensitive and I still have the same problem. The real query I'm executing and is giving problems is as follows:

SELECT
autoTag,
IDENTIFIER,
alarmTag
FROM table1 as x
WHERE
operationcontextid = 74
AND alarmTag NOT LIKE '%false_alarm%'
AND autoTag NOT LIKE '%IsMaintenance%'

The reason for the conditions having to be a NOT LIKE is that I am not the one managing the data source which means that, for example, today the column autoTag may be populated with #IsMaintenance , which is the value I want to exclude, but in some months someone may change some of the values to be something like #IsMaintenanceOffline.

I'm really appreciated for everyone's effort helping me

I tried to reproduce the issue using the data you shared. the main problem is autoTag has either #IsMaintenance or NULL. Same is the case with alarmtag and it has NULL values for few rows. due to those NULL values, query is not returning correct results.

So to test, I insert 1 row which has values for all the three columns and it is returning results. but the ones with NULL rows are still missing in the output .

eonv1111=> create table table1(identifier int, alarmtag varchar(100), autotag varchar(100));
CREATE TABLE

eonv1111=> copy table1 from local 'insert.csv' delimiter E'\t';

Rows Loaded

     131

(1 row)

eonv1111=> select * from table1 WHERE alarmTag NOT LIKE '%false_alarm%' and autoTag NOT LIKE '%IsMaintenance%'
eonv1111-> ;
identifier | alarmtag | autotag
------------+----------+---------
(0 rows)

eonv1111=> insert into table1 values(2,'#managed','#test');

OUTPUT

  1

(1 row)

eonv1111=> select * from table1 WHERE autoTag NOT ILIKE '%Maintenance%' and alarmTag NOT LIKE '%false_alarm%';
identifier | alarmtag | autotag
------------+----------+---------
2 | #managed | #test
(1 row)

@angelompcunha : I found a workaround for your issue. You need to use NVL to fill up the NULL values and now the query will return the results. Please find the results from my environment below

eonv1111=> select * from (select identifier,NVL(autoTag, 'autoTagnull') as autoTag ,NVL(alarmTag, 'alarmTagnull') as alarmTag from table1) x WHERE x.alarmTag NOT LIKE '%false_alarm%' and x.autoTag NOT LIKE '%IsMaintenance%';

identifier | autoTag | alarmTag
------------+-------------+--------------
10544472 | autoTagnull | alarmTagnull
10544492 | autoTagnull | alarmTagnull
10544675 | autoTagnull | alarmTagnull
10698154 | autoTagnull | alarmTagnull
13052646 | autoTagnull | alarmTagnull
15019596 | autoTagnull | alarmTagnull
15019597 | autoTagnull | alarmTagnull
15019631 | autoTagnull | alarmTagnull
15019637 | autoTagnull | alarmTagnull
15019758 | autoTagnull | alarmTagnull
15070080 | autoTagnull | alarmTagnull
35518301 | autoTagnull | alarmTagnull
35518301 | autoTagnull | alarmTagnull
35518322 | autoTagnull | alarmTagnull
35518322 | autoTagnull | alarmTagnull
35518352 | autoTagnull | alarmTagnull
35518352 | autoTagnull | alarmTagnull
35518388 | autoTagnull | alarmTagnull
35518388 | autoTagnull | alarmTagnull
35518429 | autoTagnull | alarmTagnull
35518429 | autoTagnull | alarmTagnull
35518432 | autoTagnull | alarmTagnull
35518432 | autoTagnull | alarmTagnull
35518434 | autoTagnull | alarmTagnull
35518434 | autoTagnull | alarmTagnull
35518440 | autoTagnull | alarmTagnull
35518440 | autoTagnull | alarmTagnull
35518447 | autoTagnull | alarmTagnull
35518484 | autoTagnull | alarmTagnull
35673681 | autoTagnull | #managed
10544471 | autoTagnull | alarmTagnull
10544483 | autoTagnull | alarmTagnull
10544491 | autoTagnull | alarmTagnull
10544496 | autoTagnull | alarmTagnull

Hello SruthiA

Thanks for the help, that did solve my problem.

Not like will filter out the nulls values, you can probably reduce your query to one of the following query:

select *
  from table1 as x
 where operationcontextid = 74
   and coalesce(autoTag , '') not like '%ismaintenance%'
   and coalesce(alarmTag, '') not like '%false_alarm%';

select *
  from table1 as x
 where operationcontextid = 74
   and (autoTag  not like '%ismaintenance%' or autoTag  is null)
   and (alarmTag not like '%false_alarm%'    or alarmTag is null);

@VValdar said:
Not like will filter out the nulls values, you can probably reduce your query to one of the following query:

select *
  from table1 as x
 where operationcontextid = 74
   and coalesce(autoTag , '') not like '%ismaintenance%'
   and coalesce(alarmTag, '') not like '%false_alarm%';

select *
  from table1 as x
 where operationcontextid = 74
   and (autoTag  not like '%ismaintenance%' or autoTag  is null)
   and (alarmTag not like '%false_alarm%'    or alarmTag is null);

Hello there. Thanks for this tip as well, it does produce results.

Now what I would like to understand is if this behavior is what's expected from the database because it doesn't make much sense to me the null values having some kind of influence in the results. I haven't found anything related to the LIKE and ILIKE operators that would be affected by null values

Yes, this is expected behavior, and it works the same in all RDBMS I know of.
Null is a special marker for absence of value, thus you can't compare it.
So when you write MyCol <> 'ABC' in reality it means MyCos is not null and MyCol <> 'ABC', because making a comparaison removed the nulls from the equation.