Query with 2 NOT LIKE conditions returns 0 results

angelompcunha · October 2023

Hello all,

I'm fairly new to Vertica and I'm running into an issue that I don't understand if I'm doing something wrong or is indeed a bug.

I'm trying to execute the following query:

SELECT
*
FROM table1 as x
WHERE
operationcontextid = 74
AND autoTag NOT LIKE '%ismaintenance%'
AND alarmTag NOT LIKE '%false_alarm%';

This query returns 0 results.
However, If I execute the query with just one of the NOT LIKE conditions it produces results and some of them do obey both initial conditions.
Example1:

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

Result:

IDENTIFIER	alarmTag	autoTag
7707491	#false_alarm
10544471
10544472
10544483

Example2:

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

Result:

IDENTIFIER	alarmTag	autoTag
37508489	#managed
37764004	#managed
39564539	#managed
41837260	#managed

As you can see, executing the query separately I get results for both conditions, and in both of them some results do obey the 2 LIKE conditions of the initial query but when I try to put them both together it outputs 0 results.
Instead of doing a NOT LIKE I execute the query with LIKE and specify all the possible values for each of the columns I get results but this is far from optimal because I would have to change the query every time a new value is added to one of the columns.

Is there something in Vertica's internal way of processing the query that makes it impossible to process 2 NOT LIKE conditions?

I'm running this on Vertica Analytic Database v7.2.3-16

I'm sorry if this is a real noob question but your help would be appreciated.

SruthiA · October 2023

@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%';

VValdar · October 2023

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);

SruthiA · October 2023

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%') ;

angelompcunha · October 2023

@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')

SruthiA · October 2023

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..

angelompcunha · October 2023

Exactly, that's what i thought. The statement makes sense from an SQL point of view but i was afraid it could be something specific to Vertica that was not running as I would expect. Im starting to think it may be something related with the database itself, dont know if the index or lack of it could be a cause.

Bryan_H · October 2023

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

angelompcunha · October 2023

It seems to be an older version.
Vertica analytic database v7.2.3-16

Bryan_H · October 2023

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 · October 2023

Datatype is varchar. The result I expect is to return all rows that do not contain ismaintenance in autoTag and do not contain false_alarm in alarmTag. I have tried the statement with both LIKE and ILIKE but face the same problem with both.
Im using both trailing and leading wildcards and yes, both columns may contain null values.
Could the null values be the issue in here?

SruthiA · October 2023

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

angelompcunha · October 2023

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

SruthiA · October 2023

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

(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 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 · October 2023

Hello SruthiA

Thanks for the help, that did solve my problem.

angelompcunha · October 2023

@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

VValdar · October 2023

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.

Query with 2 NOT LIKE conditions returns 0 results

Best Answers

Answers

Rows Loaded

OUTPUT

Leave a Comment