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

Reply via email to