Here is a copy and paste of the shema involved :
db1 eval {CREATE TABLE BulletinsTemp0 (\
IDPayeMens VARCHAR(19), \
CodSoc VARCHAR(3), \
CodEtabliss VARCHAR(3), \
AnneeMois VARCHAR(6), \
Trimestre VARCHAR(1), \
PerEmpl INTEGER, \
SousPeriode VARCHAR(2), \
Annee VARCHAR(4), \
Mois VARCHAR(2), \
Matricule VARCHAR(10), \
CodPlan VARCHAR(3), \
RubrUser VARCHAR(4), \
Callback INTEGER, \
Nature VARCHAR(12), \
Sens INTEGER, \
TxAssiette REAL DEFAULT 0.0, \
Quantite REAL DEFAULT 0.0, \
QtiteJrs REAL DEFAULT 0.0, \
Taux REAL DEFAULT 0.0, \
Montant REAL DEFAULT 0.0, \
TxPatr REAL DEFAULT 0.0, \
MontantPatronnal REAL DEFAULT 0.0, \
Assiette REAL DEFAULT 0.0, \
Precis REAL DEFAULT 0.0, \
DebutRattach VARCHAR(7) DEFAULT '', \
FinRattach VARCHAR(7) DEFAULT '', \
Fisc INTEGER , \
TypePrime INTEGER , \
IndemAbs INTEGER , \
JrAbsenceNI INTEGER , \
MMA INTEGER , \
TypeIDep VARCHAR(2), \
CodRecap1 VARCHAR(10), \
CodRecap2 VARCHAR(4), \
CodRecap3 VARCHAR(4), \
CodExt VARCHAR(2), \
Ordre INTEGER DEFAULT 1, \
Selection INTEGER DEFAULT 0, \
PRIMARY KEY (IDPayeMens,
SousPeriode, RubrUser) \
) }
There are no separate indices created on columns of BulletinsTemp0
In my preceding post, "Field1" refers to column "_CodRecap1_",
"Field2" refers to column "_RubrUser_"
The purpose of this table is to collect all historical data for a year
The process of filling BulletinsTemp0 is done in 2 steps :
1) INSERT from a table archiving old historical data from january ->
november for the current year
2) Then INSERT from another table containing "last month" data, i.e.
december in our case
These INSERT don't populate column CodRecap1, CodRecap2, CodRecap3, and
CodExt. Some occurences (not all) of these column are populated
separately from a third table "Parameters" (with column RubrUser in the
PK) in an UPDATE statement of the following pattern :
db1 eval "SELECT Parameters.RubrUser, Parameters.CodRecap1,
Rubriques.CodRecap2, Rubriques.CodRecap3, Rubriques.CodExt
FROM Parameters \
WHERE (Parameters.CodRecap1 <> '')" {
db1 eval "UPDATE BulletinsTemp0 \
SET CodRecap1 = '$CodRecap1', \
CodRecap2 = '$CodRecap2', \
CodRecap3 = '$CodRecap3', \
CodExt= '$CodExt' \
WHERE (BulletinsTemp0.RubrUser = '$RubrUser')"
}
The issue is in "WHERE (BulletinsTemp0.RubrUser = '$RubrUser')" ->
'$RubrUser' matched only the last occurence of the same value in
BulletinsTemp0.RubrUser (december)
As said, we tried to reproduce in test scripts against new tables
created for this purpose but with no success.
But we could reproduce the issue when running manualy (on the sql
console of our app) the folowing :
UPDATE BulletinsTemp0 \
SET CodRecap1 = 'ZZZ' \
WHERE (BulletinsTemp0.RubrUser = '0000')
12 records (one per month) were present in BulletinsTemp0. whith
BulletinsTemp0.RubrUser = '0000' and on each try, only the last one
(december) was updated.
Strangely when doing from the console "SELECT 1 FROM BulletinsTemp0
WHERE (BulletinsTemp0.RubrUser = '0000')", the correct answer returned
(12 records) (sorry I forgot to say that in my first post - little tired
currently...)
Strangely when trying to run manualy :
UPDATE BulletinsTemp0 \
SET CodRecap1 = 'ZZZ' \
WHERE (BulletinsTemp0.Nature = 'BRUT')
i.e. with a criteria on an other column than RubrUser, the instruction
yielded the correct result.
We failed to find an other column except "RubrUser" in BulletinsTemp0
to reproduce the pb.
Completing the info : the same script ran with no pb against another db
with exactly the same shema (but different data and maybe ANALYZE run
more frequently) a couple of days before , so we first thought of a bug
in our app.
I suspect the content of statistics held by SQLite is involved : could
obsolete gathering made by old version of sqlite do mislead newer
version of the library ?
Thank you.
Jean-Marie
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
