You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Jean T. Anderson (JIRA)" <de...@db.apache.org> on 2006/04/26 01:06:05 UTC
[jira] Reopened: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Jean T. Anderson reopened DERBY-994:
------------------------------------
Reopen to merge fix to 10.1.3.
> SQL examples for LEFT OUTER JOIN and RIGHT OUTER JOIN in the Derby Reference manual are incorrect
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-994
> URL: http://issues.apache.org/jira/browse/DERBY-994
> Project: Derby
> Type: Bug
> Components: Documentation
> Versions: 10.0.2.0
> Reporter: Rajesh Kartha
> Assignee: Jeff Levitt
> Fix For: 10.2.0.0
> Attachments: derby994.diff, rrefsqlj18922.html, rrefsqlj57522.html
>
> The SQL examples for the following in the reference manual are wrong:
> - LEFT OUTER JOIN
> - RIGHT OUTER JOIN
> The incorrectness are pointed out below. Also the fix (correct sql and descriptions) that should replace these are provided. Can someone please review this and commit into the codeline.
> LEFT OUTER JOIN :
> ================
> (v10.1) http://db.apache.org/derby/docs/10.1/ref/rrefsqlj18922.html
> (trunk) http://db.apache.org/derby/docs/dev/ref/rrefsqlj18922.html
> The manual shows:
> <quote>
> --match cities to countries
> [wrong description: should mention cities to countries in Asia] <==
> SELECT CITIES.COUNTRY, REGION
> FROM Countries
> LEFT OUTER JOIN Cities
> ON CITY_ID=CITY_ID
> WHERE REGION = 'Asia';
> [wrong sql: This will return 1305 rows meaningless rows] <==
> -- use the synonymous syntax, RIGHT JOIN, to achieve exactly
> -- the same results as in the example above
> [wrong description: The synonymous syntax is LEFT JOIN] <==
> SELECT COUNTRIES.COUNTRY, REGION
> FROM Countries
> LEFT JOIN Cities
> ON CITY_ID=CITY_ID;
> [wrong sql: Returns a Cartesian product of the two tables: 9918 rows selected] <==
> </quote>
> The correct description and sql for LEFT OUTER JOIN should be:
> ---------------------------------------------------------------------------------------
> --match cities to countries in Asia
> SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION
> FROM COUNTRIES
> LEFT OUTER JOIN CITIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
> WHERE REGION='Asia';
> -- use the synonymous syntax, LEFT JOIN, to achieve exactly
> -- the same results as in the example above
> SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION
> FROM COUNTRIES
> LEFT JOIN CITIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
> WHERE REGION='Asia';
> [Both the above queries will return
> COUNTRY |CITY_NAME |REGION
> ------------------------------------------------------------------------------
> Afghanistan |Kabul |Asia
> Bangladesh |NULL |Asia
> Cambodia |NULL |Asia
> China |Hong Kong |Asia
> China |Shanghai |Asia
> India |Bombay |Asia
> India |Calcutta |Asia
> Indonesia |Jakarta |Asia
> Japan |Osaka |Asia
> Japan |Tokyo |Asia
> Korea, Republic of |Seoul |Asia
> Malaysia |NULL |Asia
> Nepal |NULL |Asia
> Philippines |Manila |Asia
> Singapore |Singapore |Asia
> Sri Lanka |NULL |Asia
> Thailand |NULL |Asia
> Viet Nam |NULL |Asia
> 18 rows selected] <==
> RIGHT OUTER JOIN:
> =================
> (v10.1) http://db.apache.org/derby/docs/10.1/ref/rrefsqlj57522.html
> (trunk) http://db.apache.org/derby/docs/dev/ref/rrefsqlj57522.html
> The manual shows:
> <quote>
> -- get all countries and corresponding cities, including
> -- countries without any cities
> SELECT CITY_NAME, CITIES.COUNTRY
> FROM CITIES RIGHT OUTER JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;
> [wrong sql: Return meaningless 156 rows ] <==
> -- get all countries in Africa and corresponding cities, including
> -- countries without any cities
> SELECT CITY_NAME, CITIES.COUNTRY
> FROM CITIES RIGHT OUTER JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;
> WHERE Countries.region = 'frica';
> [wrong sql:
> 1) 'frica' is incorrect in the WHERE clause
> 2) incorrect results with NULL country values
> 3) incorrect ';' before WHERE clause] <==
> -- use the synonymous syntax, RIGHT JOIN, to achieve exactly
> -- the same results as in the example above
> SELECT CITY_NAME, CITIES.COUNTRY
> FROM CITIES RIGHT JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
> WHERE Countries.region = 'Africa';
> [wrong sql: Incorrect results with NULL country values] <==
> </quote>
> The correct description and sql for RIGHT OUTER JOIN should be:
> ------------------------------------------------------------------------------------------
> -- get all countries and corresponding cities, including
> -- countries without any cities
> SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
> FROM CITIES
> RIGHT OUTER JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;
> -- get all countries in Africa and corresponding cities, including
> -- countries without any cities
> SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
> FROM CITIES
> RIGHT OUTER JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
> WHERE Countries.region = 'Africa';
> -- use the synonymous syntax, RIGHT JOIN, to achieve exactly
> -- the same results as in the example above
> SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
> FROM CITIES
> RIGHT JOIN COUNTRIES
> ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
> WHERE Countries.region = 'Africa';
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira