Sticky computed field v17

[former_SQLA_member1694949]

We have a column in a table that was a computed column (smallint and would either be 1 or 0). We have an event that runs daily and updates data in the required rows. It does not update this column, but the updating of the row would just make it happen. The computed column was a case statement based on another column before it in the table ( case when x like 'xx%' then 1 else 0 end).

I have altered the table, dropped the compute and set the default to 0, and updated all rows to 0, yet the field is still getting set to 1 daily as if the compute is still on it. I have added code to catch the next update, to verify it is the daily event (as nothing else has updated these rows), but has anyone else seen a computed field sticky like this before?

More Info as requested:

The column was a computed column based on a text column before it in the table. Another column further down the line used this column in its compute. We originally removed the compute via sybase central and set the defaukt value to 0. When they came back as 1 the next day, I ran a drop compute. In sybase central, then and now, it says default 0. We have an even that runs every morning to update a date field and kick off processes. When this scheduled even runs, these revert to 1. If I trigger the event, they stay as 0. For a workaround, I have changed the event to also update this field to 0 until I have new requirements for what to do with the field.

example:

   CREATE TABLE "MYTABLES"."THISTABLE" (
    "ID"                             char(15) NOT NULL
   ,"PORTCODE"                       varchar(30) NULL
   ,"LEAGUEVALUE"                    integer NOT NULL
   ,"ISDOM"                          smallint NOT NULL COMPUTE (case when ISNULL(PORTCODE,'') like 'us%' then 1 else 0 end)
   ,"ISSD"                           integer NOT NULL COMPUTE (case when LEAGUEVALUE between 0 and 5 then 1 when ISDOM > 0 then 1 else 0 end)
   ,"CHECKDATE"              date not null
   ,CONSTRAINT "PK_THISTABLE" PRIMARY KEY ("ID" ASC) 
)
go

ALTER TABLE MYTABLES.THISTABLE
ALTER ISDOM DROP COMPUTE;
COMMIT;


UPDATE MYTABLES.THISTABLE SET ISDOM=0 WHERE ISDOM=1;
COMMIT;

-- I then set the default to 0 for this field since it is not on ANY insert or update statements anywhere in our system

-- An event runs every morning that calls a proc, this proc sets checkdate to the current date
-- When this runs, it sets ISDOM back to 1

[VolkerBarth]

Any BEFORE UPDATE trigger or the like still in place?

[jack_schueler]

Can you provide some SQL for the original COMPUTE clause and the ALTER statement that you used to drop it?

[Breck_Carter]

Have you used SQL Central to view the table definition?

Look at the "Value" column in the "Columns" tab to see if this column contains 0, or it still contains "case when ..."

[Breck_Carter]

> has anyone else seen a computed field sticky like this before?

The answer to that question is "no, no, a thousand times no... if there was a bug this big it would have been reported already"

[chris_keating]

I cannot reproduce the behavior you describe based on the example you provided or an earlier attempt when this issue was first posted. I have a SQL script that completes the tasks described except running the update on a new day (via an event or directly). Are you able to reproduce this using the example you have provided above and manually running an update? What specific version and build of 17 are you using ( select @@version)? I have tested standalone update of the checkdate column as well as in an event which was manually triggered. I did simulate a delay (the each morning event schedule) by using a checkpoint before the event and manual update cases. It is unlikely that the event running the next day that is the cause.

Breck is correct to assert that it would be an issue that would likely have already been reported. I suspect that something is missing in the repro. If you can share your real database, I would recommend working with support to isolate the reproduction methodology and have this issue addressed if it is a defect.

[former_SQLA_member1694949]

My version is 17.0.9.4803. Unfortunately, I cannot provide a copy of the DB. I haven't tried with the example. I put a trigger on the table and confirmed it is the event that sets it back to 1. It has been driving me crazy so I put the workaround in this morning and am waiting to see if that stops the change to the field.

[chris_keating]

I have tried this in that version and continue not to reproduce. I think a number of us strongly believe this to be a schema related issue.

Do you have support with SAP? If so, you should consider reporting this and work with support to narrow down the issue. If you are able to do so, please reference my name and I can provide the SQL that I based my repro and you can then test the same in a database in your environment. If you do work with support, it would be extremely helpful to get the database in which you are seeing this issue as that would be the quickest way to resolve what is the cause - a bug or some other cause.

[former_SQLA_member1694949]

I'll work with support if and when I have time. I use Brecks software to document the DB, and a quick search on that shows that field is not updated by anything on the schema. I write the software code as well, so I have double chceked all of that for the field. It was never even added to the abstracts so that people would not think they could update it. It's a mystery.

[former_SQLA_member1694949]

Just an FYI. Over 24 hours since I modified the event to not only set the date to the current date, but to set this field to 0, there have been no records changing back to a 1.

[Breck_Carter]

Good luck!

I'm sure everything will be OK now 🙂

[former_SQLA_member1694950]

Best thing to figure this out is, copy the data in some different named column and drop this column.

[Breck_Carter]

Your answer is a workaround, it does nothing to "figure this out"... the real reason is most likely something else, and it may cause other problems.