|
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.
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 |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 22 Jul '13, 09:35
question was seen: 4,227 times
last updated: 23 Jul '13, 15:13
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?
It's not possible to provide the database. I have updated my posts with the query plan (working and non-working). Thank you.
The query plans I wanted to see were from SA11 only for now.
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.
Your queries in both cases are not exactly the same - the 'working' case has the extra predicate:
Also, the select list at the end is slightly different:
Working:
Not-Working:
Is that intentional?
Sorry, it's not about this. I have started the "wrong" sql query. I have updated the file in my post.
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".
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?
"Do you have time for a remote session?" ...send me an email so I can reply by email: breck dot carter at gmail
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.