0

I am using MySQL 5.7.32-0 on ubuntu 18.04.1 and have noticied some strange behaviour with this query

SELECT *  
FROM COMPLAINT 
WHERE DATE(CREATED_DT) < STR_TO_DATE('2021-02-23','%Y-%m-%d') 
  and COALESCE(COMPLAINT_STATUS,'') NOT IN ('COM','CLS');

Here CREATED_DT is of DATETIME and COMPLAINT_STATUS is VARCHAR. Most of the time the result set returns 9 Rows but suddenly sometime 16 and 10. The results are checked in Workbench and phpmyadmin.

Update

After inspecting which condition is mibehaving i found that the condition and COALESCE(COMPLAINT_STATUS,'') NOT IN ('COM','CLS') is the culprit but dont know whats wrong with it.

Update 2

I created new table and moved data to new table and deleted old and renamed new table now its working fine.

Pavan Kumar T S
  • 1,539
  • 2
  • 17
  • 26
  • Assuming your query doesn't use something non deterministic like `RANDOM()`, then it must return the same result set, assuming the underlying data remains the same. So, either your data or your query are changing. – Tim Biegeleisen Mar 06 '21 at 11:03
  • The table is does not get any additional data. It is isolated from any updations that what bother me. – Pavan Kumar T S Mar 06 '21 at 11:06
  • Adding `SHOW WARNINGS` , see https://stackoverflow.com/questions/15559711/mysql-workbench-how-to-display-warnings#:~:text=You%20can%20go%20to%20(Help,will%20display%20the%20warning%20message.&text=checkout%20this%20link.,you%20about%20the%20workbench%20windows.) might help to find the cause. – Luuk Mar 06 '21 at 11:16
  • @Luuk No warnings reported – Pavan Kumar T S Mar 06 '21 at 12:00
  • 1
    I think you can just use `COMPLAINT_STATUS NOT IN ('COM','CLS')` – Atmahadli Mar 06 '21 at 12:00
  • @Atmahadli it returns no results in that case – Pavan Kumar T S Mar 06 '21 at 12:01
  • what about `IFNULL(COMPLAINT_STATUS,'') NOT IN ('COM','CLS')` ? I also having problem with coalesce back there, and replace it with another logic. – Atmahadli Mar 06 '21 at 12:08
  • tried the same but not success.The problem persists in `COMPLAINT_STATUS is null` also – Pavan Kumar T S Mar 06 '21 at 12:10
  • 1
    maybe you have problem with char encoding https://stackoverflow.com/a/2338215/6536300 – Atmahadli Mar 06 '21 at 12:20
  • Will this problem persist when you change it to `... and NOT COMPLAINT_STATUS,'') IN ('COM','CLS');` ? – Luuk Mar 06 '21 at 12:21
  • Can you share the output of this statement `SELECT complaint_status, count(*) FROM COMPLAINT GROUP BY complaint_status;` ? Just to verify that there are no spaces (or other characters) in that field? – Luuk Mar 06 '21 at 12:23
  • @Luuk result updates every time i run the query – Pavan Kumar T S Mar 06 '21 at 12:41
  • Any way i copied the table with data to another table and the results are now static. I dont know whats wrong – Pavan Kumar T S Mar 06 '21 at 12:42
  • 1
    sounds like a [REPAIR TABLE](https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html) was needed (for som unknown reason). – Luuk Mar 06 '21 at 12:57
  • yes may be something wrong with data charset as suggested or broken data. – Pavan Kumar T S Mar 06 '21 at 13:44

0 Answers0