problem using union and order by with several columns from different tables

the following statement gives an error:

SELECT a.id, a.name, b.description
from Apple a, Fruit b
where a.id < 10
UNION
SELECT a.id, a.name,b.description
from Appl a, Fruit b
where a.id >1000
ORDER by a.id, a.name,b.description

the error says "unknown table b"

Is this a known limitation?

Stefan


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: problem using union and order by with several columns from different tables

Dr. Stefan Richter writes:
> the following statement gives an error:
>
> SELECT a.id, a.name, b.description
> from Apple a, Fruit b
> where a.id < 10
> UNION
> SELECT a.id, a.name,b.description
> from Appl a, Fruit b
> where a.id >1000
> ORDER by a.id, a.name,b.description
>
> the error says "unknown table b"
>
> Is this a known limitation?
>
> Stefan

Hi!

I just tried this with 4.0.12 with tables I had at hand:

mysql telcent -e "select a.broj, a.naziv, b.auto, b.type from nazivi a, accounting b where a.broj < 3 union select a.broj, a.naziv, b.auto, b.type from nazivi a, accounting b where b.auto < 200 order by a.broj, a.naziv, b.type"
+------+------------+------+--------------------+
| broj | naziv | auto | type |
+------+------------+------+--------------------+
| 1 | jedan | 199 | alv |
| 1 | jedan | 204 | alv |
| 1 | jedan | 201 | alv |
| 1 | jedan | 200 | incomegetexpenses |
| 1 | jedan | 198 | incomegetexpenses |
| 1 | jedan | 203 | rent |
| 1 | jedan | 202 | personalwithdrawal |
| 2 | dva | 199 | alv |
| 2 | dva | 204 | alv |
| 2 | dva | 201 | alv |
| 2 | dva | 200 | incomegetexpenses |
| 2 | dva | 198 | incomegetexpenses |
| 2 | dva | 203 | rent |
| 2 | dva | 202 | personalwithdrawal |
| 3 | tri | 199 | alv |
| 3 | tri | 198 | incomegetexpenses |
| 4 | xxxxxxxxxx | 199 | alv |
| 4 | xxxxxxxxxx | 198 | incomegetexpenses |
| 5 | a | 199 | alv |
| 5 | a | 198 | incomegetexpenses |
| 8 | yyyyy | 199 | alv |
| 8 | yyyyy | 198 | incomegetexpenses |
| 9 | yyyyy | 199 | alv |
| 9 | yyyyy | 198 | incomegetexpenses |
+------+------------+------+--------------------+

I guess this matches yours query, or am I missing something ???

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: problem using union and order by with several columns from different tables

Hi,

