Re: Problem with records that disappear.

On 2013-01-14 11:41, John R Pierce wrote:
> On 1/14/2013 1:34 AM, Condor wrote:
>> It's a standalone statements without BEGIN TRANSACTION. An update is
>> easy to explain with another
>> but insert with missing row and there is no DELETE command in whole
>> code and no one other have access to server.
>> No any error logs, only access log system insert that user make the
>> changes (no info what changes).
>
>
> as I see it, that INSERT can't ever happen.  if AID is NULL, then the
> first IF AID > 0 will be FALSE, so the 2nd IF AID IS NULL will never
> get evaluated.   the two UPDATE's up front will not happen either if
> AID is null.
>
> and, what is SET X=X about?!?   thats a big noop anyways.
>
> is this 'redacted' code that's been sanitized?   the more I look at
> it, the more I'm cringing.   WHERE x=x on that SELECT INTO ids will
> return the whole table, since X = X is always true unless X is NULL.
>
>
>> BEGIN
>>   UPDATE table SET X = X where id = aid;
>>   UPDATE table_2 SET Y=Y where id = aid;
>>   IF aid > 0 THEN
>>     SELECT INTO ids id FROM table_3 WHERE x = x;
>>     IF aid IS NULL THEN
>>       INSERT INTO table_3 (id) VALUES (x);
>>     ELSE
>>       UPDATE table_3 SET id = id + 1 WHERE x = X;
>>     END IF;
>>   END IF;
>>   RETURN 200;
>> END;


Everything after the IF aid > 0 THEN is log statistic information and
is not important.
The first two updates are important, they actually make user changes:
UPDATE table SET X = X where id = aid;
UPDATE table_2 SET Y = Y where id = aid;
Everything after them is just statics and is not important.
I really change this line IF aid IS NULL THEN, it's should be  IF ids
IS NULL THEN
meaning if no record for ids in table_3 where x = X;

My mistake.