[Info-Ingres] Most outrageous query
Martin Bowes
Hi All,
Lets start the competition for the most outrageous query….
Here is a beauty that Ingres 9.1 refused to look at. The programmer was almost offended by this. I had to upgrade the installation to 9.2.0 just to get it to optimize…that took half an hour.
I have no idea what he is trying to achieve, not do I want to know!
Martin Bowes
CREATE TABLE dataset AS
SELECT Questionnaires.StudyID,
extra_data.region_code,
extra_data.region_is_urban,
Consent2.Is_female,
extra_data.dob,
extra_data.study_date,
extra_data.age_at_study_date,
Consent2.NID,
Questionnaires.Highest_education,
Questionnaires.Occupation,
Questionnaires.Household_size,
Questionnaires.Marital_status,
Questionnaires.Household_income,
CASE WHEN ep0001.studyid IS NULL THEN 0 ELSE 1 END ep0001,
IFNULL(ep0001.date_developed, extra_data.censoring_date) ep0001_date,
CASE WHEN ep0002.studyid IS NULL THEN 0 ELSE 1 END ep0002,
IFNULL(ep0002.date_developed, extra_data.censoring_date) ep0002_date,
CASE WHEN ep0003.studyid IS NULL THEN 0 ELSE 1 END ep0003,
IFNULL(ep0003.date_developed, extra_data.censoring_date) ep0003_date,
CASE WHEN ep0004.studyid IS NULL THEN 0 ELSE 1 END ep0004,
IFNULL(ep0004.date_developed, extra_data.censoring_date) ep0004_date,
CASE WHEN ep0005.studyid IS NULL THEN 0 ELSE 1 END ep0005,
IFNULL(ep0005.date_developed, extra_data.censoring_date) ep0005_date,
CASE WHEN ep0006.studyid IS NULL THEN 0 ELSE 1 END ep0006,
IFNULL(ep0006.date_developed, extra_data.censoring_date) ep0006_date,
CASE WHEN ep0007.studyid IS NULL THEN 0 ELSE 1 END ep0007,
IFNULL(ep0007.date_developed, extra_data.censoring_date) ep0007_date,
CASE WHEN ep0008.studyid IS NULL THEN 0 ELSE 1 END ep0008,
IFNULL(ep0008.date_developed, extra_data.censoring_date) ep0008_date,
CASE WHEN ep0009.studyid IS NULL THEN 0 ELSE 1 END ep0009,
IFNULL(ep0009.date_developed, extra_data.censoring_date) ep0009_date,
CASE WHEN ep0010.studyid IS NULL THEN 0 ELSE 1 END ep0010,
IFNULL(ep0010.date_developed, extra_data.censoring_date) ep0010_date,
CASE WHEN ep0011.studyid IS NULL THEN 0 ELSE 1 END ep0011,
IFNULL(ep0011.date_developed, extra_data.censoring_date) ep0011_date,
CASE WHEN ep0012.studyid IS NULL THEN 0 ELSE 1 END ep0012,
IFNULL(ep0012.date_developed, extra_data.censoring_date) ep0012_date,
CASE WHEN ep0013.studyid IS NULL THEN 0 ELSE 1 END ep0013,
IFNULL(ep0013.date_developed, extra_data.censoring_date) ep0013_date,
CASE WHEN ep0014.studyid IS NULL THEN 0 ELSE 1 END ep0014,
IFNULL(ep0014.date_developed, extra_data.censoring_date) ep0014_date,
CASE WHEN ep0015.studyid IS NULL THEN 0 ELSE 1 END ep0015,
IFNULL(ep0015.date_developed, extra_data.censoring_date) ep0015_date,
CASE WHEN ep0016.studyid IS NULL THEN 0 ELSE 1 END ep0016,
IFNULL(ep0016.date_developed, extra_data.censoring_date) ep0016_date,
CASE WHEN ep0017.studyid IS NULL THEN 0 ELSE 1 END ep0017,
IFNULL(ep0017.date_developed, extra_data.censoring_date) ep0017_date,
CASE WHEN ep0018.studyid IS NULL THEN 0 ELSE 1 END ep0018,
IFNULL(ep0018.date_developed, extra_data.censoring_date) ep0018_date,
CASE WHEN ep0019.studyid IS NULL THEN 0 ELSE 1 END ep0019,
IFNULL(ep0019.date_developed, extra_data.censoring_date) ep0019_date,
CASE WHEN ep0020.studyid IS NULL THEN 0 ELSE 1 END ep0020,
IFNULL(ep0020.date_developed, extra_data.censoring_date) ep0020_date,
CASE WHEN ep0021.studyid IS NULL THEN 0 ELSE 1 END ep0021,
IFNULL(ep0021.date_developed, extra_data.censoring_date) ep0021_date,
CASE WHEN ep0022.studyid IS NULL THEN 0 ELSE 1 END ep0022,
IFNULL(ep0022.date_developed, extra_data.censoring_date) ep0022_date,
CASE WHEN ep0023.studyid IS NULL THEN 0 ELSE 1 END ep0023,
IFNULL(ep0023.date_developed, extra_data.censoring_date) ep0023_date,
CASE WHEN ep0024.studyid IS NULL THEN 0 ELSE 1 END ep0024,
IFNULL(ep0024.date_developed, extra_data.censoring_date) ep0024_date,
CASE WHEN ep0025.studyid IS NULL THEN 0 ELSE 1 END ep0025,
IFNULL(ep0025.date_developed, extra_data.censoring_date) ep0025_date,
CASE WHEN ep0026.studyid IS NULL THEN 0 ELSE 1 END ep0026,
IFNULL(ep0026.date_developed, extra_data.censoring_date) ep0026_date,
CASE WHEN ep0027.studyid IS NULL THEN 0 ELSE 1 END ep0027,
IFNULL(ep0027.date_developed, extra_data.censoring_date) ep0027_date,
CASE WHEN ep0028.studyid IS NULL THEN 0 ELSE 1 END ep0028,
IFNULL(ep0028.date_developed, extra_data.censoring_date) ep0028_date,
CASE WHEN ep0029.studyid IS NULL THEN 0 ELSE 1 END ep0029,
IFNULL(ep0029.date_developed, extra_data.censoring_date) ep0029_date,
CASE WHEN ep0030.studyid IS NULL THEN 0 ELSE 1 END ep0030,
IFNULL(ep0030.date_developed, extra_data.censoring_date) ep0030_date,
CASE WHEN ep0031.studyid IS NULL THEN 0 ELSE 1 END ep0031,
IFNULL(ep0031.date_developed, extra_data.censoring_date) ep0031_date,
CASE WHEN ep0032.studyid IS NULL THEN 0 ELSE 1 END ep0032,
IFNULL(ep0032.date_developed, extra_data.censoring_date) ep0032_date,
CASE WHEN ep0033.studyid IS NULL THEN 0 ELSE 1 END ep0033,
IFNULL(ep0033.date_developed, extra_data.censoring_date) ep0033_date,
CASE WHEN ep0034.studyid IS NULL THEN 0 ELSE 1 END ep0034,
IFNULL(ep0034.date_developed, extra_data.censoring_date) ep0034_date,
CASE WHEN ep0035.studyid IS NULL THEN 0 ELSE 1 END ep0035,
IFNULL(ep0035.date_developed, extra_data.censoring_date) ep0035_date,
CASE WHEN ep0036.studyid IS NULL THEN 0 ELSE 1 END ep0036,
IFNULL(ep0036.date_developed, extra_data.censoring_date) ep0036_date,
CASE WHEN ep0037.studyid IS NULL THEN 0 ELSE 1 END ep0037,
IFNULL(ep0037.date_developed, extra_data.censoring_date) ep0037_date,
CASE WHEN ep0038.studyid IS NULL THEN 0 ELSE 1 END ep0038,
IFNULL(ep0038.date_developed, extra_data.censoring_date) ep0038_date,
CASE WHEN ep0039.studyid IS NULL THEN 0 ELSE 1 END ep0039,
IFNULL(ep0039.date_developed, extra_data.censoring_date) ep0039_date,
CASE WHEN ep0040.studyid IS NULL THEN 0 ELSE 1 END ep0040,
IFNULL(ep0040.date_developed, extra_data.censoring_date) ep0040_date,
CASE WHEN ep0041.studyid IS NULL THEN 0 ELSE 1 END ep0041,
IFNULL(ep0041.date_developed, extra_data.censoring_date) ep0041_date,
CASE WHEN ep0042.studyid IS NULL THEN 0 ELSE 1 END ep0042,
IFNULL(ep0042.date_developed, extra_data.censoring_date) ep0042_date,
CASE WHEN ep0043.studyid IS NULL THEN 0 ELSE 1 END ep0043,
IFNULL(ep0043.date_developed, extra_data.censoring_date) ep0043_date,
CASE WHEN ep0044.studyid IS NULL THEN 0 ELSE 1 END ep0044,
IFNULL(ep0044.date_developed, extra_data.censoring_date) ep0044_date,
CASE WHEN ep0045.studyid IS NULL THEN 0 ELSE 1 END ep0045,
IFNULL(ep0045.date_developed, extra_data.censoring_date) ep0045_date,
CASE WHEN ep0046.studyid IS NULL THEN 0 ELSE 1 END ep0046,
IFNULL(ep0046.date_developed, extra_data.censoring_date) ep0046_date
FROM extra_data
LEFT JOIN alc_details ON extra_data.studyid = alc_details.studyid
LEFT JOIN before_prev_house_details ON extra_data.studyid = before_prev_house_details.studyid
LEFT JOIN blood_retests ON extra_data.studyid = blood_retests.studyid
LEFT JOIN blood_tests ON extra_data.studyid = blood_tests.studyid
LEFT JOIN cidi_a ON extra_data.studyid = cidi_a.studyid
LEFT JOIN cidi_b ON extra_data.studyid = cidi_b.studyid
LEFT JOIN consent2 ON extra_data.studyid = consent2.studyid AND extra_data.latest_consent_date =
consent2.creation_date
LEFT JOIN diet_spice_details ON extra_data.studyid = diet_spice_details.studyid
LEFT JOIN farm_phys_activities ON extra_data.studyid = farm_phys_activities.studyid
LEFT JOIN lt_events on extra_data.latest_event_kuid = lt_events.lt_event_kuid
LEFT JOIN lt_death_events on extra_data.latest_event_kuid = lt_death_events.lt_event_kuid
LEFT JOIN phys_exam_common ON extra_data.studyid = phys_exam_common.studyid
LEFT JOIN prev_house_details ON extra_data.studyid = prev_house_details.studyid
LEFT JOIN questionnaires ON extra_data.studyid = questionnaires.studyid
LEFT JOIN reproductive_histories ON extra_data.studyid = reproductive_histories.studyid
LEFT JOIN section_timings ON extra_data.studyid = section_timings.studyid
LEFT JOIN smoking_details ON extra_data.studyid = smoking_details.studyid
LEFT JOIN tea_details ON extra_data.studyid = tea_details.studyid
LEFT JOIN urban_phys_activities ON extra_data.studyid = urban_phys_activities.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 1) ep0001 ON extra_data.studyid =
ep0001.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 2) ep0002 ON extra_data.studyid =
ep0002.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 3) ep0003 ON extra_data.studyid =
ep0003.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 4) ep0004 ON extra_data.studyid =
ep0004.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 5) ep0005 ON extra_data.studyid =
ep0005.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 6) ep0006 ON extra_data.studyid =
ep0006.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 7) ep0007 ON extra_data.studyid =
ep0007.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 8) ep0008 ON extra_data.studyid =
ep0008.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 9) ep0009 ON extra_data.studyid =
ep0009.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 10) ep0010 ON extra_data.studyid =
ep0010.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 11) ep0011 ON extra_data.studyid =
ep0011.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 12) ep0012 ON extra_data.studyid =
ep0012.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 13) ep0013 ON extra_data.studyid =
ep0013.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 14) ep0014 ON extra_data.studyid =
ep0014.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 15) ep0015 ON extra_data.studyid =
ep0015.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 16) ep0016 ON extra_data.studyid =
ep0016.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 17) ep0017 ON extra_data.studyid =
ep0017.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 18) ep0018 ON extra_data.studyid =
ep0018.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 19) ep0019 ON extra_data.studyid =
ep0019.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 20) ep0020 ON extra_data.studyid =
ep0020.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 21) ep0021 ON extra_data.studyid =
ep0021.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 22) ep0022 ON extra_data.studyid =
ep0022.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 23) ep0023 ON extra_data.studyid =
ep0023.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 24) ep0024 ON extra_data.studyid =
ep0024.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 25) ep0025 ON extra_data.studyid =
ep0025.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 26) ep0026 ON extra_data.studyid =
ep0026.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 27) ep0027 ON extra_data.studyid =
ep0027.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 28) ep0028 ON extra_data.studyid =
ep0028.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 29) ep0029 ON extra_data.studyid =
ep0029.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 30) ep0030 ON extra_data.studyid =
ep0030.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 31) ep0031 ON extra_data.studyid =
ep0031.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 32) ep0032 ON extra_data.studyid =
ep0032.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 33) ep0033 ON extra_data.studyid =
ep0033.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 34) ep0034 ON extra_data.studyid =
ep0034.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 35) ep0035 ON extra_data.studyid =
ep0035.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 36) ep0036 ON extra_data.studyid =
ep0036.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 37) ep0037 ON extra_data.studyid =
ep0037.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 38) ep0038 ON extra_data.studyid =
ep0038.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 39) ep0039 ON extra_data.studyid =
ep0039.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 40) ep0040 ON extra_data.studyid =
ep0040.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 41) ep0041 ON extra_data.studyid =
ep0041.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 42) ep0042 ON extra_data.studyid =
ep0042.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 43) ep0043 ON extra_data.studyid =
ep0043.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 44) ep0044 ON extra_data.studyid =
ep0044.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 45) ep0045 ON extra_data.studyid =
ep0045.studyid
LEFT JOIN (SELECT studyid, date_developed FROM endpoints WHERE endpoint = 46) ep0046 ON extra_data.studyid =
ep0046.studyid
WHERE extra_data.keep = 1;
rthdavid
Ouch!
rthdavid
Wow!
Can you find a way to sharing the QEP and QE90 of that evil sucker??
Karl Schendel
On Nov 23, 2009, at 4:47 AM, Martin Bowes wrote:
>
> Lets start the competition for the most outrageous query�.
>
I think one of the craziest queries I ever saw was 40k worth
of SELECT result-list. It "only" had 350 result columns (some
queries from the same client had over 2000 result columns, we
had a special Ingres built for them), but this query had
so many deeply nested CASE and COALESCE expressions
that it blew out Ingres's query compiler. It turned out that
it was trying to generate more than 32K instructions
into the result-list compiled expression, and the CX
instruction counter was an i2...
That query ended with: FROM view. No where clause.
The view had been generated by starting with some
base tables t1, t2, ... and doing:
create view v1 as select <lots> from t1;
create view v2 as select <lots>,v1.* from v1 join t2 on <2 columns>;
create view v3 as select <lots>,v2.* from v2 join t3 on <2 columns>;
<repeat 4 more times>
and the end result view containing several thousand columns
was what the crashing query was selecting from.
Amazing stuff. I can't post any of the actual queries but
trust me, you wouldn't want to look at them.
Karl
OldSchool
"Martin Bowes" <martin...@ctsu.ox.ac.uk> wrote in message
news:mailman.1186.1258969...@kettleriverconsulting.com...
>Hi All,
>
>Lets start the competition for the most outrageous query�.
>
>Here is a beauty that Ingres 9.1 refused to look at. The programmer was
>almost offended by this. I had to upgrade the >installation to 9.2.0 just
>to get it to optimize�that took half an hour.
Wrong approach.... I would've "upgraded" the programmer.
>I have no idea what he is trying to achieve, not do I want to know!
I wouldn't feel *bad* about that....I'd expect the programmer has no idea
what it will *produce*, either.
> Martin Bowes
>
>CREATE TABLE dataset AS
.....
>WHERE extra_data.keep = 1;
If it hasn't occurred already, said programmer will most likely be back,
demanding that you "tune" the db, as "performance is terrible / attrocious /
abmismal".
You do get bonus points for not having eliminated the source of the issue
(and I'm not referring to the po
James K. Lowden
>
> >I have no idea what he is trying to achieve, not do I want to know!
>
> I wouldn't feel *bad* about that....I'd expect the programmer has no
> idea what it will *produce*, either.
I assumed that query wasn't written, but rather generated by a ORM or
something. Was it actually the product of human intelligence?
--jkl
Jürgen Cappel
is a contradiction in terms
:-)
JC
-------- Original-Nachricht --------
Betreff: Re: [Info-Ingres] Most outrageous query
Datum: Mon, 23 Nov 2009 21:21:53 -0500
Von: James K. Lowden <jklo...@schemamania.org>
Antwort an: Ingres and related product discussion forum
<info-...@kettleriverconsulting.com>
An: Ingres and related product discussion forum
<info-...@kettleriverconsulting.com>
Referenzen:
<E7321E45EFCFAD4C995A...@exch-p202.ctsu.ox.ac.uk>
<mailman.1188.1258990...@kettleriverconsulting.com>
<heehlk$7j3$1...@news.eternal-september.org>
--jkl
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Martin Bowes
It was hand generated. Tenacious little bugger isn't he.
Marty
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of
James K. Lowden
Sent: 24 November 2009 02:22
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Most outrageous query
OldSchool wrote:
>
> >I have no idea what he is trying to achieve, not do I want to know!
>
> I wouldn't feel *bad* about that....I'd expect the programmer has no
> idea what it will *produce*, either.
I assumed that query wasn't written, but rather generated by a ORM or
Gorby
attributes.
Surely there is an easier way to "pull a bird?"
cheers
Gordon
