Does the behavior change if you use the SQL SET ISOLATION statement? http://db.apache.org/derby/docs/10.15/devguide/cdevconcepts15366.html#cdevconcepts15366
Also, have you tried your reproduction with different versions of Derby? thanks, bryan On Wed, Oct 23, 2019 at 2:07 PM Geraldine McCormack <geral...@ie.ibm.com> wrote: > > Hi derby group, > > I am hitting a very strange issue on derby 10.14 where an update statement > seems to be "remembering" an old column value. I cannot recreate this on all > systems but the SQL below shows the issue. > > At one point in the past, the integer column PATTERN_IS_OKwas set to > 318901for 4066 rows. Later it is updated to a different value. But derby > seems to remember 318901aswell as knowing about the new value. > > See the pieces in red below where 4066 rows are updated even though the > PATTERN_IS_OK column has a different value. > > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from RELATEDEVENTS.RE_GROUPS > WHERE CONFIGNAME = 'ACCESS_CEMS' group by CONFIGNAME ,PATTERN_IS_OK; > 1 |PATTERN_IS&|CONFIGNAME > -------------------------------------------------------------------------------------------------------------------------------------------------------- > 208 |3 |ACCESS_CEMS > 4066 |5555 |ACCESS_CEMS > > 2 rows selected > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 4444 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ; > 4066 rows inserted/updated/deleted > > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from RELATEDEVENTS.RE_GROUPS > WHERE CONFIGNAME = 'ACCESS_CEMS' group by CONFIGNAME ,PATTERN_IS_OK; > 1 |PATTERN_IS&|CONFIGNAME > -------------------------------------------------------------------------------------------------------------------------------------------------------- > 208 |3 |ACCESS_CEMS > 4066 |4444 |ACCESS_CEMS > > 2 rows selected > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 8888 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ; > 4066 rows inserted/updated/deleted > > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from RELATEDEVENTS.RE_GROUPS > WHERE CONFIGNAME = 'ACCESS_CEMS' group by CONFIGNAME ,PATTERN_IS_OK; > 1 |PATTERN_IS&|CONFIGNAME > -------------------------------------------------------------------------------------------------------------------------------------------------------- > 208 |3 |ACCESS_CEMS > 4066 |8888 |ACCESS_CEMS > > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 2222 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 8888 ; > 4066 rows inserted/updated/deleted > > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from RELATEDEVENTS.RE_GROUPS > WHERE CONFIGNAME = 'ACCESS_CEMS' group by CONFIGNAME ,PATTERN_IS_OK; > 1 |PATTERN_IS&|CONFIGNAME > -------------------------------------------------------------------------------------------------------------------------------------------------------- > 208 |3 |ACCESS_CEMS > 4066 |2222 |ACCESS_CEMS > > 2 rows selected > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 2222 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 1111; > 0 rows inserted/updated/deleted > WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of > a query is an empty table. > > ij> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 8888 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ; > 4066 rows inserted/updated/deleted > > ij> > update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK = 8888 > WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 8888; > 4066 rows inserted/updated/deleted > > > The table was an existing table for which the column PATTERN_IS_OK column was > added with default value of -2. This was updated to 318901 and ever after > derby seems to remember this value. Restarting derby does not help. The issue > persists. I cannot reproduce this on all systems or even consistently on this > system - I have tried adding other columns like this one and the issue does > not happen. > > > describe RELATEDEVENTS.RE_GROUPS; > COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& > ------------------------------------------------------------------------------ > GROUPID |BIGINT |0 |10 |19 |GENERATED&|NULL |NO > GROUPNAME |VARCHAR |NULL|NULL|256 |NULL |512 |NO > INSTANCES |BIGINT |0 |10 |19 |NULL |NULL |NO > CREATIONTIME |BIGINT |0 |10 |19 |NULL |NULL |NO > VALIDATIONTIME |TIMESTAMP|9 |10 |29 |NULL |NULL |NO > CONFIGNAME |VARCHAR |NULL|NULL|256 |NULL |512 |NO > GROUPTTL |BIGINT |0 |10 |19 |NULL |NULL |NO > PROFILE |VARCHAR |NULL|NULL|128 |NULL |256 |NO > TIMESFIRED |BIGINT |0 |10 |19 |NULL |NULL |NO > TIMESFIREDMONTHLY |BIGINT |0 |10 |19 |NULL |NULL |NO > TIMESFIREDRENEW |BIGINT |0 |10 |19 |NULL |NULL |NO > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO > LASTFIRED |TIMESTAMP|9 |10 |29 |NULL |NULL |YES > UNIQUEEVENTS |BIGINT |0 |10 |19 |NULL |NULL |NO > TOTALEVENTS |BIGINT |0 |10 |19 |NULL |NULL |NO > REVIEWED |BOOLEAN |NULL|NULL|1 |NULL |NULL |NO > TYPE |VARCHAR |NULL|NULL|10 |NULL |20 |NO > EVENTIDENTITIES |VARCHAR |NULL|NULL|1024 |NULL |2048 |NO > ALGVERSION |INTEGER |0 |10 |10 |NULL |NULL |YES > PATTERNACTION |VARCHAR |NULL|NULL|500 |NULL |1000 |YES > PATTERN |VARCHAR |NULL|NULL|4028 |NULL |8056 |YES > IS_CONFLICT |BOOLEAN |NULL|NULL|1 |FALSE |NULL |YES > CONFLICT_GROUP |VARCHAR |NULL|NULL|1024 |'' |2048 |YES > EVENTSCOUNT |INTEGER |0 |10 |10 |0 |NULL |YES > ACKNOWLEDGED_NON_ZE&|DECIMAL |0 |10 |31 |NULL |NULL |YES > NODE_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES > SUMMARY_EXAMPLE |VARCHAR |NULL|NULL|2048 |NULL |4096 |YES > ORIGINALSEVERITY_MAX|DECIMAL |0 |10 |31 |NULL |NULL |YES > ALERTGROUP_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES > ALARMSOURCE_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES > ALERTKEY_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES > PATTERN_IS_OK |INTEGER |0 |10 |10 |-2 |NULL |YES > GROUP_IS_OK |INTEGER |0 |10 |10 |-2 |NULL |YES > > > Has anyone come across something like this before ? There are no errors in > the derby log. Is there any logging I can turn on which would shed some light > on what is happening? > > Kind Regards, > Geraldine > > ----------------------------------------------------------------------------------------------------------------------- > > Geraldine McCormack > L3 Team Lead - TBSM & Netcool/Impact > IBM Ireland > > Visit the IBM Support Portalto open a case, download fixes, access product > documentation, education and training materials. > > Visit our devcenters (applications and operations) for blogs, docs and > resources brought to you by the technical people who create our offerings. > > > ------------------------------------------------------------------ > >
