SQL Anywhere 11: Select brings inconsistent results when database cache is low

[Former Member]

We have a complex sql query with five subquerys and the group by clause. If I restrict the database server cache to 16m (with the option "-ch 16m") we get randomized different results from the same sql query (these issue is reproducible on different machines). Our customer have the same issue on his system without reducing the cache with the -ch option and with 8g of free memory.

What is a possible explanation for such a behavior?

We think that it would be better to get a warning message or a database server crash than inconsistent results.

• DB-Version: 11.0.1.2744
• DB-Size: ~1g
• We tested it also with the newest 11.0.1.XXXX EBF release.

Here the sql:

SELECT (select year(beginnt) * 100 + month ( beginnt ) FROM geschaeftsjahr WHERE beginnt <= '2012-06-30' and 
 endet >= '2012-06-30' and 
 mandant_nr = i1_mandant_nr) geschaeftsjahresbeginn_vorjahr_yyyymm, 
(select beginnt from geschaeftsjahr where beginnt <= '2012-06-30' and 
 endet >= '2012-06-30' and 
 mandant_nr = i1_mandant_nr) geschaeftsjahresbeginn_vorjahr_datum, 
(select year(beginnt) * 100 + month ( beginnt ) from geschaeftsjahr where beginnt <= '2013-06-30' and 
 endet >= '2013-06-30' and 
 mandant_nr = i1_mandant_nr) geschaeftsjahresbeginn_yyyymm, 
(select beginnt from geschaeftsjahr where beginnt <= '2013-06-30' and 
 endet >= '2013-06-30' and 
 mandant_nr = i1_mandant_nr) geschaeftsjahresbeginn_datum, 
