uwdb / Cosette

Cosette is an automated SQL solver.
BSD 2-Clause "Simplified" License
662 stars 54 forks source link

Feature request: Not Exists #58

Open Njanderson opened 6 years ago

Njanderson commented 6 years ago

I would expect the following queries to be comparable, but alas I think NOT EXISTS does not parse.

schema sch_flights(fid:int,
    year:int,
    month_id:int,
    day_of_month:int,
    day_of_week_id:int,
    carrier_id:int,
    flight_num:int,
    origin_city:int,
    origin_state:int,
    dest_city:int,
    dest_state:int,
    departure_delay:int,
    taxi_out:int,
    arrival_delay:int,
    canceled:int,
    actual_time:int,
    distance:int
);

schema sch_carriers
(
    cid:int,
    name:int
);

SCHEMA sch_months
(
    mid:int,
    month:int
);

SCHEMA sch_days
(
    did:int,
    day_of_week:int
);

table Flights(sch_flights);
table Carriers(sch_carriers);
table Weekdays(sch_days);
table Months(sch_months);

query q1 `select distinct F1.origin_city as orig from Flights F1
where not exists(   select * from Flights F2
                    where F2.actual_time >= 180
                    and F2.origin_city = F1.origin_city )`;

query q2                -- define query q2 likewise
`select distinct F1.origin_city as orig
from Flights F1
where not exists(   select * from Flights F2
                    where F2.actual_time >= 180
                    and F2.origin_city = F1.origin_city )`;

verify q1 q2;           -- does q1 equal to q2?