Sql-server – Odd behavior of between operator when updating

sql serversql-server-2008sql-server-2012t-sql

I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software loads some info in the morning from a text file (from an ERP) and then 3 hours after it runs 4 jobs. If I run all these processes by hand, I get the same result at the first job. This is why I have isolated this one job.

I have checked for triggers and there are none. I have checked for procedures and all belong to stuff users can do in the software. Even with every user logged out (I took down the service, so no users would log in) it still happens.

Job number 1

UPDATE SOME_TABLE SET VALUE_DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)
WHERE VALUE_DATE < DATEADD(d,0,GETDATE()) AND FLAG BETWEEN '0' AND '1'

This baffles me because Flag can only take 3 values 0,1,2 and are numeric values.

DataSet prior to job

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-05-22  |    0    |
                |  AAA  |   19999  |   2018-05-21  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-05-15  |    0    |
                |  CCC  |   15000  |   2018-04-15  |    1    |
                +-------+----------+---------------+---------+

DataSet after the is job run (today's date is 2018-06-14)

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    1    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    1    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

Second job executed

UPDATE SOME_TABLE SET FLAG='0'
WHERE OTHER_FLAG IN ('COND1', 'COND2'...) AND FLAG='1'

Result of dataSet after second job – expected

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    0    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    0    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

This query doesn't only update the VALUE_DATE it also sets FLAG to 1 on every entry. Then the second job is in queue to set the FLAG value back to 0 where it updated. I find it convoluted and inefficient. I want to update it because it takes a great deal of time and people once a month to curate the information in the db.

Why is the FLAG column getting updated? Will changing FLAG BETWEEN '0' and '1' to FLAG in ('0','1') have any corrective effect? Or any adding "context" delimiter will help ("",'',(),[],{},etc)?

How can I make the first job only update the Value_date when flag is 0 or 1 and ditch the second job completely? This way I can get the expected table results

I'm sure it's not a software issue, because the date integrity holds just fine when doing stuff in the software. It only happens when this job is executed.

Best Answer

The SQL statement you've listed, run by itself, would not alter the FLAG column.

I've put together a SQLFiddle that shows what that statement does on its own, and you'll see that FLAG isn't changed by it.

This is why so many have assumed there's a trigger, or another statement, involved in the process.

You've said that there's no trigger on SOME_TABLE. If so, then some other statement is making the change.

The best way to find what that statement is would be to use SQL Profiler (or extended events) to track what's being executed when your job runs. If some other statement is being triggered by the relevant job, this should show you what that is. If you search this site, you'll find information on these options.

If you need to prove to yourself whether or not the UPDATE statement in and of itself is causing the change to FLAG, I'd recommend the following:

During a maintenance window:

  • Begin a transaction
  • Run the UPDATE statement inside that transaction
  • Check the results of the statement on SOME_TABLE (again from within the transaction)
  • Roll back the transaction.

If there's no transaction, and that SQL Statement (not the whole job, just that statement) does change the value of FLAG, then either this is tied to a bug in SQL Server (not impossible, but somewhat unlikely), or there's something seriously wrong with your SQL Server installation. Or, of course, there really is a trigger - I'm assuming not, based on what you've said, but that's a much more likely solution than a bug or a messed up SQL Server instance.

Related Question