ok may be i've oversimplified the problem. We use a publicly available
database (ftp://ftp.ensembl.org/pub/human-11.31/data/mysql/) and execute
the following query:

(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
AND f.analysis_id = 1
AND (a.chr_start BETWEEN 7800000 AND 7810000))
UNION ALL
(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
AND f.analysis_id = 1
AND (a.chr_end BETWEEN 7800000 AND 7810000))
UNION ALL
(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
AND f.analysis_id = 1
AND (a.chr_start <7800000 AND a.chr_end>7810000))
ORDER BY f.hit_name, f.contig_start
;

this gives the error:
General error unknown table f in order clause.

if we remove the f.contig_start the statement works.

Stefan



Sinisa Milivojevic wrote:

>Dr. Stefan Richter writes:
>
>
>>the following statement gives an error:
>>
>>SELECT a.id, a.name, b.description
>> from Apple a, Fruit b
>> where a.id < 10
>>UNION
>>SELECT a.id, a.name,b.description
>> from Appl a, Fruit b
>> where a.id >1000
>>ORDER by a.id, a.name,b.description
>>
>>the error says "unknown table b"
>>
>>Is this a known limitation?
>>
>>Stefan
>>
>>
>
>Hi!
>
>I just tried this with 4.0.12 with tables I had at hand:
>
> mysql telcent -e "select a.broj, a.naziv, b.auto, b.type from nazivi a, accounting b where a.broj < 3 union select a.broj, a.naziv, b.auto, b.type from nazivi a, accounting b where b.auto < 200 order by a.broj, a.naziv, b.type"
>+------+------------+------+--------------------+
>| broj | naziv | auto | type |
>+------+------------+------+--------------------+
>| 1 | jedan | 199 | alv |
>| 1 | jedan | 204 | alv |
>| 1 | jedan | 201 | alv |
>| 1 | jedan | 200 | incomegetexpenses |
>| 1 | jedan | 198 | incomegetexpenses |
>| 1 | jedan | 203 | rent |
>| 1 | jedan | 202 | personalwithdrawal |
>| 2 | dva | 199 | alv |
>| 2 | dva | 204 | alv |
>| 2 | dva | 201 | alv |
>| 2 | dva | 200 | incomegetexpenses |
>| 2 | dva | 198 | incomegetexpenses |
>| 2 | dva | 203 | rent |
>| 2 | dva | 202 | personalwithdrawal |
>| 3 | tri | 199 | alv |
>| 3 | tri | 198 | incomegetexpenses |
>| 4 | xxxxxxxxxx | 199 | alv |
>| 4 | xxxxxxxxxx | 198 | incomegetexpenses |
>| 5 | a | 199 | alv |
>| 5 | a | 198 | incomegetexpenses |
>| 8 | yyyyy | 199 | alv |
>| 8 | yyyyy | 198 | incomegetexpenses |
>| 9 | yyyyy | 199 | alv |
>| 9 | yyyyy | 198 | incomegetexpenses |
>+------+------------+------+--------------------+
>
>I guess this matches yours query, or am I missing something ???
>
>
>




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: problem using union and order by with several columns from different tables

Dr. Stefan Richter writes:
> Hi,
>
> ok may be i've oversimplified the problem. We use a publicly available
> database (ftp://ftp.ensembl.org/pub/human-11.31/data/mysql/) and execute
> the following query:
>
> (SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
> FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
> WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
> AND f.analysis_id = 1
> AND (a.chr_start BETWEEN 7800000 AND 7810000))
> UNION ALL
> (SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
> FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
> WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
> AND f.analysis_id = 1
> AND (a.chr_end BETWEEN 7800000 AND 7810000))
> UNION ALL
> (SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
> FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
> WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
> AND f.analysis_id = 1
> AND (a.chr_start <7800000 AND a.chr_end>7810000))
> ORDER BY f.hit_name, f.contig_start
> ;
>
> this gives the error:
> General error unknown table f in order clause.
>
> if we remove the f.contig_start the statement works.
>
> Stefan

well, this turns out to be completely different issue.

You are using parenthesis. Without parenthesis, ORDER BY and LIMIT are
pertaining to the query.

With parentheses ORDER BY and LIMIT ... WITHIN parenthesiss are still
pertaining to the query itself, but the last ones are pertaining to
the UNION as a whole.

And UNION as a whole does not know about individual tables. So, you
have to use column aliases to make order as you desire.

This is already documented, but our documentation can still be
improved on this matter, which we shall do in the near future.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: problem using union and order by with several columns from different tables

Thanks a lot now it works.

Stefan

Sinisa Milivojevic wrote:

>Dr. Stefan Richter writes:
>
>
>>Hi,
>>
>>ok may be i've oversimplified the problem. We use a publicly available
>>database (ftp://ftp.ensembl.org/pub/human-11.31/data/mysql/) and execute
>>the following query:
>>
>>(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
>>FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
>>WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
>>AND f.analysis_id = 1
>>AND (a.chr_start BETWEEN 7800000 AND 7810000))
>>UNION ALL
>>(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
>>FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
>>WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
>>AND f.analysis_id = 1
>>AND (a.chr_end BETWEEN 7800000 AND 7810000))
>>UNION ALL
>>(SELECT a.superctg_name, f.dna_align_feature_id, f.hit_name, f.score, a.contig_ori * f.contig_strand,
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_start, a.chr_start + a.contig_end - f.contig_end),
>> IF (a.contig_ori > 0, a.chr_start - a.contig_start + f.contig_end, a.chr_start + a.contig_end - f.contig_start)
>>FROM homo_sapiens_core_11_31.assembly a, homo_sapiens_core_11_31.dna_align_feature f
>>WHERE a.chromosome_id = 1 AND a.contig_id = f.contig_id
>>AND f.analysis_id = 1
>>AND (a.chr_start <7800000 AND a.chr_end>7810000))
>>ORDER BY f.hit_name, f.contig_start
>>;
>>
>>this gives the error:
>>General error unknown table f in order clause.
>>
>>if we remove the f.contig_start the statement works.
>>
>>Stefan
>>
>>
>
>well, this turns out to be completely different issue.
>
>You are using parenthesis. Without parenthesis, ORDER BY and LIMIT are
>pertaining to the query.
>
>With parentheses ORDER BY and LIMIT ... WITHIN parenthesiss are still
>pertaining to the query itself, but the last ones are pertaining to
>the UNION as a whole.
>
>And UNION as a whole does not know about individual tables. So, you
>have to use column aliases to make order as you desire.
>
>This is already documented, but our documentation can still be
>improved on this matter, which we shall do in the near future.
>
>
>




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org