"v_kostenstellenplan"."ks1_kstnummer", 
"v_kostenstellenplan"."ks1_bezeichnung", 
isnull(sum ( if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+month('2013-06-01') then if not isnull("bereich",'') = 'C' then "ksb1_betrag" else 0 endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_kosten", 
isnull(sum ( if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+month('2013-06-01') then if isnull("bereich",'') = 'C' then "ksb1_betrag" else 0 endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_ertrag", 
betrag_kosten + betrag_ertrag "betrag", 
isnull(sum ( if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+01 then if not isnull("bereich",'') = 'C' then "ksb1_betrag" else 0 endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_kosten_jahresbeginn", 
isnull(sum ( if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+01 then if isnull("bereich",'') = 'C' then "ksb1_betrag" else 0 endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_ertrag_jahresbeginn", 
betrag_kosten_jahresbeginn + betrag_ertrag_jahresbeginn "betrag_jahresbeginn", 
isnull(sum ( if not isnull("bereich",'') = 'C' then if isnull("ks1_kumulation",'g') = 'a' then /*seit auftragsbeginn*/ "ksb1_betrag" else /*seit Jahresbeginn*/ if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+01 then "ksb1_betrag" else 0 endif endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_kosten_baubeginn", 
isnull(sum ( if isnull("bereich",'') = 'C' then if isnull("ks1_kumulation",'g') = 'a' then /*seit auftragsbeginn*/ "ksb1_betrag" else /*nur Vorjahreszahlen*/ if ksb1_jahr*100+ksb1_monat >= year('2013-06-01')*100+01 then "ksb1_betrag" else 0 endif endif else 0 endif * get_vorzeichen_kore(ksb1_mandant_nr, 
ksb1_kontonummer)) ,0) "betrag_ertrag_baubeginn", 
betrag_kosten_baubeginn + betrag_ertrag_baubeginn "betrag_baubeginn", 
isnull(sum ( "ksb1_betrag" ),0) "betrag_vorjahr", 
count() count, 
"i_daten"."i1_name", 
"i_daten"."i1_mandant_nr", 
"i_daten"."i1f1_zahlenformat_auswertung", 
isnull(v_kostenstellenplan.ks1_inaktiv, 
'n' ) kst_inaktiv, 
"v_kostenstellenplan"."kl_nr1", 
"v_kostenstellenplan"."kl_bez1", 
"v_kostenstellenplan"."kl_bez_ergebnis1", 
"v_kostenstellenplan"."kl_nr2", 
"v_kostenstellenplan"."kl_bez2", 
"v_kostenstellenplan"."kl_bez_ergebnis2", 
"v_kostenstellenplan"."kl_nr3", 
"v_kostenstellenplan"."kl_bez3", 
"v_kostenstellenplan"."kl_bez_ergebnis3", 
"v_kostenstellenplan"."kl_nr4", 
"v_kostenstellenplan"."kl_bez4", 
"v_kostenstellenplan"."kl_bez_ergebnis4", 
"v_kostenstellenplan"."kl_nrn", 
"v_kostenstellenplan"."ks1_typ", 
"v_kostenstellenplan"."ks1_kumulation", 
isnull((select k.ks1_verantwortlicher from kostenstelle k where k.ks1_kstnummer = "v_kostenstellenplan"."ks1_kstnummer" and 
 k.ks1_mandant_nr = "v_kostenstellenplan"."ks1_mandant_nr"),'') kst_verantwortlicher FROM "v_kostenstellenplan" join "konto_kst_statistik_budget" on "v_kostenstellenplan"."ks1_mandant_nr" = "konto_kst_statistik_budget"."ksb1_mandant_nr" and 
 "v_kostenstellenplan"."ks1_kstnummer" = "konto_kst_statistik_budget"."ksb1_kostenstelle" and 
 "v_kostenstellenplan"."kl1_typ" = 'ks2' join "konto" on "konto"."kontonummer" = "konto_kst_statistik_budget"."ksb1_kontonummer" and 
 "konto"."mandant_nr" = "konto_kst_statistik_budget"."ksb1_mandant_nr" join "i_daten" on "konto"."mandant_nr" = "i_daten"."i1_mandant_nr" where not ksb1_quelle = string(char(70), 
char(51)) and 
 not "left"(ksb1_quelle,1) = CHAR(66) and 
 string('D1;D2;D3;F1;F2;F4;F5;F50;F51;F52;K1;K2;L1;N1;N2;N3;N4;N5;O1;O2;P1;', 
'F20;F21;F23;') like string(char(37),ksb1_quelle, 
char(59) , 
char(37)) and 
 /*Seit Baubeginn oder Geschäftsjahresbeginn*/ ksb1_jahr*100+ksb1_monat >= if isnull("v_kostenstellenplan"."ks1_kumulation",'g') = 'a' then 0 else year('2012-06-01')*100+month('2012-06-01') end if and 
 ksb1_jahr*100+ksb1_monat <= year('2013-06-30')*100+month('2013-06-30') and
 "kontonummer" not in (907010,997010,907011,997011 ) and 
 ks1_kstnummer in (select kk.kk1_kostenstelle from kostenstelle_Klassierung kk join klassierung_typ on kk1_typ = kt1_typ join klassierung on kl1_id = kk1_klassierung_id where kk1_mandant_nr = isnull(i1_kostenstellen_vererben_von_mandant_nr, 
i1_mandant_nr) and 
 kk1_typ = 'kst' and 
 kl1_nummer >= '7150' and 
 kl1_nummer <= '7150' ) and 
 i1_mandant_nr in (13) GROUP BY "v_kostenstellenplan"."ks1_kstnummer", 
"v_kostenstellenplan"."ks1_bezeichnung", 
"i_daten"."i1_name", 
"i_daten"."i1_mandant_nr", 
"i_daten"."i1f1_zahlenformat_auswertung", 
"v_kostenstellenplan"."ks1_inaktiv", 
"v_kostenstellenplan"."kl_nr1", 
"v_kostenstellenplan"."kl_bez1", 
"v_kostenstellenplan"."kl_bez_ergebnis1", 
"v_kostenstellenplan"."kl_nr2", 
"v_kostenstellenplan"."kl_bez2", 
"v_kostenstellenplan"."kl_bez_ergebnis2", 
"v_kostenstellenplan"."kl_nr3", 
"v_kostenstellenplan"."kl_bez3", 
"v_kostenstellenplan"."kl_bez_ergebnis3", 
"v_kostenstellenplan"."kl_nr4", 
"v_kostenstellenplan"."kl_bez4", 
"v_kostenstellenplan"."kl_bez_ergebnis4", 
"v_kostenstellenplan"."kl_nrn", 
"v_kostenstellenplan"."ks1_typ", 
"v_kostenstellenplan"."ks1_kumulation", 
ks1_mandant_nr HAVING ( "betrag_kosten" <> 0 OR "betrag_ertrag" <> 0 OR "betrag" <> 0 OR "betrag_kosten_jahresbeginn" <> 0 OR "betrag_ertrag_jahresbeginn" <> 0 OR "betrag_jahresbeginn" <> 0 OR "betrag_kosten_baubeginn" <> 0 OR "betrag_ertrag_baubeginn" <> 0 ) ORDER BY i_daten.i1_mandant_nr, 
"v_kostenstellenplan"."kl_nr1", 
"v_kostenstellenplan"."kl_nr2",
"v_kostenstellenplan"."kl_nr3", 
"v_kostenstellenplan"."kl_nr4", 
"ks1_kstnummer"

The column "betrag_kosten" for example delivers different results: Printscreen

Query plan with statistics (working and non-working with SQL Anywhere 11, generated with ISQL 16): Query Plan

[chris_keating]

Is this database something that you can provide for investigation?

If not, can you post the query plan (from dbisql as a graphical plan with statistics) for the working and non-working case?

[Former Member]

It's not possible to provide the database. I have updated my posts with the query plan (working and non-working). Thank you.

[chris_keating]

The query plans I wanted to see were from SA11 only for now.

[Former Member]

At this moment It's hard to produce a working query with SA11 (also if I restart the database server with "-c 1g"). But I think I was able to produce a working case. I have updated the link in my post.

[jeff_albion]

Your queries in both cases are not exactly the same - the 'working' case has the extra predicate:

 "kontonummer" not in (907010,997010,907011,997011 ) and

Also, the select list at the end is slightly different:

Working:

 "v_kostenstellenplan"."kl_nr1", 
 "v_kostenstellenplan"."kl_nr2",
 "v_kostenstellenplan"."kl_nr3", 
 "v_kostenstellenplan"."kl_nr4",

Not-Working:

 "v_kostenstellenplan"."kl_nr1", 
 "v_kostenstellenplan"."kl_nr2", 
 "v_kostenstellenplan"."kl_nr2", -- Selected twice
 "v_kostenstellenplan"."kl_nr3", 
 "v_kostenstellenplan"."kl_nr4",

Is that intentional?

[Former Member]

Sorry, it's not about this. I have started the "wrong" sql query. I have updated the file in my post.

[Breck_Carter]

The plans are "Optimizer estimates only". They both say RowsReturned 12 but AFAIK that is meaningless since there are no actual statistics gathered. IMO the plans should be created with "Detailed and node statistics".

[Former Member]

I did the "Detailed and node statistics" (as described in http://sqlanywhere.blogspot.ch/2009/10/how-to-capture-graphical-plan.html ). Do you have time for a remote session?

[Breck_Carter]

"Do you have time for a remote session?" ...send me an email so I can reply by email: breck dot carter at gmail

[Former Member]

If sharing the database is not possible, could you please try to simplify the query by eliminating tables one at a time and checking that the problem still reproduces? (this is something we would do internally if given the database)

You can restrict the amount of cache available by setting -c and -ch server options to a small value, maybe smaller than 1g.