Re: Fwd: Re: Re: [Bug in UNION clause]
am 12.08.2002 21:28:30 von Sinisa Milivojevic
Cliff writes:
> oops sorry, I am messing up like crazy today =). Here is the query:
>
> (SELECT
> allusa.id,line,name,city,country,listcity,state,liststate,ur l,line,bookable,
> cost_range_low,cost_range_high,showlink,desc1,child,pets,wc, spec.id,spec.his
> toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun ,spec.spas,spec.
> luxury_,spec.historic FROM temp.allusa LEFT JOIN temp.spec on
> allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by
> state,city,name) UNION (SELECT
> allusa.id,line,name,city,country,listcity,state,liststate,ur l,line,bookable,
> cost_range_low,cost_range_high,showlink,desc1,child,pets,wc, spec.id,spec.his
> toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun ,spec.spas,spec.
> luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id
> WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR
> state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name LIKE
> '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR
> state='francisco' OR statename LIKE '%francisco%' OR country LIKE
> '%francisco%' OR name LIKE '%francisco%')ORDER BY state,city,name )
Hi!
I have just tested your case and I get the following result :
------------------------------------------------------------ --------------------------
id line name city country listcity state liststate url line bookable cost_range_low cost_range_high showlink desc1 child pets wc id historic skiing romance antiques family_fun spas luxury_ historic
8647 FULL 24 Henry Guesthouse San Francisco USA San Francisco CA CA http://www.24Henry.com FULL 65.00 109.00 1 24 Henry Guesthouse is located just a few short blocks from the heart of the Castro District. There are 5 beautifully furnished rooms in this Victorian home, dating from the 1880's. 8647 NULL NULL NULL NULL NULL NULL NULL NULL
3082 FULL Amsterdam Hotel San Francisco USA San Francisco CA CA http://www.amsterdamhotel.com FULL 99.00 149.00 1 Located on Nob Hill. Quality accommodations and friendly service provided at modest rates. A little bit of Europe in America. Near Union Square, Financial District, cable car. yes no no 3082 NULL NULL NULL NULL NULL NULL NULL NULL
877 FULL Annie's Cottage San Francisco USA San Francisco CA CA http://anniescottage.com FULL 140.00 0.00 1 Guestroom with private entrance, private bath, sitting area, deck, refrigerator/microwave. Country hideaway in the middle of San Francisco. Furnished with antiques. no no 877 NULL NULL NULL NULL NULL NULL NULL NULL
20353 FULL Artists Inn San Francisco USA San Francisco CA CA http://www.artistsinn.com FULL 170.00 0.00 1 Artists Inn is a charming 19th century farm house and an artist's studio building on either side of a secluded garden courtyard. Located in Pacific Heights in the heart of SF. ltd no no 20353 NULL NULL NULL NULL NULL NULL NULL NULL
3073 FULL Bock's Bed & Breakfast San Francisco USA San Francisco CA CA http://www.sfgate.com/listings/venue.php?lodging,m46557 FULL 75.00 90.00 0 Restored 1906 Edwardian residence. Splendid view, decks, private phones, TVs and clock radios in room. Golden Gate Park and excellent public transportation nearby yes no no 3073 NULL NULL NULL NULL NULL NULL NULL NULL
25899 FULL Castillo Inn San Francisco USA San Francisco CA CA FULL 60.00 80.00 0 Gay accommodations mainly. Centrally located close to Castro and Market Streets. no no 25899 NULL NULL NULL NULL NULL NULL NULL NULL
964 FULL Country Cottage San Francisco USA San Francisco CA CA http://www.bbsf.com/country.html FULL 89.00 0.00 1 The Country Cottage B&B is a cozy country-style B&B in the heart of San Francisco. The four guestrooms are comfortabely furnished in American country antiques and brass beds. 964 NULL NULL NULL NULL NULL NULL NULL NULL
1968 FULL Edward II Inn & Suites San Francisco USA San Francisco CA CA http://www.edwardii.com FULL 85.00 235.00 1 Edward II Inn is a three-story European-style inn, situated only blocks from San Francisco's yacht harbor. yes no ltd 1968 NULL NULL NULL NULL NULL NULL NULL NULL
1963 FULL Garden Studio, The San Francisco USA San Francisco CA CA FULL 110.00 0.00 0 Studio apartment opens to garden, has private entrance, private bath, queen-sized bed. On lower level of charming Edwardian home; 2 blocks from Golden Gate Park. yes no no 1963 NULL NULL NULL NULL NULL NULL NULL NULL
16074 FULL Inn 1890 San Francisco USA San Francisco CA CA http://www.inn1890.com FULL 89.00 159.00 1 Landmark Victorian Inn blending old elegance w/modern comforts. Centrally located, convenient to all SF destinations. yes ltd no 16074 NULL NULL NULL NULL NULL NULL NULL NULL
1964 FULL Moffatt House San Francisco USA San Francisco CA CA http://www.moffatthouse.com FULL 79.00 99.00 1 Walk to Golden Gate Park's major attractions from our Arts & Crafts home. Safe location for active, independent guests. yes no no 1964 NULL NULL NULL NULL NULL NULL NULL NULL
1959 FULL Stanyan Park Hotel San Francisco USA San Francisco CA CA http://www.stanyanpark.com FULL 130.00 315.00 1 The Stanyan Park Hotel is an elegant, thoroughly restored Victorian Hotel that will take you back to a bygone era of style, grace and comfort. yes no yes 1959 NULL NULL NULL NULL NULL NULL NULL NULL
11154 FULL Subtleties: Carol's Cow Hollow Inn San Francisco USA San Francisco CA CA http://www.subtleties.com FULL 150.00 260.00 1 Charm in the heart of Pacific Heights. Memorable views and cuisine, original art. yes no no 11154 NULL NULL NULL NULL NULL NULL NULL NULL
7897 FULL The Andrews Hotel San Francisco USA San Francisco CA CA http://www.andrewshotel.com FULL 99.00 175.00 1 European-style charm in the heart of Union Square shopping and theater district. Features architectural bay windows. yes no no 7897 NULL NULL NULL NULL NULL NULL NULL NULL
7181 FULL The Golden Gate Hotel San Francisco USA San Francisco CA CA http://www.goldengatehotel.com FULL 85.00 130.00 1 Charming turn-of-the-century hotel. Friendly atmosphere. Antique furnishings, fresh flowers. Ideal Nob Hill location, corner cable car stop. yes no no 7181 NULL NULL NULL NULL NULL NULL NULL NULL
1953 FULL The Grove Inn San Francisco USA San Francisco CA CA http://www.grovinn.com FULL 80.00 150.00 1 Turn-of-the-century Victorian, fully restored, simply furnished with bay windows. Community kitchen, refrigerator. Part of Alamo Square Historic District. yes no no 1953 NULL NULL NULL NULL NULL NULL NULL NULL
1967 FULL The Inn San Francisco USA San Francisco CA CA http://www.thebandb.com FULL 80.00 420.00 1 One of San Francisco's best kept secrets, we offer a variety of charming accommodations on a secluded courtyard, just a corner's turn from the excitement of Union Street. 1967 NULL NULL NULL NULL NULL NULL NULL NULL
1934 FULL The Inn at Union Square San Francisco USA San Francisco CA CA http://www.unionsquare.com FULL 195.00 375.00 1 An elegant, cozy European-style hotel in the heart of San Francisco. A half-block from Union Square, & close to the finest restaurants, best shopping & wonderful theater. Our location in unbeatable! ltd no yes 1934 NULL NULL NULL NULL NULL NULL NULL NULL
1942 FULL The Inn San Francisco San Francisco USA San Francisco CA CA http://www.innsf.com FULL 95.00 265.00 1 Authentic 1872 Italianate Victorian Mansion. Rich Victorian decor. English Garden with Redwood Hot Tub. Sundeck with spectacular SF vistas. Excellent restaurants nearby. Be welcomed as a friend. yes no no 1942 NULL NULL NULL NULL NULL NULL NULL NULL
1949 FULL The Monte Cristo San Francisco USA San Francisco CA CA http://www.virtualcities.com/ons/ca/b/cab3504.htm FULL 83.00 118.00 1 1875 hotel-saloon-bordello furnished with antiques. Each room uniquely decorated: Georgian four-poster, Chinese wedding bed, spindle bed. Personal service. yes no yes 1949 NULL NULL NULL NULL NULL NULL NULL NULL
1960 FULL Victorian Inn on the Park San Francisco USA San Francisco CA CA http://www.victorianinnonthepark.com FULL 159.00 199.00 1 1897 Queen Anne Victorian near Golden Gate Park, downtown. Each room has antiques, flowers, beautiful comforters, down pillows, phones. yes no no 1960 NULL NULL NULL NULL NULL NULL NULL NULL
------------------------------------------------------------ --------------------------
As you can see each of the above 22 rows is distinct.
To start with, every row has it's own allusa.id, which does not repeat
in any other row.
This fact alone makes all 22 rows distinct from each other.
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12351@lists.mysql.com
To unsubscribe, e-mail
Re: Fwd: Re: Re: [Bug in UNION clause]
am 13.08.2002 09:33:56 von Cliff
I don't understand then, I ran the *same* query on my machine with that
exact table dump and it returns 49 rows, 3 of which are repeats of ones that
were already returned. What else could it be?
----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc: ;
Sent: Monday, August 12, 2002 12:28 PM
Subject: Re: Fwd: Re: Re: [Bug in UNION clause]
> Cliff writes:
> > oops sorry, I am messing up like crazy today =). Here is the query:
> >
> > (SELECT
> >
allusa.id,line,name,city,country,listcity,state,liststate,ur l,line,bookable,
> >
cost_range_low,cost_range_high,showlink,desc1,child,pets,wc, spec.id,spec.his
> >
toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun ,spec.spas,spec.
> > luxury_,spec.historic FROM temp.allusa LEFT JOIN temp.spec on
> > allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by
> > state,city,name) UNION (SELECT
> >
allusa.id,line,name,city,country,listcity,state,liststate,ur l,line,bookable,
> >
cost_range_low,cost_range_high,showlink,desc1,child,pets,wc, spec.id,spec.his
> >
toric,spec.skiing,spec.romance,spec.antiques,spec.family_fun ,spec.spas,spec.
> > luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on
allusa.id=spec.id
> > WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR
> > state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name
LIKE
> > '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR
> > state='francisco' OR statename LIKE '%francisco%' OR country LIKE
> > '%francisco%' OR name LIKE '%francisco%')ORDER BY state,city,name )
>
> Hi!
>
> I have just tested your case and I get the following result :
>
>
> ------------------------------------------------------------ --------------
------------
>
> id line name city country listcity state liststate url line bookable
cost_range_low cost_range_high showlink desc1 child pets wc id historic
skiing romance antiques family_fun spas luxury_ historic
> 8647 FULL 24 Henry Guesthouse San Francisco USA San Francisco CA CA
http://www.24Henry.com FULL 65.00 109.00 1 24 Henry Guesthouse is located
just a few short blocks from the heart of the Castro District. There are 5
beautifully furnished rooms in this Victorian home, dating from the 1880's.
8647 NULL NULL NULL NULL NULL NULL NULL NULL
> 3082 FULL Amsterdam Hotel San Francisco USA San Francisco CA CA
http://www.amsterdamhotel.com FULL 99.00 149.00 1 Located on Nob Hill.
Quality accommodations and friendly service provided at modest rates. A
little bit of Europe in America. Near Union Square, Financial District,
cable car. yes no no 3082 NULL NULL NULL NULL NULL NULL NULL NULL
> 877 FULL Annie's Cottage San Francisco USA San Francisco CA CA
http://anniescottage.com FULL 140.00 0.00 1 Guestroom with private entrance,
private bath, sitting area, deck, refrigerator/microwave. Country hideaway
in the middle of San Francisco. Furnished with antiques. no no 877 NULL NULL
NULL NULL NULL NULL NULL NULL
> 20353 FULL Artists Inn San Francisco USA San Francisco CA CA
http://www.artistsinn.com FULL 170.00 0.00 1 Artists Inn is a charming 19th
century farm house and an artist's studio building on either side of a
secluded garden courtyard. Located in Pacific Heights in the heart of SF.
ltd no no 20353 NULL NULL NULL NULL NULL NULL NULL NULL
> 3073 FULL Bock's Bed & Breakfast San Francisco USA San Francisco CA CA
http://www.sfgate.com/listings/venue.php?lodging,m46557 FULL 75.00 90.00 0
Restored 1906 Edwardian residence. Splendid view, decks, private phones, TVs
and clock radios in room. Golden Gate Park and excellent public
transportation nearby yes no no 3073 NULL NULL NULL NULL NULL NULL NULL NULL
> 25899 FULL Castillo Inn San Francisco USA San Francisco CA CA FULL 60.00
80.00 0 Gay accommodations mainly. Centrally located close to Castro and
Market Streets. no no 25899 NULL NULL NULL NULL NULL NULL NULL NULL
> 964 FULL Country Cottage San Francisco USA San Francisco CA CA
http://www.bbsf.com/country.html FULL 89.00 0.00 1 The Country Cottage B&B
is a cozy country-style B&B in the heart of San Francisco. The four
guestrooms are comfortabely furnished in American country antiques and brass
beds. 964 NULL NULL NULL NULL NULL NULL NULL NULL
> 1968 FULL Edward II Inn & Suites San Francisco USA San Francisco CA CA
http://www.edwardii.com FULL 85.00 235.00 1 Edward II Inn is a three-story
European-style inn, situated only blocks from San Francisco's yacht harbor.
yes no ltd 1968 NULL NULL NULL NULL NULL NULL NULL NULL
> 1963 FULL Garden Studio, The San Francisco USA San Francisco CA CA FULL
110.00 0.00 0 Studio apartment opens to garden, has private entrance,
private bath, queen-sized bed. On lower level of charming Edwardian home; 2
blocks from Golden Gate Park. yes no no 1963 NULL NULL NULL NULL NULL NULL
NULL NULL
> 16074 FULL Inn 1890 San Francisco USA San Francisco CA CA
http://www.inn1890.com FULL 89.00 159.00 1 Landmark Victorian Inn blending
old elegance w/modern comforts. Centrally located, convenient to all SF
destinations. yes ltd no 16074 NULL NULL NULL NULL NULL NULL NULL NULL
> 1964 FULL Moffatt House San Francisco USA San Francisco CA CA
http://www.moffatthouse.com FULL 79.00 99.00 1 Walk to Golden Gate Park's
major attractions from our Arts & Crafts home. Safe location for active,
independent guests. yes no no 1964 NULL NULL NULL NULL NULL NULL NULL NULL
> 1959 FULL Stanyan Park Hotel San Francisco USA San Francisco CA CA
http://www.stanyanpark.com FULL 130.00 315.00 1 The Stanyan Park Hotel is an
elegant, thoroughly restored Victorian Hotel that will take you back to a
bygone era of style, grace and comfort. yes no yes 1959 NULL NULL NULL NULL
NULL NULL NULL NULL
> 11154 FULL Subtleties: Carol's Cow Hollow Inn San Francisco USA San
Francisco CA CA http://www.subtleties.com FULL 150.00 260.00 1 Charm in the
heart of Pacific Heights. Memorable views and cuisine, original art. yes no
no 11154 NULL NULL NULL NULL NULL NULL NULL NULL
> 7897 FULL The Andrews Hotel San Francisco USA San Francisco CA CA
http://www.andrewshotel.com FULL 99.00 175.00 1 European-style charm in the
heart of Union Square shopping and theater district. Features architectural
bay windows. yes no no 7897 NULL NULL NULL NULL NULL NULL NULL NULL
> 7181 FULL The Golden Gate Hotel San Francisco USA San Francisco CA CA
http://www.goldengatehotel.com FULL 85.00 130.00 1 Charming
turn-of-the-century hotel. Friendly atmosphere. Antique furnishings, fresh
flowers. Ideal Nob Hill location, corner cable car stop. yes no no 7181 NULL
NULL NULL NULL NULL NULL NULL NULL
> 1953 FULL The Grove Inn San Francisco USA San Francisco CA CA
http://www.grovinn.com FULL 80.00 150.00 1 Turn-of-the-century Victorian,
fully restored, simply furnished with bay windows. Community kitchen,
refrigerator. Part of Alamo Square Historic District. yes no no 1953 NULL
NULL NULL NULL NULL NULL NULL NULL
> 1967 FULL The Inn San Francisco USA San Francisco CA CA
http://www.thebandb.com FULL 80.00 420.00 1 One of San Francisco's best kept
secrets, we offer a variety of charming accommodations on a secluded
courtyard, just a corner's turn from the excitement of Union Street. 1967
NULL NULL NULL NULL NULL NULL NULL NULL
> 1934 FULL The Inn at Union Square San Francisco USA San Francisco CA CA
http://www.unionsquare.com FULL 195.00 375.00 1 An elegant, cozy
European-style hotel in the heart of San Francisco. A half-block from Union
Square, & close to the finest restaurants, best shopping & wonderful
theater. Our location in unbeatable! ltd no yes 1934 NULL NULL NULL NULL
NULL NULL NULL NULL
> 1942 FULL The Inn San Francisco San Francisco USA San Francisco CA CA
http://www.innsf.com FULL 95.00 265.00 1 Authentic 1872 Italianate Victorian
Mansion. Rich Victorian decor. English Garden with Redwood Hot Tub. Sundeck
with spectacular SF vistas. Excellent restaurants nearby. Be welcomed as a
friend. yes no no 1942 NULL NULL NULL NULL NULL NULL NULL NULL
> 1949 FULL The Monte Cristo San Francisco USA San Francisco CA CA
http://www.virtualcities.com/ons/ca/b/cab3504.htm FULL 83.00 118.00 1 1875
hotel-saloon-bordello furnished with antiques. Each room uniquely decorated:
Georgian four-poster, Chinese wedding bed, spindle bed. Personal service.
yes no yes 1949 NULL NULL NULL NULL NULL NULL NULL NULL
> 1960 FULL Victorian Inn on the Park San Francisco USA San Francisco CA CA
http://www.victorianinnonthepark.com FULL 159.00 199.00 1 1897 Queen Anne
Victorian near Golden Gate Park, downtown. Each room has antiques, flowers,
beautiful comforters, down pillows, phones. yes no no 1960 NULL NULL NULL
NULL NULL NULL NULL NULL
>
>
> ------------------------------------------------------------ --------------
------------
>
>
> As you can see each of the above 22 rows is distinct.
>
> To start with, every row has it's own allusa.id, which does not repeat
> in any other row.
>
> This fact alone makes all 22 rows distinct from each other.
>
>
> --
> Regards,
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12351@lists.mysql.com
> To unsubscribe, e-mail
>
>
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12352@lists.mysql.com
To unsubscribe, e-mail