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 "Rajesh Kartha (JIRA)" <de...@db.apache.org> on 2006/02/16 07:24:01 UTC
[jira] Created: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
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
Fix For: 10.2.0.0
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
[jira] Updated: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Jeff Levitt (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Jeff Levitt updated DERBY-994:
------------------------------
Attachment: derby994.diff
rrefsqlj18922.html
rrefsqlj57522.html
Attached patch fixes the examples as requested. HTML output included for review.
> 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
> 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
[jira] Closed: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Jean T. Anderson closed DERBY-994:
----------------------------------
Fix Version: 10.1.3.0
(was: 10.2.0.0)
Resolution: Fixed
Fix merged from 10.2, committed revision 397013.
> 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.1.3.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
[jira] Assigned: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Andrew McIntyre reassigned DERBY-994:
-------------------------------------
Assign To: Jeff Levitt
> 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
[jira] Reopened: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=comments#action_12367408 ]
Rajesh Kartha commented on DERBY-994:
-------------------------------------
The patch looks good. If there is no further feedback, can one of the committers please commit it.
> 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
> 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
[jira] Resolved: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Andrew McIntyre resolved DERBY-994:
-----------------------------------
Resolution: Fixed
Committed revision 379913.
> 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
> 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
[jira] Updated: (DERBY-994) SQL examples for LEFT OUTER JOIN and
RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Posted by "Jeff Levitt (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-994?page=all ]
Jeff Levitt updated DERBY-994:
------------------------------
Other Info: [Patch available]
> 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
> 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