Re: Mysql 5.1 union with group by for results

[Machiel Richards]

Hi Jesper

I was just discussing this with the development manager now and the 
following was noted.


- The query was written for mysql 4.0 originally and it seems 
that in version 5.0 they had enabled some legacy support stuff ( I am 
not too familiar with this as it is before my mysql time ;-) ).


- I have now explained to them what the problem is and they 
will be working with the developers to rewrite all these queries.


Regards
Machiel.





On 08/11/2013 13:27, Jesper Wisborg Krogh wrote:

Hi Machiel,

On 8/11/2013 20:04, Machiel Richards wrote:

Good day all

   I am hoping someone can assist me in the following.

One of our servers were running mysql 5.0 still and as part 
of a phased upgrade route we have upgraded to version 5.1.


However since the upgrade, the query below gives us an error 
stating that the syntax is incorrect and I simply cant seem to find 
out what is actually wrong as all tests and changes have been giving 
us the same.


I have tried many suggestions from the net but to no avail.

The query is as follows:


Using a shorter but equivalent query, you have:

   (SELECT t.id, t.name, SUM(val) FROM t1 t)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t)
   GROUP BY t.id, t.name;

That does not work in 5.0 either (at least in 5.0.96):

   ERROR 1064 (42000): You have an error in your SQL syntax; check the
   manual that corresponds to your MySQL server version for the right
   syntax to use near 'GROUP BY t.id, t.name' at line 4


The issue is that you are trying grouping the entire UNION result, but 
at that point there is no SELECT any longer - there is just the result 
set. You are also referencing tables that exists inside each of the 
SELECT statements, but at the time the GROUP BY is reached, there are 
no tables. Note that as written the two SELECT parts will also give 
non-deterministic results as you have an aggregate function but no 
GROUP BY, so the values of id and val1 will be "random".


What you probably want instead is either:

   (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);

or

   SELECT a.id, a.name, SUM(val)
  FROM (
(SELECT t.id, t.name, t.val FROM t1 t)
   UNION
(SELECT t.id, t.name, t.val FROM t2 t)
   ) a
 GROUP BY a.id, a.name;


On a side note:



 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')


Assuming t.Day is a date, datetime, or timestamp column, you can 
rewrite that WHERE clause to something like (depending on the exact 
data type):


   t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'

or

   t.Day >= '2013-08-01 00:00:00' AND t.Day < '2013-12-01 00:00:00'


That way you will be able to use an index for that condition.

Best regards,
Jesper Krogh
MySQL Support




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

[Machiel Richards]

Happiness, that gave me what I was looking for. Thank you Johan.

I have tested the option you gave me but my brackets was in the wrong place.




On 08/11/2013 13:23, Johan De Meersman wrote:

- Original Message -

From: "Machiel Richards" 

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'GROUP BY t.AccountID,


I suspect your query has never been doing what you think at all, and you need to


   select [fields] from (
 select fields1 blahblah
 UNION
 select fields2 blahblah) unionized
   group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

[Johan De Meersman]

- Original Message -
> From: "Machiel Richards" 
> 
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'GROUP BY t.AccountID,


I suspect your query has never been doing what you think at all, and you need 
to 


  select [fields] from (
    select fields1 blahblah
UNION
select fields2 blahblah) unionized
  group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

[Jesper Wisborg Krogh]

Hi Machiel,

On 8/11/2013 20:04, Machiel Richards wrote:

Good day all

   I am hoping someone can assist me in the following.

One of our servers were running mysql 5.0 still and as part of 
a phased upgrade route we have upgraded to version 5.1.


However since the upgrade, the query below gives us an error 
stating that the syntax is incorrect and I simply cant seem to find 
out what is actually wrong as all tests and changes have been giving 
us the same.


I have tried many suggestions from the net but to no avail.

The query is as follows:


Using a shorter but equivalent query, you have:

   (SELECT t.id, t.name, SUM(val) FROM t1 t)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t)
   GROUP BY t.id, t.name;

That does not work in 5.0 either (at least in 5.0.96):

   ERROR 1064 (42000): You have an error in your SQL syntax; check the
   manual that corresponds to your MySQL server version for the right
   syntax to use near 'GROUP BY t.id, t.name' at line 4


The issue is that you are trying grouping the entire UNION result, but 
at that point there is no SELECT any longer - there is just the result 
set. You are also referencing tables that exists inside each of the 
SELECT statements, but at the time the GROUP BY is reached, there are no 
tables. Note that as written the two SELECT parts will also give 
non-deterministic results as you have an aggregate function but no GROUP 
BY, so the values of id and val1 will be "random".


What you probably want instead is either:

   (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
   UNION
   (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);

or

   SELECT a.id, a.name, SUM(val)
  FROM (
(SELECT t.id, t.name, t.val FROM t1 t)
   UNION
(SELECT t.id, t.name, t.val FROM t2 t)
   ) a
 GROUP BY a.id, a.name;


On a side note:



 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')


Assuming t.Day is a date, datetime, or timestamp column, you can rewrite 
that WHERE clause to something like (depending on the exact data type):


   t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'

or

   t.Day >= '2013-08-01 00:00:00' AND t.Day < '2013-12-01 00:00:00'


That way you will be able to use an index for that condition.

Best regards,
Jesper Krogh
MySQL Support

Mysql 5.1 union with group by for results

[Machiel Richards]

Good day all

   I am hoping someone can assist me in the following.

One of our servers were running mysql 5.0 still and as part of 
a phased upgrade route we have upgraded to version 5.1.


However since the upgrade, the query below gives us an error 
stating that the syntax is incorrect and I simply cant seem to find out 
what is actually wrong as all tests and changes have been giving us the 
same.


I have tried many suggestions from the net but to no avail.

The query is as follows:

(SELECT SUBSTRING(t.Day,1,7) AS Date,
  a.Name AS Account,
  a.Status AS AccountStatus,
  c.Name AS Login,
  t.Service AS Service,
  n.name AS Network,
  tc.Name AS Toc,
  t.SrcAddress AS FromAddress,
  SUM(t.Count) AS COUNT
   FROM statstx_archive t
   LEFT JOIN account a ON t.AccountID=a.ID
   LEFT JOIN client2 c ON t.ClientID=c.ID
   LEFT JOIN tocname tc ON t.TOC=tc.toc
   LEFT JOIN network n ON t.NetworkID=n.ID
   WHERE t.toc=1
 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')
UNION
  (SELECT SUBSTRING(t.Day,1,7) AS Date,
  a.Name AS Account,
  a.Status AS AccountStatus,
  c.Name AS Login,
  t.Service AS Service,
  n.name AS Network,
  tc.Name AS Toc,
  t.SrcAddress AS FromAddress,
  SUM(t.COUNT) AS COUNT
   FROM statstx t
   LEFT JOIN account a ON t.AccountID=a.ID
   LEFT JOIN client2 c ON t.ClientID=c.ID
   LEFT JOIN tocname tc ON t.TOC=tc.toc
   LEFT JOIN network n ON t.NetworkID=n.ID
   WHERE t.toc=1
 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')
GROUP BY t.AccountID,
 t.ClientID,
 t.Service,
 t.NetworkID,
 t.Toc,
 t.SrcAddress,
 SUBSTRING(t.Day,1,7)



When running the queries individually it works fine, and 
when removing the group by it also runs, however the moment we put the 
group by section back then it falls over.


Any help would by appreciated.

Regards
Machiel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: UNION and ORDER BY

[Rick James]

The parens are for making sure the parsing works correctly.  Probably either 
one works fine.  Suggest you do
  EXPLAIN EXTENDED ...;
  SHOW WARNINGS;
I suspect that the output from each will be identical, and have more parens.

The main need for parens is to avoid associating the ORDER BY with just the 
second SELECT.

> -Original Message-
> From: Hal?sz S?ndor [mailto:h...@tbbs.net]
> Sent: Thursday, June 21, 2012 4:07 PM
> To: mysql@lists.mysql.com
> Subject: UNION and ORDER BY
> 
> >>>> 2012/06/20 14:32 -0700, Rick James >>>>
> (
>SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx
>UNION
>SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx
> ) ORDER BY overlap DESC;
> 
> Make it UNION ALL or UNION DISTINCT depending on whether xxx can be in
> both fields of one row.  UNION DISTINCT makes a pass over the temp
> table to dedup.
> <<<<<<<<
> The help example shows a UNION s each SELECT separatly round-bracketed,
> (SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx)
> UNION
> (SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx)
>   ORDER BY overlap DESC
> but bracketing both is not mentioned. What is the difference?
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

UNION and ORDER BY

[Hal�sz S�ndor]

>>>> 2012/06/20 14:32 -0700, Rick James >>>>
(
   SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx
   UNION
   SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx
) ORDER BY overlap DESC;

Make it UNION ALL or UNION DISTINCT depending on whether xxx can be in both 
fields of one row.  UNION DISTINCT makes a pass over the temp table to dedup.
<<<<<<<<
The help example shows a UNION s each SELECT separatly round-bracketed,
(SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid1=xxx)
UNION
(SELECT ruid1, ruid2, overlap FROM l4_link WHERE ruid2=xxx)
  ORDER BY overlap DESC
but bracketing both is not mentioned. What is the difference?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

query mystery: union de-optimizes component queries

[Dave Dyer]

Why would using UNION cause the subqueries to be de-optimized?

explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord 
where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) and 
(player1='13213' or player2='13213' ) group by variation limit 3)  

shows using index on gmtdate

explain
(SELECT count(gamename) as gname ,variation from mp_gamerecord 
  where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) 
  and (player1='13213' or player2='13213' or player3='13213' or player4='13213' 
or player5='13213' or player6='13213') 
  group by variation limit 3)

shows using index gmtdate

explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord 
where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) and 
(player1='13213' or player2='13213' ) group by variation limit 3) 
UNION 
(SELECT count(gamename) as gname ,variation from mp_gamerecord 
  where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) 
  and (player1='13213' or player2='13213' or player3='13213' or player4='13213' 
or player5='13213' or player6='13213') 
  group by variation limit 3) 

ie: the same two queries shows using no indexes on the first half
of the query.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MERGE Engine vs. UNION ALL

[James W. McKelvey]

We've been experimenting with the merge engine.

But suppose that instead of using the MERGE engine I instead modified my 
code to UNION ALL the shards.


Would I get worse performance? In other words, besides the convenience, 
does the MERGE engine have specific performance optimizations that make 
it perform better?


This is of interest for several reasons. First, my code could determine 
in some cases that only a few (or a single) shards were needed. Second, 
I could apply the shard technique to InnoDB tables. Third, I could apply 
the shard technique to databases that were not identical.


I didn't get any response in the MERGE forum.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Slow Union Statement

[chen jia]

Yes, that's the trick. Thank Rudy and Gavin.

Best,
Jia

On Mon, Apr 5, 2010 at 2:13 PM, Gavin Towey  wrote:
> Union does a distinct on all results.  UNION ALL will avoid that.
>
> Regards,
> Gavin Towey
>
> -Original Message-
> From: chen.1...@gmail.com [mailto:chen.1...@gmail.com] On Behalf Of chen jia
> Sent: Monday, April 05, 2010 11:07 AM
> To: mysql@lists.mysql.com
> Subject: Slow Union Statement
>
> Hi there,
>
> I run simple statement like this:
>
> create table c
> select * from a
> union
> select * from b;
>
> where table a has 90,402,534 rows, and table b has 33,358,725 rows.
> Both tables have the same three variables.
>
> It's taken a long time, more than half an hour now. How do I make it faster?
>
> Best,
> Jia
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=gto...@ffn.com
>
>
> This message contains confidential information and is intended only for the 
> individual named.  If you are not the named addressee, you are notified that 
> reviewing, disseminating, disclosing, copying or distributing this e-mail is 
> strictly prohibited.  Please notify the sender immediately by e-mail if you 
> have received this e-mail by mistake and delete this e-mail from your system. 
> E-mail transmission cannot be guaranteed to be secure or error-free as 
> information could be intercepted, corrupted, lost, destroyed, arrive late or 
> incomplete, or contain viruses. The sender therefore does not accept 
> liability for any loss or damage caused by viruses or errors or omissions in 
> the contents of this message, which arise as a result of e-mail transmission. 
> [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, 
> FriendFinder.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Slow Union Statement

[Gavin Towey]

Union does a distinct on all results.  UNION ALL will avoid that.

Regards,
Gavin Towey

-Original Message-
From: chen.1...@gmail.com [mailto:chen.1...@gmail.com] On Behalf Of chen jia
Sent: Monday, April 05, 2010 11:07 AM
To: mysql@lists.mysql.com
Subject: Slow Union Statement

Hi there,

I run simple statement like this:

create table c
select * from a
union
select * from b;

where table a has 90,402,534 rows, and table b has 33,358,725 rows.
Both tables have the same three variables.

It's taken a long time, more than half an hour now. How do I make it faster?

Best,
Jia

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Slow Union Statement

[Rudy Lippan]

On 04/05/2010 02:06 PM, chen jia wrote:
> Hi there,
> 
> I run simple statement like this:
> 
> create table c
> select * from a
> union
> select * from b;
> 
> where table a has 90,402,534 rows, and table b has 33,358,725 rows.
> Both tables have the same three variables.
> 
> It's taken a long time, more than half an hour now. How do I make it faster?


UNION ALL, perhaps?

-r

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Slow Union Statement

[chen jia]

Hi there,

I run simple statement like this:

create table c
select * from a
union
select * from b;

where table a has 90,402,534 rows, and table b has 33,358,725 rows.
Both tables have the same three variables.

It's taken a long time, more than half an hour now. How do I make it faster?

Best,
Jia

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Creating Table Through Union

[Victor Subervi]

On Sun, Nov 22, 2009 at 9:29 PM, Gavin Towey  wrote:

> The form would be like:
>
> CREATE TABLE products
> SELECT b0basics, b0fieldValues, s0prescriptions,
> s0prescriptions0doctors, s0prescriptions0patient, pics FROM table1
> UNION
> SELECT b0basics, b0fieldValues, s0prescriptions,
> s0prescriptions0doctors, s0prescriptions0patient, pics FROM table2
>

Thank you.
V

RE: Creating Table Through Union

[Gavin Towey]

The form would be like:

CREATE TABLE products
SELECT b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics FROM table1
UNION
SELECT b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics FROM table2

Regards,
Gavin Towey

-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Sunday, November 22, 2009 10:56 AM
To: mysql@lists.mysql.com
Subject: Creating Table Through Union

Hi;
I would like to create a table out of merging the fields in other,
previously created tables. I have the following syntax which doesn't work:

create table products union (b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics);

Please advise.
TIA,
Victor

The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Creating Table Through Union

[Victor Subervi]

Hi;
I would like to create a table out of merging the fields in other,
previously created tables. I have the following syntax which doesn't work:

create table products union (b0basics, b0fieldValues, s0prescriptions,
s0prescriptions0doctors, s0prescriptions0patient, pics);

Please advise.
TIA,
Victor

AW: OR vs UNION

[Majk.Skoric]

> -Ursprüngliche Nachricht-
> Von: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com]
> Gesendet: Dienstag, 13. Oktober 2009 15:26
> An: Skoric, Majk
> Cc: mysql@lists.mysql.com
> Betreff: Re: OR vs UNION
> 
> Majk, all,
> 
> 
> I'm no optimizer expert, but your result doesn't really surprise me.
> 
> I'll reorder your post because that makes reasoning simpler:
> 
> majk.sko...@eventim.de wrote:
> > Hi List,
> 
> First, your table:
> 
> > TABLEDEF.
> > | KTEMP | CREATE TABLE `KTEMP` (
> >   `tid` bigint(20) NOT NULL auto_increment,
> >   `kunde_id` varchar(20) collate utf8_bin NOT NULL,
> >   `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0',
> >   `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL,
> >   `veranst_id` bigint(20) NOT NULL,
> >   `rolle_nummer` int(11) default '0',
> >   `status` tinyint(1) unsigned NOT NULL,
> >   `tstamp_insert` bigint(20) NOT NULL,
> >   `tstamp_update` bigint(20) NOT NULL,
> >   `KategorienWechsel` tinyint(4) NOT NULL default '0',
> >   PRIMARY KEY  (`tid`),
> >   KEY `buchungs_kunde_id`
> (`buchungs_kunde_id`,`veranst_id`,`status`),
> >   KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
> 
> So you have two indexes which consist of three fields each, and the
> least significant two fields are the same for both indexes.
> 
> 
> You do a SELECT that fully specifies values for these two indexes,
> combining them with "OR":
> 
> >
> > mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
> > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or
> (kunde_id=
> > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1);
> > ++-+---+-+---
> -+-
> > ---+-+--+--+-
> ---
> > --+
> > | id | select_type | table | type| possible_keys
> |
> > key| key_len | ref  | rows | Extra
> > |
> > +----+-+---+-+---
> -+-
> > ---+-+--+--+-
> ---
> > --+
> > |  1 | SIMPLE  | KTEMP | index_merge | buchungs_kunde_id,kunde_id
> |
> > buchungs_kunde_id,kunde_id | 71,71   | NULL |2 | Using
> > union(buchungs_kunde_id,kunde_id); Using where |
> > +----+-----+---+-+---
> -+-
> > ---+-+--+--+-
> ---
> > --+
> > 1 row in set (0.00 sec)
> >
> > All seems fine here . Optimizer choose to use an union! This is the
> same
> > as following union query.
> 
> As an alternative, you replace the "OR" by a UNION. No real change:
> 
> >
> > mysql> explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id
> =
> > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION
> (SELECT
> > * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id =
> 16058
> > and status = 1);
> > ++--++--+---+
> ---
> > +-+---+--+-+
> > | id | select_type  | table  | type | possible_keys | key
> > | key_len | ref   | rows | Extra   |
> > ++--++--+---+
> ---
> > +-+---+--+-+
> > |  1 | PRIMARY  | KTEMP  | ref  | buchungs_kunde_id |
> > buchungs_kunde_id | 71  | const,const,const |1 | Using where
> |
> > |  2 | UNION| KTEMP  | ref  | kunde_id  |
> kunde_id
> > | 71  | const,const,const |1 | Using where |
> > |NULL | UNION RESULT |  | ALL  | NULL  | NULL
> > | NULL| NULL  | NULL | |
> >
> 
> Note that both queries fully specify the index values.
> 
> 
> Then, you apply Boolean logic to factor out the two identical
> predicates
> from both the "OR" alternatives:
> 
> >
> > But the following query is handled in a strange way
> >
> > mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
> > 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id =
> > 16058 and stat

Re: OR vs UNION

[Joerg Bruehe]

Majk, all,


I'm no optimizer expert, but your result doesn't really surprise me.

I'll reorder your post because that makes reasoning simpler:

majk.sko...@eventim.de wrote:
> Hi List,

First, your table:

> TABLEDEF.
> | KTEMP | CREATE TABLE `KTEMP` (
>   `tid` bigint(20) NOT NULL auto_increment,
>   `kunde_id` varchar(20) collate utf8_bin NOT NULL,
>   `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0',
>   `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL,
>   `veranst_id` bigint(20) NOT NULL,
>   `rolle_nummer` int(11) default '0',
>   `status` tinyint(1) unsigned NOT NULL,
>   `tstamp_insert` bigint(20) NOT NULL,
>   `tstamp_update` bigint(20) NOT NULL,
>   `KategorienWechsel` tinyint(4) NOT NULL default '0',
>   PRIMARY KEY  (`tid`),
>   KEY `buchungs_kunde_id` (`buchungs_kunde_id`,`veranst_id`,`status`),
>   KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

So you have two indexes which consist of three fields each, and the
least significant two fields are the same for both indexes.


You do a SELECT that fully specifies values for these two indexes,
combining them with "OR":

> 
> mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
> 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or (kunde_id=
> 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1);
> ++-+---+-++-
> ---+-+--+--+
> --+
> | id | select_type | table | type| possible_keys  |
> key| key_len | ref  | rows | Extra
> |
> ++-+---+-++-
> ---+-+--+--+
> --+
> |  1 | SIMPLE  | KTEMP | index_merge | buchungs_kunde_id,kunde_id |
> buchungs_kunde_id,kunde_id | 71,71   | NULL |2 | Using
> union(buchungs_kunde_id,kunde_id); Using where |
> ++-+---+-++-
> ---+-+--+--+
> --+
> 1 row in set (0.00 sec)
> 
> All seems fine here . Optimizer choose to use an union! This is the same
> as following union query.

As an alternative, you replace the "OR" by a UNION. No real change:

> 
> mysql> explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id =
> 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION (SELECT
> * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058
> and status = 1);
> ++--++--+---+---
> +-+---+--+-+
> | id | select_type  | table  | type | possible_keys | key
> | key_len | ref   | rows | Extra   |
> ++--+----+--+---+---
> +-+---+--+-+
> |  1 | PRIMARY  | KTEMP  | ref  | buchungs_kunde_id |
> buchungs_kunde_id | 71  | const,const,const |1 | Using where |
> |  2 | UNION| KTEMP  | ref  | kunde_id  | kunde_id
> | 71  | const,const,const |1 | Using where |
> |NULL | UNION RESULT |  | ALL  | NULL  | NULL
> | NULL| NULL  | NULL | |
> 

Note that both queries fully specify the index values.


Then, you apply Boolean logic to factor out the two identical predicates
from both the "OR" alternatives:

> 
> But the following query is handled in a strange way
> 
> mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
> 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id =
> 16058 and status = 1;
> ++-+---+--++--+-
> +--+-+-+
> | id | select_type | table | type | possible_keys  | key  |
> key_len | ref  | rows| Extra   |
> ++-+---+--++--+-
> +--+-+-+
> |  1 | SIMPLE  | KTEMP | ALL  | buchungs_kunde_id,kunde_id | NULL |
> NULL| NULL | 1040700 | Using where |
> ++-+---+--++--+-
> +--+-+-+
> 
> I don't get it! Maybe someone has a clue or a hint for me.

While that is equivalent from a logic point of view, it is different for
t

OR vs UNION

[Majk.Skoric]

Hi List,

i have a problem with an OR STATEMENT. Maybe someone can explain to me
why the
mysql optimizer doesn't work like expected. Please have a look at
following "similar" queries.

mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or (kunde_id=
'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1);
++-+---+-++-
---+-+--+--+
--+
| id | select_type | table | type| possible_keys  |
key| key_len | ref  | rows | Extra
|
++-+---+-++-
---+-+--+--+
--+
|  1 | SIMPLE  | KTEMP | index_merge | buchungs_kunde_id,kunde_id |
buchungs_kunde_id,kunde_id | 71,71   | NULL |2 | Using
union(buchungs_kunde_id,kunde_id); Using where |
++-+---+-++-
---+-+--+--+
--+
1 row in set (0.00 sec)

All seems fine here . Optimizer choose to use an union! This is the same
as following union query.

mysql> explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id =
'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION (SELECT
* FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = 16058
and status = 1);
++--++--+---+---
+-+---+--+-+
| id | select_type  | table  | type | possible_keys | key
| key_len | ref   | rows | Extra   |
++--++--+---+---
+-+---+--+-+
|  1 | PRIMARY  | KTEMP  | ref  | buchungs_kunde_id |
buchungs_kunde_id | 71  | const,const,const |1 | Using where |
|  2 | UNION| KTEMP  | ref  | kunde_id  | kunde_id
| 71  | const,const,const |1 | Using where |
|NULL | UNION RESULT |  | ALL  | NULL  | NULL
| NULL| NULL  | NULL | |



But the following query is handled in a strange way

mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id =
'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id =
16058 and status = 1;
++-+---+--++--+-
+--+-+-+
| id | select_type | table | type | possible_keys  | key  |
key_len | ref  | rows| Extra   |
++-+---+--++--+-
+--+-+-+
|  1 | SIMPLE  | KTEMP | ALL  | buchungs_kunde_id,kunde_id | NULL |
NULL| NULL | 1040700 | Using where |
++-+---+--++--+-
+--+-+-+

I don't get it! Maybe someone has a clue or a hint for me.

TABLEDEF.
| KTEMP | CREATE TABLE `KTEMP` (
  `tid` bigint(20) NOT NULL auto_increment,
  `kunde_id` varchar(20) collate utf8_bin NOT NULL,
  `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0',
  `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL,
  `veranst_id` bigint(20) NOT NULL,
  `rolle_nummer` int(11) default '0',
  `status` tinyint(1) unsigned NOT NULL,
  `tstamp_insert` bigint(20) NOT NULL,
  `tstamp_update` bigint(20) NOT NULL,
  `KategorienWechsel` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`tid`),
  KEY `buchungs_kunde_id` (`buchungs_kunde_id`,`veranst_id`,`status`),
  KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |


mysql> SELECT VERSION();
+-+
| VERSION()   |
+-+
| 5.0.27-standard-log |
+-+
1 row in set (0.00 sec)

Regards,

Majk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Union query

[kabel]

I have three tables in a survey system (that I didn't design)

surveys (the instance of a user taking a survey) with survey_id and show_id 
(each survey is in response to a particular show) 

responses (possible responses to questions) with response_id

response_map ( survey_id, response_id ) representing a user's answer to a 
survey.

I'm trying to determine how many surveys per show responded with response_id 
30 and response_id 28

I think I need a union here, but am not sure.

So far, I THINK I can figure out how many surveys per show responded with just 
response 30 as follows:

SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE 
m.response_id = 30 AND m.survey_id = s.survey_id GROUP BY s.show_id


Am I right in assuming that the following would give me the count of surveys 
per show that had both response 30 and 28 with the following?

SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE 
m.response_id = 30 AND m.survey_id = s.survey_id GROUP BY s.show_id UNION 
SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE 
m.response_id = 28 AND m.survey_id = s.survey_id GROUP BY s.show_id;


Thanks in advance for any help

kabel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: "union" operator problems in MySQL v3.23?

[Lopez David E]

glenn

Union is 4.0 feature.

david 

>Notwithstanding end-of-life status, is there an issue in MySQL 
>v3.23 r.e. the "union" operator?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "union" operator problems in MySQL v3.23?

[Glenn Gillis]

Jim Winstead wrote, On 1/11/2008 12:54 PM:

On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
Notwithstanding end-of-life status, is there an issue in MySQL v3.23 
r.e. the "union" operator?


My installation returns a syntax error on any query containing the union 
operator, even with the sample queries provided in the documentation:


  mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
  ERROR 1064: syntax error near 'UNION SELECT REPEAT('b',10)' at line 1


Support for UNION was not added until 4.0.

Jim Winstead
MySQL Inc.


Thanks, Jim and David. I guess it's about time to upgrade!
--
Glenn

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "union" operator problems in MySQL v3.23?

[Jim Winstead]

On Fri, Jan 11, 2008 at 12:28:05PM -0800, Glenn Gillis wrote:
> Notwithstanding end-of-life status, is there an issue in MySQL v3.23 
> r.e. the "union" operator?
> 
> My installation returns a syntax error on any query containing the union 
> operator, even with the sample queries provided in the documentation:
> 
>   mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
>   ERROR 1064: syntax error near 'UNION SELECT REPEAT('b',10)' at line 1

Support for UNION was not added until 4.0.

Jim Winstead
MySQL Inc.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

"union" operator problems in MySQL v3.23?

[Glenn Gillis]

Notwithstanding end-of-life status, is there an issue in MySQL v3.23 
r.e. the "union" operator?


My installation returns a syntax error on any query containing the union 
operator, even with the sample queries provided in the documentation:


  mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
  ERROR 1064: syntax error near 'UNION SELECT REPEAT('b',10)' at line 1

--
Thanks,

Glenn Gillis
ELAW U.S. Information Technology Manager
Environmental Law Alliance Worldwide
http://www.elaw.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: BUG in UNION implementation?! Confimation or Explaination please

[Joshua J. Kugler]

On Wednesday 11 July 2007 00:34, Anders Karlsson wrote:
> UNION will only return distinct rows. This is according to spec and to
> the SQL Standard.

And of course, to no one's surprise, this also matches the mathematical 
definition of union: 

j

-- 
Joshua Kugler   
Lead System Admin -- Senior Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0xDB26D7CE
PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: BUG in UNION implementation?! Confimation or Explaination please

[Rhys Campbell]

UNION is mean to removed duplicate rows. Use "UNION ALL" if you don't want
this to happen.

http://dev.mysql.com/doc/refman/5.0/en/union.html

-Original Message-
From: list account [mailto:[EMAIL PROTECTED]
Sent: 11 July 2007 09:19
To: mysql@lists.mysql.com
Subject: BUG in UNION implementation?! Confimation or Explaination
please


Hi all,
I believe to have found a bug in MySQL's union implementation. Can someone
confirm this, please or convince me that this is not a buggy behaviour of
mysql :

UNION seems to behave like DISTINCT by default:

mysql> select 2 c1
-> union
-> select 1 c1
-> union
-> select 2 c1
-> union
-> select 1 c1;
++
| c1 |
++
|  2 |
|  1 |
++
2 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union select 1
c1,4;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
|  1 | 4 |
++---+
4 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union select
1,2;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
++---+
3 rows in set (0.00 sec)

mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
c1),count(*)  from
-> (
-> select 2 c1
-> union
-> select 1 c1
-> union
-> select 1 c1
-> union
-> select 1
-> ) a
-> ;
+---++---+-+---+--+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+---++---+-+---+--+
|1.5000 | 1.5000 | 3 |   2 | 2 |2 |
+---++---+-+---+--+
1 row in set (0.00 sec)

but I would have expected:

+---++---+-+---+--+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+---++---+-+---+--+
|1.2500 | 1.5000 | 5 |   4 |  2 |4 |
+---++---+-+---+--+


TIA,

CVH

This email is confidential and may also be privileged. If you are not the 
intended recipient please notify us immediately by telephoning +44 (0)20 7452 
5300 or email [EMAIL PROTECTED] You should not copy it or use it for any 
purpose nor disclose its contents to any other person. Touch Local cannot 
accept liability for statements made which are clearly the sender's own and are 
not made on behalf of the firm.

Touch Local Limited
Registered Number: 2885607
VAT Number: GB896112114
Cardinal Tower, 12 Farringdon Road, London EC1M 3NN
+44 (0)20 7452 5300


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: BUG in UNION implementation?! Confimation or Explaination please

[Anders Karlsson]

UNION will only return distinct rows. This is according to spec and to 
the SQL Standard. To avoid this, use UNION ALL instead of UNION. Try 
that with your queries and you'll see that this will do the trick. This 
is, as I said, in accordance with the standard and the way all SQL based 
databases work.


Quoting SQL 2003 section 4.10.6.2:
"MULTISET UNION is an operator that computes the union of two multisets. 
There are two variants, specified

using ALL or DISTINCT, to either retain duplicates or remove duplicates."
Where UNION DISTINCT is the default if neither DISTINCT nor ALL is 
specified then.


Cheers
/Karlsson
list account wrote:

Hi all,
I believe to have found a bug in MySQL's union implementation. Can 
someone

confirm this, please or convince me that this is not a buggy behaviour of
mysql :

UNION seems to behave like DISTINCT by default:

mysql> select 2 c1
   -> union
   -> select 1 c1
   -> union
   -> select 2 c1
   -> union
   -> select 1 c1;
++
| c1 |
++
|  2 |
|  1 |
++
2 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union 
select 1

c1,4;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
|  1 | 4 |
++---+
4 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union 
select

1,2;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
++---+
3 rows in set (0.00 sec)

mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
c1),count(*)  from
   -> (
   -> select 2 c1
   -> union
   -> select 1 c1
   -> union
   -> select 1 c1
   -> union
   -> select 1
   -> ) a
   -> ;
+---++---+-+---+--+ 

|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | 
count(*) |
+---++---+-+---+--+ 

|1.5000 | 1.5000 | 3 |   2 | 2 
|2 |
+---++---+-+---+--+ 


1 row in set (0.00 sec)

but I would have expected:

+---++---+-+---+--+ 

|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | 
count(*) |
+---++---+-+---+--+ 


|1.2500 | 1.5000 | 5 |   4 |  2 |4 |
+---++---+-+---+--+ 




TIA,

CVH




--
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /  Anders Karlsson ([EMAIL PROTECTED])
 / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/  /_/\_, /___/\___\_\___/ Stockholm
   <___/   www.mysql.com Cellphone: +46 708 608121
  Skype: drdatabase



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

BUG in UNION implementation?! Confimation or Explaination please

[list account]

Hi all,
I believe to have found a bug in MySQL's union implementation. Can someone
confirm this, please or convince me that this is not a buggy behaviour of
mysql :

UNION seems to behave like DISTINCT by default:

mysql> select 2 c1
   -> union
   -> select 1 c1
   -> union
   -> select 2 c1
   -> union
   -> select 1 c1;
++
| c1 |
++
|  2 |
|  1 |
++
2 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union select 1
c1,4;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
|  1 | 4 |
++---+
4 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union  select 2 c1,3 union select
1,2;
++---+
| c1 | 1 |
++---+
|  2 | 1 |
|  1 | 2 |
|  2 | 3 |
++---+
3 rows in set (0.00 sec)

mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
c1),count(*)  from
   -> (
   -> select 2 c1
   -> union
   -> select 1 c1
   -> union
   -> select 1 c1
   -> union
   -> select 1
   -> ) a
   -> ;
+---++---+-+---+--+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+---++---+-+---+--+
|1.5000 | 1.5000 | 3 |   2 | 2 |2 |
+---++---+-+---+--+
1 row in set (0.00 sec)

but I would have expected:

+---++---+-+---+--+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+---++---+-+---+--+
|1.2500 | 1.5000 | 5 |   4 |  2 |4 |
+---++---+-+---+--+


TIA,

CVH

FOUND_ROWS & UNION

[Taras D]

Hi everyone,

I have a couple of questions invovling using FOUND_ROWS() and UNION.
The manual states that:

"The value of FOUND_ROWS() is exact only if UNION ALL is used. If
UNION without ALL
is used, duplicate removal occurs and the value of FOUND_ROWS() is
only approximate."

I am using UNION DISTINCT. In what way is the value approximate? I
have tried some test queries using UNION DISTINCT and it seems to give
the correct number of results (ie: FOUND_ROWS isn't including the rows
that appear twice). Perhaps the 'duplicate removal occurs' statement
means that FOUND_ROWS doesn't find the number of rows in the UNION of
the result sets because DISTINCT wasn't specified (ie: it gives the
number of rows after duplicates have been removed)?

If it is the case the sometimes/all the time FOUND_ROWS doesn't give
the correct number of distinct results, ss there anyway of getting
over this limitation apart from executing the query twice (once with
the limit and once without the limit)?

Thanks

Taras

Test code:
=
(select SQL_CALC_FOUND_ROWS * from s where ID <= 20)UNION(select *
from s where ID <= 50) LIMIT 0,30;

select found_rows();

Gives 50 results, which is the correct value. If duplicate entries
were being counted, the above query would result in 70 rows.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query problem: UNION in subquery

[Luke]

A big "Thank you" goes to you! That was it!

Looks like I tried with too many parentheses i.e.

/this is wrong/

SELECT   FROM ...

(
(SELECT   FROM ...)

UNION

(SELECT   FROM ...)

) AS abc


Regards,
Luke


- Original Message - 
From: "Neeraj" <[EMAIL PROTECTED]>

To: "'Luke'" <[EMAIL PROTECTED]>; 
Sent: Wednesday, May 24, 2006 2:16 AM
Subject: RE: Query problem: UNION in subquery




Hi Luke..


Try this

SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')


UNION


SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc


Cheers :)


Neeraj Black Bits

-Original Message-
From: Luke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery

Hello!

I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.

The question is about the right syntax.

1.
This works fine /UNION/


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))


UNION


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))


2.
This works fine too /subquery/:


SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;


3.
But when I run 1&2 combined I get in troubles. This is a query draft,
can't come up with the right syntax:


SELECT ObjectId FROM


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))


UNION


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID  AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))


I tried many combinations and got various syntax errors. Any ideas?


Thanks,
Luke



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem: UNION in subquery

[Neeraj]

Hi Luke..


Try this

SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6') 


UNION 


SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))as abc


Cheers :)


Neeraj Black Bits

-Original Message-
From: Luke [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 24, 2006 9:36 AM
To: mysql@lists.mysql.com
Subject: Query problem: UNION in subquery

Hello! 

I have a problem using UNIONs inside subqueries. I have simplified my 
query to make it more readable/understandable. 

The question is about the right syntax. 

1. 
This works fine /UNION/ 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 


UNION 


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 


2. 
This works fine too /subquery/: 


SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS 
SubTable1; 


3. 
But when I run 1&2 combined I get in troubles. This is a query draft, 
can't come up with the right syntax: 


SELECT ObjectId FROM 


(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 


UNION 


(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 


I tried many combinations and got various syntax errors. Any ideas? 


Thanks, 
Luke 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query problem: UNION in subquery

[Luke]

Hello! 

I have a problem using UNIONs inside subqueries. I have simplified my 
query to make it more readable/understandable. 

The question is about the right syntax. 

1. 
This works fine /UNION/ 



(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 



UNION 



(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 



2. 
This works fine too /subquery/: 



SELECT ObjectId FROM 



(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS 
SubTable1; 



3. 
But when I run 1&2 combined I get in troubles. This is a query draft, 
can't come up with the right syntax: 



SELECT ObjectId FROM 



(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId, 
f15.Form15PatientID AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) 



UNION 



(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId, 
f15.Form15PatientID  AS PtId FROM form15 f15 
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6')) 



I tried many combinations and got various syntax errors. Any ideas? 



Thanks, 
Luke 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Sum with union, tricky question perhaps not for you

[SGreen]

"H L" <[EMAIL PROTECTED]> wrote on 04/03/2006 01:53:37 PM:

> 
> >The solution is to redesign your tables. You need to split into 
separate
> >columns the values you want to maintain. You do not want to keep the 
"flat
> >file" design you are currently trying to use.
> >
> >CREATE TABLE calendar (
> >   objectid,
> >   year,
> >   dayofyear,
> >   ... other fields...
> >)
> >
> >CREATE TABLE price (
> >   objectid,
> >   year,
> >   dayofyear,
> >   price
> >)
> >
> >Having a separate column for each day of the year may make sense to a
> >person but as you have discovered, it is extremely difficult to use for
> >any kind of ad-hoc querying.  A more normalized data structure will be
> >almost as efficient in space usage but 1000s of times more efficient 
for
> >querying.  There is no simple way to write a query that spans years 
with
> >the table structures you currently have.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> 
> 
> Hi thanks for the quick response!
> 
> Well i think you are right about this. I will probably have to redesign 
the 
> database. What do you think of using DATE instead of year,dayofyear or 
is 
> there problems i could get into then.  It would be great to be able to 
use 
> select from price where date>=somedate AND date <= tosomedate
> 
> /Henrik
> 

You could use a single date column for (year, Julian date) but if you are 
constantly querying on the Julian date (based on your application or other 
needs) then having that column would be a good thing. Without a column for 
Julian date, you could not index that value either by itself or as part of 
another index.   Which way works better for you really depends on *your* 
application's needs and I cannot guess all of them. However, you really 
should normalize that data first.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Select Sum with union, tricky question perhaps not for you

[H L]

The solution is to redesign your tables. You need to split into separate
columns the values you want to maintain. You do not want to keep the "flat
file" design you are currently trying to use.

CREATE TABLE calendar (
  objectid,
  year,
  dayofyear,
  ... other fields...
)

CREATE TABLE price (
  objectid,
  year,
  dayofyear,
  price
)

Having a separate column for each day of the year may make sense to a
person but as you have discovered, it is extremely difficult to use for
any kind of ad-hoc querying.  A more normalized data structure will be
almost as efficient in space usage but 1000s of times more efficient for
querying.  There is no simple way to write a query that spans years with
the table structures you currently have.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Hi thanks for the quick response!

Well i think you are right about this. I will probably have to redesign the 
database. What do you think of using DATE instead of year,dayofyear or is 
there problems i could get into then.  It would be great to be able to use 
select from price where date>=somedate AND date <= tosomedate


/Henrik

_
Hitta rätt på nätet med MSN Search http://search.msn.se/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Sum with union, tricky question perhaps not for you

[SGreen]

"H L" <[EMAIL PROTECTED]> wrote on 04/02/2006 11:51:48 AM:

> Hi, i have a problem to  select sum from same table using UNION.  The 
key 
> question is there a way of combining two questions in one so the 
resulting 
> objectid in query1 is only used in query 2 in a smart way. Eg. if only 
one 
> object is avaliable in a search first year then only check that objectid 
for 
> next year and append the sum in the question.
> 
> I have 4 tables
> companyobjects that contains the key (companyid,objectid,name etc)
> 
> and  i have
> calendar the table looks similar to this 
> (objectid,year,day1,day2.day365)
> price that looks similar to this (objectid,year,day1,day2.day365)
> reservation that looks similar to this 
(objectid,year,day1,day2.day365)
> 
> 
> I have tried as follows below, as you can see i want to calculate price 
> discount and amount to pay from same table but from 2 years. I have 
tried 
> removed all my "where" clause below and the result is not correct it is 
not 
> from both querys.
> 
> 
> Maybe you know an easier way, totaly diffrent way that i have not 
thought of 
> perhaps.
> 
> Thanks in advance /Henrik
> 
> 
> SELECT
> `companyobjects`.`objectid`,
> Sum(`objectprice`.`d362` +`objectprice`.`d363` 
> +`objectprice`.`d364`+`objectprice`.`d365`),
> Sum((`objectprice`.`d362` +`objectprice`.`d363` 
> +`objectprice`.`d364`+`objectprice`.`d365`)*0.1),
> Sum((`objectprice`.`d362` +`objectprice`.`d363` 
> +`objectprice`.`d364`+`objectprice`.`d365`)*0.9)
> FROM
> `companyobjects`
> Inner Join `objectprice` ON `companyobjects`.`objectid` = 
> `objectprice`.`objectid`
> Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
> `objectreservation`.`objectid`
> Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
> `objectcalendar`.`objectid`
> 
> WHERE
> `companyobjects`.`companyid` = 'C050319112022656' AND
> `companyobjects`.`maxnrofguests` >= '1' AND
> `objectprice`.`year` = '2006' AND
> `objectreservation`.`year` = '2006' AND
> `objectcalendar`.`year` = '2006'
> 
> GROUP BY
> `companyobjects`.`objectid`
> 
> UNION ALL
> 
> SELECT
> `companyobjects`.`objectid`,
> Sum(`objectprice`.`d1` +`objectprice`.`d2`),
> Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1),
> Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9)
> FROM
> `companyobjects`
> Inner Join `objectprice` ON `companyobjects`.`objectid` = 
> `objectprice`.`objectid`
> Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
> `objectreservation`.`objectid`
> Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
> `objectcalendar`.`objectid`
> WHERE
> `companyobjects`.`companyid` = 'C050319112022656' AND
> `companyobjects`.`maxnrofguests` >= '1' AND
> `objectprice`.`year` = '2007' AND
> `objectreservation`.`year` = '2007' AND
> `objectcalendar`.`year` = '2007'
> GROUP BY
> `companyobjects`.`objectid`
> 
> 
> 
> 
> 
> 
> I
> 
> _
> Hitta rätt på nätet med MSN Search http://search.msn.se/


The solution is to redesign your tables. You need to split into separate 
columns the values you want to maintain. You do not want to keep the "flat 
file" design you are currently trying to use.

CREATE TABLE calendar (
  objectid,
  year,
  dayofyear,
  ... other fields...
)

CREATE TABLE price (
  objectid,
  year,
  dayofyear,
  price
)

Having a separate column for each day of the year may make sense to a 
person but as you have discovered, it is extremely difficult to use for 
any kind of ad-hoc querying.  A more normalized data structure will be 
almost as efficient in space usage but 1000s of times more efficient for 
querying.  There is no simple way to write a query that spans years with 
the table structures you currently have.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Select Sum with union, tricky question perhaps not for you

[H L]

Hi, i have a problem to  select sum from same table using UNION.  The key 
question is there a way of combining two questions in one so the resulting 
objectid in query1 is only used in query 2 in a smart way. Eg. if only one 
object is avaliable in a search first year then only check that objectid for 
next year and append the sum in the question.


I have 4 tables
companyobjects that contains the key (companyid,objectid,name etc)

and  i have
calendar the table looks similar to this 
(objectid,year,day1,day2.day365)

price that looks similar to this (objectid,year,day1,day2.day365)
reservation that looks similar to this (objectid,year,day1,day2.day365)


I have tried as follows below, as you can see i want to calculate price 
discount and amount to pay from same table but from 2 years. I have tried 
removed all my "where" clause below and the result is not correct it is not 
from both querys.



Maybe you know an easier way, totaly diffrent way that i have not thought of 
perhaps.


Thanks in advance /Henrik


SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`),
Sum((`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`)*0.1),
Sum((`objectprice`.`d362` +`objectprice`.`d363` 
+`objectprice`.`d364`+`objectprice`.`d365`)*0.9)

FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` = 
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
`objectcalendar`.`objectid`


WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` >= '1' AND
`objectprice`.`year` = '2006' AND
`objectreservation`.`year` = '2006' AND
`objectcalendar`.`year` = '2006'

GROUP BY
`companyobjects`.`objectid`

UNION ALL

SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d1` +`objectprice`.`d2`),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9)
FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` = 
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` = 
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` = 
`objectcalendar`.`objectid`

WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` >= '1' AND
`objectprice`.`year` = '2007' AND
`objectreservation`.`year` = '2007' AND
`objectcalendar`.`year` = '2007'
GROUP BY
`companyobjects`.`objectid`






I

_
Hitta rätt på nätet med MSN Search http://search.msn.se/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with UNION

[Rhino]

"Shaun" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]

Hi,

The following 3 queries on their own produce more rows than if I UNION 
them

together:

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('W_', Work_Type_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Booking'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('T_', Task_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Task'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('U_', Unavailability_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Unavailability'

I am trying to get all types of bookings - unavailability, tasks and
bookings into one result but am confused as to why the query produces less
rows this way.

Any tips here would be greatly appreciated.


UNION removes duplicate rows from the result set; in other words, if two 
identical rows are produced by two or more of the queries that have been 
UNIONed together, the duplicate rows are removed from the final result set. 
If you don't want to remove the duplicates, use UNION ALL instead of UNION. 
(I'm not sure if UNION ALL is supported in MySQL but it certainly is in DB2, 
my main database.)


Perhaps that's why you have fewer rows in the UNION result than you do by 
summing up the row counts in the individual queries?


By the way, I'm not sure how you posted your question but my copy of Outlook 
Express thinks it's a newsgroup post, not an email so I couldn't reply in 
the normal way.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with UNION

[Gabriel PREDA]

The UNION Syntax is:

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
  [UNION [ALL | DISTINCT]
   SELECT ...]


Lower in the manual it says:
* A DISTINCT union can be produced explicitly by using UNION
DISTINCT or implicitly by using UNION with no following DISTINCT or ALLkeyword.
*
**
So the thing is DISTINCT is implicit !

You will need to add after UNION the keyword ALL !

Good luck !

--
Gabriel PREDA
Senior Web Developer

Problem with UNION

[Shaun]

Hi,

The following 3 queries on their own produce more rows than if I UNION them 
together:

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('W_', Work_Type_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Booking'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('T_', Task_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Task'

UNION

SELECT CONCAT('Y',DATE_FORMAT(Date,'%Y')) AS Booking_Year,
CONCAT('M',DATE_FORMAT(Date,'%c')) AS Booking_Month,
CONCAT('USR_', B.User_ID) AS User,
Booking_Status,
CONCAT('U_', Unavailability_ID) AS Day_Type,
'1' AS Count
FROM Bookings B, Booking_Dates BD
WHERE B.Booking_ID = BD.Booking_ID
AND B.Booking_Type = 'Unavailability'

I am trying to get all types of bookings - unavailability, tasks and 
bookings into one result but am confused as to why the query produces less 
rows this way.

Any tips here would be greatly appreciated.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 5.0.16. Bug in union?

[Gabriel PREDA]

Interesting... maybe this is because the fill is not actualy stored in the
database... and being sorted/compared as a number MySQL removes the ZEROFILL
!

You can go and do:
select BINARY * from a
union
select BINARY * from a;

--
Gabriel PREDA
Senior Web Developer


On 2/14/06, Juri Shimon <[EMAIL PROTECTED]> wrote:
>
> Hello mysql,
>
> Union on zerofilled fields eats 'zerofilling'.
>
> How to repeat:
>
> > create table a (id integer zerofill);
> > insert into a values(1),(2),(3);
> > select * from a;
> ++
> | id |
> ++
> | 01 |
> | 02 |
> | 03 |
> ++
>
> > select * from a union select * from a;
> +--+
> | id   |
> +--+
> |1 |
> |2 |
> |3 |
> +--+
>
> Where are my leading zeroes? :(
>
> Is this a known bug?
>
> PS. On 4.1.* all works as expected.
> --
> Best regards,
> Juri  mailto:[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]

5.0.16. Bug in union?

[Juri Shimon]

Hello mysql,

  Union on zerofilled fields eats 'zerofilling'.

How to repeat:

> create table a (id integer zerofill);
> insert into a values(1),(2),(3);
> select * from a;
++
| id |
++
| 01 |
| 02 |
| 03 |
++

> select * from a union select * from a;
+--+
| id   |
+--+
|1 |
|2 |
|3 |
+--+

Where are my leading zeroes? :(

  Is this a known bug?

PS. On 4.1.* all works as expected.
-- 
Best regards,
 Juri  mailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: union/collation problem, error 1267: feature or bug?

[schlubediwup]

Hi Gleb,

i finally found out a method to be entirely independent from any 
character-set as well as collation-sequence problem, when forming a 
UNION, where you occasionnally have to insert place-holders in one of 
the SELECT statements:
as (text, varchar, char) placeholders use NULL instead of "" or '' if 
your application permits it.


thanks for your suggestion an help.

suomi


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: union/collation problem, error 1267: feature or bug?

[schlubediwup]

Hi Gleb,


localhost.(none)> show session variables like "%version%";
+-+--+
| Variable_name   | Value|
+-+--+
| protocol_version| 10   |
| version | 4.1.16-standard  |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os  | pc-linux-gnu |
+-+--+
5 rows in set (0.00 sec)

localhost.(none)>

with the new version , in a first view, the problem i reported on 20. 
january 2006, does not occur anymore.


thanks for your suggestion an help.

suomi

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: union/collation problem, error 1267: feature or bug?

[Gleb Paharenko]

 | text | YES  | | NULL 
> ||
> | task_history | text | YES  | | NULL 
> ||
> | task_last_update | timestamp| YES  | | CURRENT_TIMESTAMP
> ||
> | contact_link | int(11)  | YES  | | NULL 
> ||
> | task_counter | int(10) unsigned |  | PRI | NULL  |
> auto_increment |
> +--+--+--+-+---++
> 
> 16 rows in set (0.00 sec)
> 
> localhost.addresses2>
> 
> 
> 
> 3. following character-sets and collations:
> 
> localhost.addresses2> show session variables like "character%";
> +--++
> | Variable_name| Value  |
> +--++
> | character_set_client | utf8   |
> | character_set_connection | utf8   |
> | character_set_database   | latin1 |
> | character_set_results| utf8   |
> | character_set_server | latin1 |
> | character_set_system | utf8   |
> | character_sets_dir   | /usr/share/mysql/charsets/ |
> +--++
> 7 rows in set (0.00 sec)
> 
> localhost.addresses2> show session variables like "collat%";
> +--+---+
> | Variable_name    | Value |
> +--+---+
> | collation_connection | utf8_general_ci   |
> | collation_database   | latin1_swedish_ci |
> | collation_server | latin1_swedish_ci |
> +--+---+
> 3 rows in set (0.00 sec)
> 
> localhost.addresses2>
> 
> 
> 
> 4. following problem:
> 
> when i do a union statement between the two tables, where obviousely at
> least in one select of the union some fields are left open by the
> placeholder "" or '' , these left-off fields are acted on with the
> collation_database (latin1_swedish_ci), wherease all non-left-off fields
> (i.e. those with a real column value in it are acted on with the
> collation_connection (utf8_general_ci), which then turns out in the
> error 1267.
> if i give those left off fields a _latin1 converter, everything works
> fine. to find out, however, if the _latin1 converter is necessary, is
> not easy.
> is there a better solution to solve this problem? straight away: is this
> a bug?
> 
> 
> 
> 5. following examples:
> 
> is accepted:
> (SELECT name,task_name,actiony,date_last_action,counter,task_counter
> FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
> contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> UNION (SELECT name,firm,status,'', counter,'' FROM contacts2 WHERE name
> LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100;
> 
> is accepted:
> (SELECT
> name,firm,status,businesscategory,addon,date_last_action,counter,task_counter
> FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
> contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> UNION (SELECT name,firm,status,businesscategory,addon, '', counter,''
> FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> ORDER BY counter LIMIT 100;
> 
> 
> is not accepted:
> (SELECT
> name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter
> FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
> contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> UNION (SELECT name,firm,status,businesscategory,addon, '','', counter,''
> FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> ORDER BY counter LIMIT 100;
> ERROR 1267 (HY000): Illegal mix of collations
> (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
> operation 'UNION'
> 
> 
> is accepted:
> (SELECT
> name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter
> FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link =
> contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100)
> UNION (SELECT name,firm,status,businesscategory,addon, _latin1'','',
> counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter
> LIMIT 100) ORDER BY counter LIMIT 100;
> 
> 
> 
> 
> 
> suomi


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

union/collation problem, error 1267: feature or bug?

[schlubediwup]

--+
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_results| utf8   |
| character_set_server | latin1 |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)

localhost.addresses2> show session variables like "collat%";
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--+---+
3 rows in set (0.00 sec)

localhost.addresses2>



4. following problem:

when i do a union statement between the two tables, where obviousely at 
least in one select of the union some fields are left open by the 
placeholder "" or '' , these left-off fields are acted on with the 
collation_database (latin1_swedish_ci), wherease all non-left-off fields 
(i.e. those with a real column value in it are acted on with the 
collation_connection (utf8_general_ci), which then turns out in the 
error 1267.
if i give those left off fields a _latin1 converter, everything works 
fine. to find out, however, if the _latin1 converter is necessary, is 
not easy.
is there a better solution to solve this problem? straight away: is this 
a bug?




5. following examples:

is accepted:
(SELECT name,task_name,actiony,date_last_action,counter,task_counter 
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = 
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
UNION (SELECT name,firm,status,'', counter,'' FROM contacts2 WHERE name 
LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100;


is accepted:
(SELECT 
name,firm,status,businesscategory,addon,date_last_action,counter,task_counter 
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = 
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
UNION (SELECT name,firm,status,businesscategory,addon, '', counter,'' 
FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
ORDER BY counter LIMIT 100;



is not accepted:
(SELECT 
name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter 
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = 
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
UNION (SELECT name,firm,status,businesscategory,addon, '','', counter,'' 
FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
ORDER BY counter LIMIT 100;
ERROR 1267 (HY000): Illegal mix of collations 
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for 
operation 'UNION'



is accepted:
(SELECT 
name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter 
FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = 
contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) 
UNION (SELECT name,firm,status,businesscategory,addon, _latin1'','', 
counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter 
LIMIT 100) ORDER BY counter LIMIT 100;






suomi

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

is UNION allowed in a MySQL stored procedure?

[Gordon Bruce]

I have a simple stored procedure which works as intended.

As soon as I add a UNION in the SELECT I get the error message 
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'select phon_Lvl INTO Lvl

Are UNION's currently not allowed in a stored procedure? 


mysql> delimiter //
mysql> create procedure ph()
-> BEGIN
->   DECLARE LVL Char(10);
->   select phon_Lvl INTO Lvl
->   FROM   phones
->
->   limit 1;
->   SET @Lvl:=Lvl;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call ph();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> Select @Lvl;
+--+
| @Lvl |
+--+
| locn |
+--+
1 row in set (0.00 sec)

mysql>
mysql> drop procedure if exists ph;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create procedure ph()
-> BEGIN
->   DECLARE LVL Char(10);
->   select phon_Lvl INTO Lvl
->   FROM   phones
->   UNION
->   Select "a" into LVL
->   limit 1;
->   SET @Lvl:=Lvl;
-> END//
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'select phon_Lvl INTO Lvl
  FROM   phones
  UNION
  Select "a" into LVL
  limi' at line 4
mysql> delimiter ;
mysql>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Insert into table select * ... union

[Detlev Jaeger]

Hello,
 
I'm collecting data into a very simple table with one varchar(100) field
("myValue") and one int(10) field ("Qty"). I'm doing this with a simple
"insert into . on duplicate key update qty = qty +1".
 
After the table has reached a count of several 10 million records every
insert takes several seconds - and that's much to slow for my
application.
 
To increase the speed, I have splitted the table into 50 parts
(table-01, table-02 and so on) and my application is rotating through
this tables. For speed reasons it's ok.
 
But now I need to export the "top one hundred" of Qty values of each
table into another summary table and, of course, are trying to do the
same "on duplicate key update" thing. I know that it is mathematically
nonsense but it is exact enough for my purposes, because the "top one
hundred" values are very big and the other Qty values are very small...
 
By now, I'm sending 50 "insert into summarytable select * from table-xx
on duplicate key update commands.
 
After explaining my details, I would like to know, if there is a
possibility of a command like
 
insert into summarytable
select * from `table-01` union select * from `table-02`  union
select * from `table-02`
on duplicate key update Qty ...
 
I have tried a lot of possibilities, but nearly all caused a sql error
message, the other ones did not do what i want...
 
Is any one out there who has a good idea of how I can do this in one
command ?
 
Thanks in advance for any possible help
 
Detlev
 

Re: About union sql Mysql 4.x

[Michael Stassen]

HALIL DEMIREZEN wrote:

Michael,

Thank you and all for effort to help.. I solved the problem by giving high 
limit numbers  such as;



(select * from tablea where item=1 order by rand() limit 0, 1) 
union all
(select  * from tablea where item != 1 order by rand() limit 0, 
1);


I think this is not so much a solution as a temporary work-around.  It will stop 
working correctly, without giving any errors, as soon as your table gets to a 
certain size.  Perhaps that will never happen in this particular case, but I 
think this sort of thing is a bad idea, in general.


Your desire is to sort the rows of tablea.  You want all the rows with item = 1 
first, then all the rest.  Within each group (item = 1, item != 1), you want the 
rows in random order.  You should see that this is simply a two step ordering. 
Instead of trying to fake that with unioned queries, you should solve it 
directly in your ORDER BY clause.  Both the query Gleb sent,


  SELECT * FROM tablea ORDER BY IF(item=1,0,1), RAND();

and the one I sent,

 SELECT * FROM tablea ORDER BY (item != 1), RAND();

do just that.  Both will provide the results you asked for, and neither will 
break when the table reaches some particular size.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: About union sql Mysql 4.x

[Gleb Paharenko]

Hello.



If you want all records with item=1 to be at the beginning and

sorted in a random order you can use this query:



select * from tablea order by if(item=1,0,1),  rand();





HALIL DEMIREZEN wrote:

> What if i want to list all the records not limiting them to a constant?

> 

> 

> 

> 

> Halil Demirezen 

> System Support Engineer/ Sistem Destek Muhendisi 

> Mobile Tel/Cep Tel: +90(543) 502 04 42

> E-Mail/E-Posta: [EMAIL PROTECTED]

> ---

> 

> 

> 

> 

> Gleb Paharenko <[EMAIL PROTECTED]> 

> 05.12.2005 12:19

> 

> To

> mysql@lists.mysql.com

> cc

> 

> Subject

> Re: About union sql  Mysql 4.x

> 

> 

> 

> 

> 

> 

> Hello.

> 

> 

> 

> From http://dev.mysql.com/doc/refman/5.0/en/union.html:

> 

> 

> 

> "ORDER BY for individual SELECT statements within parentheses has an

> 

> effect only when combined with LIMIT. Otherwise, the ORDER BY is

> 

> optimized away."

> 

> 

> 

> 

> 

> Therefore you're getting the same results, because ORDER BY doen't

> 

> work for your query.

> 

> 

> 

> 

> 

> 

> 

> HALIL DEMIREZEN wrote:

> 

> 

>>Hi,

> 

> 

> 

>>I am trying to run an sql query such as below to list items=x randomly 

> 

> and 

> 

> 

>>then items != x randomly.. 

> 

> 

> 

> 

>>mysql> (select * from tablea where item=1 order by rand()) union all 

> 

> 

>>(select  * from tablea where item != 1 order by rand());

> 

> 

> 

>>but the result is not as expected. rand() seems not to be working...

> 

> 

> 

>>What can be the problem? or what can be the difference between my will 

> 

> and 

> 

> 

>>the exact result of the query?

> 

> 

> 

>>Thanks..

> 

> 

> 

> 

>>

> 

> 

>>Halil Demirezen 

> 

> 

>>System Support Engineer/ Sistem Destek Muhendisi 

> 

> 

>>Mobile Tel/Cep Tel: +90(543) 502 04 42

> 

> 

>>E-Mail/E-Posta: [EMAIL PROTECTED]

> 

> 

>>---

> 

> 

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: About union sql Mysql 4.x

[HALIL DEMIREZEN]

Michael,

Thank you and all for effort to help.. I solved the problem by giving high 
limit numbers  such as;


(select * from tablea where item=1 order by rand() limit 0, 1) 
union all
(select  * from tablea where item != 1 order by rand() limit 0, 
1);


Halil Demirezen 
System Support Engineer/ Sistem Destek Muhendisi 
Mobile Tel/Cep Tel: +90(543) 502 04 42
E-Mail/E-Posta: [EMAIL PROTECTED]
---




Michael Stassen <[EMAIL PROTECTED]> 
05.12.2005 15:46

To
HALIL DEMIREZEN <[EMAIL PROTECTED]>
cc
Gleb Paharenko <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Subject
Re: About union sql  Mysql 4.x






HALIL DEMIREZEN wrote:
 > Hi,
 >
 > I am trying to run an sql query such as below to list items=x randomly 
and
 > then items != x randomly..
 >
 >
 > mysql> (select * from tablea where item=1 order by rand()) union all
 > (select  * from tablea where item != 1 order by rand());
 >
 > but the result is not as expected. rand() seems not to be working...
 >
 > What can be the problem? or what can be the difference between my will 
and
 > the exact result of the query?

Gleb Paharenko wrote:
 > Hello.
 >
 >>From http://dev.mysql.com/doc/refman/5.0/en/union.html:
 >
 > "ORDER BY for individual SELECT statements within parentheses has an
 > effect only when combined with LIMIT. Otherwise, the ORDER BY is
 > optimized away."
 >
 > Therefore you're getting the same results, because ORDER BY doen't
 > work for your query.

HALIL DEMIREZEN wrote:
> What if i want to list all the records not limiting them to a constant?

How about

   SELECT * FROM tablea
   ORDER BY (item != 1), RAND();

Michael

Re: About union sql Mysql 4.x

[Michael Stassen]

HALIL DEMIREZEN wrote:
> Hi,
>
> I am trying to run an sql query such as below to list items=x randomly and
> then items != x randomly..
>
>
> mysql> (select * from tablea where item=1 order by rand()) union all
> (select  * from tablea where item != 1 order by rand());
>
> but the result is not as expected. rand() seems not to be working...
>
> What can be the problem? or what can be the difference between my will and
> the exact result of the query?

Gleb Paharenko wrote:
> Hello.
>
>>From http://dev.mysql.com/doc/refman/5.0/en/union.html:
>
> "ORDER BY for individual SELECT statements within parentheses has an
> effect only when combined with LIMIT. Otherwise, the ORDER BY is
> optimized away."
>
> Therefore you're getting the same results, because ORDER BY doen't
> work for your query.

HALIL DEMIREZEN wrote:

What if i want to list all the records not limiting them to a constant?


How about

  SELECT * FROM tablea
  ORDER BY (item != 1), RAND();

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: About union sql Mysql 4.x

[HALIL DEMIREZEN]

What if i want to list all the records not limiting them to a constant?




Halil Demirezen 
System Support Engineer/ Sistem Destek Muhendisi 
Mobile Tel/Cep Tel: +90(543) 502 04 42
E-Mail/E-Posta: [EMAIL PROTECTED]
---




Gleb Paharenko <[EMAIL PROTECTED]> 
05.12.2005 12:19

To
mysql@lists.mysql.com
cc

Subject
Re: About union sql  Mysql 4.x






Hello.



>From http://dev.mysql.com/doc/refman/5.0/en/union.html:



"ORDER BY for individual SELECT statements within parentheses has an

effect only when combined with LIMIT. Otherwise, the ORDER BY is

optimized away."





Therefore you're getting the same results, because ORDER BY doen't

work for your query.







HALIL DEMIREZEN wrote:

> Hi,

> 

> I am trying to run an sql query such as below to list items=x randomly 
and 

> then items != x randomly.. 

> 

> 

> mysql> (select * from tablea where item=1 order by rand()) union all 

> (select  * from tablea where item != 1 order by rand());

> 

> but the result is not as expected. rand() seems not to be working...

> 

> What can be the problem? or what can be the difference between my will 
and 

> the exact result of the query?

> 

> Thanks..

> 

> 

> 

> Halil Demirezen 

> System Support Engineer/ Sistem Destek Muhendisi 

> Mobile Tel/Cep Tel: +90(543) 502 04 42

> E-Mail/E-Posta: [EMAIL PROTECTED]

> ---

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

Re: About union sql Mysql 4.x

[Gleb Paharenko]

Hello.



>From http://dev.mysql.com/doc/refman/5.0/en/union.html:



"ORDER BY for individual SELECT statements within parentheses has an

effect only when combined with LIMIT. Otherwise, the ORDER BY is

optimized away."





Therefore you're getting the same results, because ORDER BY doen't

work for your query.







HALIL DEMIREZEN wrote:

> Hi,

> 

> I am trying to run an sql query such as below to list items=x randomly and 

> then items != x randomly.. 

> 

> 

> mysql> (select * from tablea where item=1 order by rand()) union all 

> (select  * from tablea where item != 1 order by rand());

> 

> but the result is not as expected. rand() seems not to be working...

> 

> What can be the problem? or what can be the difference between my will and 

> the exact result of the query?

> 

> Thanks..

>  

> 

> 

> Halil Demirezen 

> System Support Engineer/ Sistem Destek Muhendisi 

> Mobile Tel/Cep Tel: +90(543) 502 04 42

> E-Mail/E-Posta: [EMAIL PROTECTED]

> ---

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

About union sql Mysql 4.x

[HALIL DEMIREZEN]

Hi,

I am trying to run an sql query such as below to list items=x randomly and 
then items != x randomly.. 


mysql> (select * from tablea where item=1 order by rand()) union all 
(select  * from tablea where item != 1 order by rand());

but the result is not as expected. rand() seems not to be working...

What can be the problem? or what can be the difference between my will and 
the exact result of the query?

Thanks..
 


Halil Demirezen 
System Support Engineer/ Sistem Destek Muhendisi 
Mobile Tel/Cep Tel: +90(543) 502 04 42
E-Mail/E-Posta: [EMAIL PROTECTED]
---

Re: Does MySQL 3.23.58 Support UNION

[SGreen]

"Simon Longstaff" <[EMAIL PROTECTED]> wrote on 
10/28/2005 10:50:24 AM:

> I'm trying to run this :
> 
> SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID 
=
> B.U_Number and B.U_Username = 'user1' 
> UNION
> SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID 
=
> D.U_Number and D.U_Username = 'user2' 
> 
> and it's failing saying 
> 
> SQL Error: You have an error in your SQL syntax near 'UNION SELECT 
DISTINCT
> A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID ' at line 2
> SQL Error #: 1064
> Query: SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE
> A.B_PosterID = B.U_Number and B.U_Username = 'Simon' UNION SELECT 
DISTINCT
> A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number 
and
> B.U_Username = 'MartyX' 
> 
> 
> I've been using DB2 SQL for ages.
> 
> 

According to the book for your version:
http://dev.mysql.com/doc/refman/4.1/en/index.html

The UNION keyword:
http://dev.mysql.com/doc/refman/4.1/en/union.html

Union is supported starting with version 4.0.0. Version 5.0 is the current 
production version of MySQL; it may be time to consider an upgrade. What 
do you think? If you are not interested in upgrading all the way to 5.0, 
4.1 was the leading production version until 5.0 was released. You may 
want to upgrade to just that

http://dev.mysql.com/doc/refman/4.1/en/upgrade.html
or
http://dev.mysql.com/doc/refman/5.0/en/upgrade.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Does MySQL 3.23.58 Support UNION

[Simon Longstaff]

I'm trying to run this :
 
SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID =
B.U_Number and B.U_Username = 'user1' 
UNION
SELECT DISTINCT C.B_IP FROM w3t_Posts C , w3t_Users D WHERE C.B_PosterID =
D.U_Number and D.U_Username = 'user2' 

and it's failing saying 
 
SQL Error: You have an error in your SQL syntax near 'UNION SELECT DISTINCT
A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID ' at line 2
SQL Error #: 1064
Query: SELECT DISTINCT A.B_IP FROM w3t_Posts A , w3t_Users B WHERE
A.B_PosterID = B.U_Number and B.U_Username = 'Simon' UNION SELECT DISTINCT
A.B_IP FROM w3t_Posts A , w3t_Users B WHERE A.B_PosterID = B.U_Number and
B.U_Username = 'MartyX' 

 
I've been using DB2 SQL for ages.
 
 

Re: MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again

[Gleb Paharenko]

Hello.



> MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) 

>and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again



Check the character set of fields in you tables with

'SHOW CREATE TABLE' statement. I recommend you to test if

the problem remains in 4.1.14 version, because the similar

bug was fixed in 4.1.11. See:



  http://bugs.mysql.com/bug.php?id=6519





Jeff Kolber <[EMAIL PROTECTED]> wrote:

>I recently converted the entire database to utf8 - made sure all the

>connections are utf8 etc -- made php use utf8 - set the doctype on the

>page to utf8 -- when I run the same query in the mysql monitor it runs

>fine - when apache/php run it it fails to deal with the collation.

>

>the data was converted via mysqldump to text file and reimporting

>changing all tables/database to utf.

>

>fwiw: the query looks like this:

>

>( SELECT meetings.id , meetings.name , meetings.status , ' '

>contact_name , ' ' contact_id , meetings.date_start ,

>meetings.parent_id , meetings.parent_type , meetings.time_start ,

>'meetings' panel_name FROM meetings where ( meetings.parent_id=

>'63301596-6175-1b89-75df-431283170495' AND

>meetings.parent_type='Opportunities' AND meetings.deleted=0 AND

>(meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL (

>SELECT tasks.id , tasks.name , tasks.status ,

>CONCAT(CONCAT(jt0.first_name , ' '), CONCAT(jt0.last_name , ' '))

>contact_name, tasks.contact_id , tasks.date_due , tasks.parent_id ,

>tasks.parent_type , tasks.time_due , 'tasks' panel_name FROM tasks

>LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0

>where ( tasks.parent_id= '63301596-6175-1b89-75df-431283170495' AND

>tasks.parent_type='Opportunities' AND tasks.deleted=0 AND

>(tasks.status='Not Started' OR tasks.status='In Progress' OR

>tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL (

>SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' '

>contact_id , calls.date_start , calls.parent_id , calls.parent_type ,

>calls.time_start , 'calls' panel_name FROM calls where (

>calls.parent_id= '63301596-6175-1b89-75df-431283170495' AND

>calls.parent_type='Opportunities' AND calls.deleted=0 AND

>(calls.status='Planned')) AND calls.deleted=0 )

>

>and in this case it doesn't return anything - which is correct given the data.

>

>we are using: mysql  Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i386)

>Server characterset:utf8

>Db characterset:utf8

>Client characterset:utf8

>Conn.  characterset:utf8

>

>I've seen some stuff that versions before 4.1.11 suffered from

>collation issues - is this likely to my case or can anyone see some

>other path through this - we have a single production database that we

>are very reluctant to update at this time.

>

>should i just switch back to latin1 ?

>

>thanks mysql list - you guys rock,

>

>lost in translation

>

>Jeff



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again

[Jeff Kolber]

Hi list,

I've got a query coming out of sugarCRM that is generating this error:

MySQL error 1267: Illegal mix of collations
(latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for
operation 'UNION'

I recently converted the entire database to utf8 - made sure all the
connections are utf8 etc -- made php use utf8 - set the doctype on the
page to utf8 -- when I run the same query in the mysql monitor it runs
fine - when apache/php run it it fails to deal with the collation.

the data was converted via mysqldump to text file and reimporting
changing all tables/database to utf.

fwiw: the query looks like this:

( SELECT meetings.id , meetings.name , meetings.status , ' '
contact_name , ' ' contact_id , meetings.date_start ,
meetings.parent_id , meetings.parent_type , meetings.time_start ,
'meetings' panel_name FROM meetings where ( meetings.parent_id=
'63301596-6175-1b89-75df-431283170495' AND
meetings.parent_type='Opportunities' AND meetings.deleted=0 AND
(meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL (
SELECT tasks.id , tasks.name , tasks.status ,
CONCAT(CONCAT(jt0.first_name , ' '), CONCAT(jt0.last_name , ' '))
contact_name, tasks.contact_id , tasks.date_due , tasks.parent_id ,
tasks.parent_type , tasks.time_due , 'tasks' panel_name FROM tasks
LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0
where ( tasks.parent_id= '63301596-6175-1b89-75df-431283170495' AND
tasks.parent_type='Opportunities' AND tasks.deleted=0 AND
(tasks.status='Not Started' OR tasks.status='In Progress' OR
tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL (
SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' '
contact_id , calls.date_start , calls.parent_id , calls.parent_type ,
calls.time_start , 'calls' panel_name FROM calls where (
calls.parent_id= '63301596-6175-1b89-75df-431283170495' AND
calls.parent_type='Opportunities' AND calls.deleted=0 AND
(calls.status='Planned')) AND calls.deleted=0 )

and in this case it doesn't return anything - which is correct given the data.

we are using: mysql  Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i386)
Server characterset:utf8
Db characterset:utf8
Client characterset:utf8
Conn.  characterset:utf8

I've seen some stuff that versions before 4.1.11 suffered from
collation issues - is this likely to my case or can anyone see some
other path through this - we have a single production database that we
are very reluctant to update at this time.

should i just switch back to latin1 ?

thanks mysql list - you guys rock,

lost in translation

Jeff

RE: Union vs OR

[SGreen]

"Gordon Bruce" <[EMAIL PROTECTED]> wrote on 08/26/2005 05:04:17 PM:

> It's getting late on Friday, but couldn't you build a table with all of
> the parameter combinations and then just join against that table?
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 26, 2005 4:25 PM
> To: mysql@lists.mysql.com
> Subject: Union vs OR
> 
> I have a table that holds attributes for users. This is the structure:
> 
> TABLE properties (
>   id int(11) NOT NULL,
>   userid int(11) NOT NULL,
>   attrType int(11) NOT NULL,
>   attrValue text NOT NULL,
>   FULLTEXT KEY propValue (propValue)
> ) TYPE=MyISAM;
> 
> The table is used to find people based on criteria.
> 
> A simple query:
> 
> select
> userID, attrType, attrValue from properties
> where
> propType = 1
> and
> propValue= 'some value'
> 
> The problem I'm running into is that the number of attributes could be
> over
> 50.
> 
> Would a query with many sets of
> 
> (propType = 1 and propValue= 'some value')
> or
> (propType = 2 and propValue= 'some other value')
> or ...
> 
> work better than doing the same thing with unions?
> 
> Or does anyone have an alternate solution?
> 
> Thanks for any help!
> 
> -- Avi
> 

I think Gordon's suggestion has merit. Create a temporary table like

CREATE TEMPORARY TABLE tmpSearch (
PropType
, PropValue
, KEY(PropType, PropValue)
);

and populate it with the list of search parameters

INSERT tmpSearch(Proptype, propvalue) VALUES (1,'some value'),(2,'some 
other value'),(2,'an alternative to some other value');

Then JOIN this table to your data:

SELECT ...
FROM properties p
INNER JOIN tmpSearch ts
ON ts.PropType = p.PropType
AND ts.PropValue = p.PropValue;


That will give you a list of all property records that match your search 
conditions (the same thing you would have had with the OR-ed query).

If you don't want to do this, I think the UNION form would probably make 
better use of any indexes.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Union vs OR

[Gordon Bruce]

It's getting late on Friday, but couldn't you build a table with all of
the parameter combinations and then just join against that table?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs OR

I have a table that holds attributes for users. This is the structure:

TABLE properties (
  id int(11) NOT NULL,
  userid int(11) NOT NULL,
  attrType int(11) NOT NULL,
  attrValue text NOT NULL,
  FULLTEXT KEY propValue (propValue)
) TYPE=MyISAM;

The table is used to find people based on criteria.

A simple query:

select
userID, attrType, attrValue from properties
where
propType = 1
and
propValue= 'some value'

The problem I'm running into is that the number of attributes could be
over
50.

Would a query with many sets of

(propType = 1 and propValue= 'some value')
or
(propType = 2 and propValue= 'some other value')
or ...

work better than doing the same thing with unions?

Or does anyone have an alternate solution?

Thanks for any help!

-- Avi



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Union vs OR

[avrombay]

I have a table that holds attributes for users. This is the structure:

TABLE properties (
  id int(11) NOT NULL,
  userid int(11) NOT NULL,
  attrType int(11) NOT NULL,
  attrValue text NOT NULL,
  FULLTEXT KEY propValue (propValue)
) TYPE=MyISAM;

The table is used to find people based on criteria.

A simple query:

select
userID, attrType, attrValue from properties
where
propType = 1
and
propValue= 'some value'

The problem I'm running into is that the number of attributes could be over
50.

Would a query with many sets of

(propType = 1 and propValue= 'some value')
or
(propType = 2 and propValue= 'some other value')
or ...

work better than doing the same thing with unions?

Or does anyone have an alternate solution?

Thanks for any help!

-- Avi



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Florian Burkart]

Hey Gleb,
thanks for taking your time!

php.ini:

; As of 4.0b4, PHP always outputs a character encoding by default in
; the Content-type: header.  To disable sending of the charset, simply
; set it to be empty.
;
; PHP's built-in default is text/html
default_mimetype = "text/html"
default_charset = "utf-8"


mysql version:
mysql  Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 5.0

php version:
PHP 4.4.0 (cli) (built: Aug 23 2005 14:55:11)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies

apache2:
Server version: Apache/2.0.54
Server built:   Aug 24 2005 13:41:09
Server's Module Magic Number: 20020903:9
Architecture:   32-bit
Server compiled with
 -D APACHE_MPM_DIR="server/mpm/prefork"
 -D APR_HAS_SENDFILE
 -D APR_HAS_MMAP
 -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
 -D APR_USE_SYSVSEM_SERIALIZE
 -D APR_USE_PTHREAD_SERIALIZE
 -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
 -D APR_HAS_OTHER_CHILD
 -D AP_HAVE_RELIABLE_PIPED_LOGS
 -D HTTPD_ROOT="/usr"
 -D SUEXEC_BIN="/usr/sbin/suexec2"
 -D DEFAULT_PIDLOG="/var/run/httpd.pid"
 -D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
 -D DEFAULT_LOCKFILE="/var/run/accept.lock"
 -D DEFAULT_ERRORLOG="logs/error_log"
 -D AP_TYPES_CONFIG_FILE="/etc/apache2/mime.types"
 -D SERVER_CONFIG_FILE="/etc/apache2/httpd.conf"



The text in the browser coming from PHP is as well as the plain HTML utf-8 
encoded, it is only the mysql-queries in php which return iso - besides the 
original problem that the query sends strings as iso...

:/

Thanks again,
Florian

On Thu, 25 Aug 2005 10:58:07 +0300
Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> Hello.
> 
> What is the value of the default_charset variable in your php.ini file?
> What version of MySQL do you use?
> 
> 
> 
> Florian Burkart <[EMAIL PROTECTED]> wrote:
> > What might help as well is another problem I have:
> > 
> > Somehow, the data I am getting out of mysql and php and is being served by =
> > apache is still in iso format, and not utf8. Which leads to bad displaying =
> > (unless i switch back to iso in the browser, but then the html stuff in utf=
> > 8 gets strange (which is served correctly in utf8).=20
> > 
> > Is that related? If not, still someone with hints on it?
> > 
> > 
> > On Wed, 24 Aug 2005 20:55:21 +0300
> > Gleb Paharenko <[EMAIL PROTECTED]> wrote:
> > 
> 
> 
> -- 
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
><___/   www.mysql.com
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 


-- 
Zur Zeit befinde ich mich hinter einer Firewall welche das versenden von 
E-Mails über die Adresse [EMAIL PROTECTED] verhindert. Ihr erhaltet Antworten 
auf E-Mails an [EMAIL PROTECTED] daher ebenfalls von [EMAIL PROTECTED] Es macht 
keinen Unterschied welche der beiden Adressen ihr anschreibt.

Meine alte Handynummer +49-151-11616247 ist gekündigt. Hier in Guadalajara, 
Mexico, erreicht ihr mich unter der +52-3310650934.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Gleb Paharenko]

Hello.



What is the value of the default_charset variable in your php.ini file?

What version of MySQL do you use?







Florian Burkart <[EMAIL PROTECTED]> wrote:

> What might help as well is another problem I have:

> 

> Somehow, the data I am getting out of mysql and php and is being served by =

> apache is still in iso format, and not utf8. Which leads to bad displaying =

> (unless i switch back to iso in the browser, but then the html stuff in utf=

> 8 gets strange (which is served correctly in utf8).=20

> 

> Is that related? If not, still someone with hints on it?

> 

> 

> On Wed, 24 Aug 2005 20:55:21 +0300

> Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Florian Burkart]

What might help as well is another problem I have:

Somehow, the data I am getting out of mysql and php and is being served by 
apache is still in iso format, and not utf8. Which leads to bad displaying 
(unless i switch back to iso in the browser, but then the html stuff in utf8 
gets strange (which is served correctly in utf8). 

Is that related? If not, still someone with hints on it?


On Wed, 24 Aug 2005 20:55:21 +0300
Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> Hello.
> 
> Your character_set_xxx variables is ok. The problem could be in
> the table definition. Send the output of
>   SHOW CREATE TABLE tbl_gruppen;
> 
> Florian Burkart <[EMAIL PROTECTED]> wrote:
> > Hey everyone,
> > 
> > can't figure this one out, might be easy for one of you.
> > 
> > 
> > 
> > This is the query:
> > (SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
> > UNION ALL
> > (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
> > FROM tbl_gruppen
> > ORDER BY gruppe);
> > 
> > 
> > 
> > This error message is returned on the php website:
> > Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and 
> > (utf8_general_ci,IMPLICIT) for operation 'UNION'
> > 
> > 
> > 
> > There is no error message (query executes successfully) in phpmyadmin or 
> > when connecting by mysql.
> > 
> > 
> > 
> > Following query always executes successfully (no long term solution though):
> > (SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
> > UNION ALL
> > (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
> > FROM tbl_gruppen
> > ORDER BY gruppe);
> > 
> > 
> > 
> > This is the output of
> > mysql> show variables like "%character%"; show variables like "%collation%";
> > +--++
> > | Variable_name| Value  |
> > +--++
> > | character_set_client | utf8   |
> > | character_set_connection | utf8   |
> > | character_set_database   | utf8   |
> > | character_set_results| utf8   |
> > | character_set_server | utf8   |
> > | character_set_system | utf8   |
> > | character_sets_dir   | /usr/share/mysql/charsets/ |
> > +--++
> > 7 rows in set (0.00 sec)
> > 
> > +--+-+
> > | Variable_name| Value   |
> > +--+-+
> > | collation_connection | utf8_general_ci |
> > | collation_database   | utf8_general_ci |
> > | collation_server | utf8_general_ci |
> > +--+-+
> > 3 rows in set (0.00 sec)
> > 
> > 
> > 
> > This is my.cnf
> > ...
> > [mysql]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqladmin]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqlcheck]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqldump]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqlimport]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqlshow]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [myisamchk]
> > character-sets-dir=/usr/share/mysql/charsets
> > 
> > [myisampack]
> > character-sets-dir=/usr/share/mysql/charsets
> > 
> > # use [safe_mysqld] with mysql-3
> > [mysqld_safe]
> > err-log = /var/log/mysql/mysql.err
> > 
> > # add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
> > [mysqld]
> > character-set-server= utf8
> > default-character-set   = utf8
> > ...
> > 
> > 
> > 
> > Probably easy, but help please :)
> > Florian
> > 
> 
> 
> -- 
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__  ___ ___   __
&

Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Florian Burkart]

mysql> show create table tbl_gruppen;
+-++
| Table   | Create Table



   |
+-++
| tbl_gruppen | CREATE TABLE `tbl_gruppen` (
  `gruppe_id` smallint(4) unsigned NOT NULL auto_increment,
  `gruppe` varchar(50) NOT NULL default '',
  `kommentar` varchar(255) NOT NULL default '',
  `geändert` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
  PRIMARY KEY  (`gruppe_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8  |
+-++
1 row in set (0.00 sec)


On Wed, 24 Aug 2005 20:55:21 +0300
Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> Hello.
> 
> Your character_set_xxx variables is ok. The problem could be in
> the table definition. Send the output of
>   SHOW CREATE TABLE tbl_gruppen;
> 
> Florian Burkart <[EMAIL PROTECTED]> wrote:
> > Hey everyone,
> > 
> > can't figure this one out, might be easy for one of you.
> > 
> > 
> > 
> > This is the query:
> > (SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
> > UNION ALL
> > (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
> > FROM tbl_gruppen
> > ORDER BY gruppe);
> > 
> > 
> > 
> > This error message is returned on the php website:
> > Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and 
> > (utf8_general_ci,IMPLICIT) for operation 'UNION'
> > 
> > 
> > 
> > There is no error message (query executes successfully) in phpmyadmin or 
> > when connecting by mysql.
> > 
> > 
> > 
> > Following query always executes successfully (no long term solution though):
> > (SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
> > UNION ALL
> > (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
> > FROM tbl_gruppen
> > ORDER BY gruppe);
> > 
> > 
> > 
> > This is the output of
> > mysql> show variables like "%character%"; show variables like "%collation%";
> > +--++
> > | Variable_name| Value  |
> > +--++
> > | character_set_client | utf8   |
> > | character_set_connection | utf8   |
> > | character_set_database   | utf8   |
> > | character_set_results| utf8   |
> > | character_set_server | utf8   |
> > | character_set_system | utf8   |
> > | character_sets_dir   | /usr/share/mysql/charsets/ |
> > +--++
> > 7 rows in set (0.00 sec)
> > 
> > +--+-+
> > | Variable_name| Value   |
> > +--+-+
> > | collation_connection | utf8_general_ci |
> > | collation_database   | utf8_general_ci |
> > | collation_server | utf8_general_ci |
> > +--+-+
> > 3 rows in set (0.00 sec)
> > 
> > 
> > 
> > This is my.cnf
> > ...
> > [mysql]
> > character-sets-dir=/usr/share/mysql/charsets
> > default-character-set=utf8
> > 
> > [mysqladmin]
> > 

Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Gleb Paharenko]

Hello.



Your character_set_xxx variables is ok. The problem could be in

the table definition. Send the output of

  SHOW CREATE TABLE tbl_gruppen;



Florian Burkart <[EMAIL PROTECTED]> wrote:

> Hey everyone,

> 

> can't figure this one out, might be easy for one of you.

> 

> 

> 

> This is the query:

> (SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)

> UNION ALL

> (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id

> FROM tbl_gruppen

> ORDER BY gruppe);

> 

> 

> 

> This error message is returned on the php website:

> Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and 
> (utf8_general_ci,IMPLICIT) for operation 'UNION'

> 

> 

> 

> There is no error message (query executes successfully) in phpmyadmin or when 
> connecting by mysql.

> 

> 

> 

> Following query always executes successfully (no long term solution though):

> (SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)

> UNION ALL

> (SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id

> FROM tbl_gruppen

> ORDER BY gruppe);

> 

> 

> 

> This is the output of

> mysql> show variables like "%character%"; show variables like "%collation%";

> +--++

> | Variable_name| Value  |

> +--++

> | character_set_client | utf8   |

> | character_set_connection | utf8   |

> | character_set_database   | utf8   |

> | character_set_results| utf8   |

> | character_set_server | utf8   |

> | character_set_system | utf8   |

> | character_sets_dir   | /usr/share/mysql/charsets/ |

> +--++

> 7 rows in set (0.00 sec)

> 

> +--+-+

> | Variable_name| Value   |

> +--+-+

> | collation_connection | utf8_general_ci |

> | collation_database   | utf8_general_ci |

> | collation_server | utf8_general_ci |

> +--+-+

> 3 rows in set (0.00 sec)

> 

> 

> 

> This is my.cnf

> ...

> [mysql]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [mysqladmin]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [mysqlcheck]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [mysqldump]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [mysqlimport]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [mysqlshow]

> character-sets-dir=/usr/share/mysql/charsets

> default-character-set=utf8

> 

> [myisamchk]

> character-sets-dir=/usr/share/mysql/charsets

> 

> [myisampack]

> character-sets-dir=/usr/share/mysql/charsets

> 

> # use [safe_mysqld] with mysql-3

> [mysqld_safe]

> err-log = /var/log/mysql/mysql.err

> 

> # add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.

> [mysqld]

> character-set-server= utf8

> default-character-set   = utf8

> ...

> 

> 

> 

> Probably easy, but help please :)

> Florian

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Florian Burkart]

Hey everyone,

can't figure this one out, might be easy for one of you.



This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);



This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and 
(utf8_general_ci,IMPLICIT) for operation 'UNION'



There is no error message (query executes successfully) in phpmyadmin or when 
connecting by mysql.



Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);



This is the output of
mysql> show variables like "%character%"; show variables like "%collation%";
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_results| utf8   |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)

+--+-+
| Variable_name| Value   |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)



This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set   = utf8
...



Probably easy, but help please :)
Florian

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

[Florian Burkart]

Hey everyone,

can't figure this one out, might be easy for one of you.



This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);



This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and 
(utf8_general_ci,IMPLICIT) for operation 'UNION'



There is no error message (query executes successfully) in phpmyadmin or when 
connecting by mysql.



Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);



This is the output of
mysql> show variables like "%character%"; show variables like "%collation%";
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_results| utf8   |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)

+--+-+
| Variable_name| Value   |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)



This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set   = utf8
...



Probably easy, but help please :)
Florian

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UNION - different result when statements interchanged

[Scott Noyes]

>From http://dev.mysql.com/doc/mysql/en/union.html :
"Before MySQL 4.1.1, a limitation of UNION  is that only the values
from the first SELECT are used to determine result column types and
lengths. This could result in value truncation if, for example, the
first SELECT retrieves shorter values than the second SELECT:"

You can either update to version 4.1.1 or later, when the problem was
solved, or make sure that the first SELECT retrieves columns that are
wide enough and character types.

On 8/5/05, Kapoor, Nishikant <[EMAIL PROTECTED]> wrote:
> I have a UNION whose statements when interchanged gives a different result. I 
> can understand the change in the order of the rows, but how is it that 
> 'picture' gets the correct value in (a) but not in (b)? Here are the two 
> queries and their results:
> 
> (a)
>   (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE 
> te.person_id = u.uId AND te.tId IN (1) LIMIT 5)
> UNION
>   (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE 
> te.person_id = a.person_id AND te.tId IN (1) LIMIT 5);
> 
> last_name   picture
> Kapoor  avataar02.png 
> Manni   0 
> Office of Technology Assessment 0 
> Queue Readers   0 
> Milnes  0 
> 
> (b)
>   (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE 
> te.person_id = a.person_id AND te.tId IN (1) LIMIT 5)
> UNION
>   (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE 
> te.person_id = u.uId AND te.tId IN (1) LIMIT 5);
> 
> last_name   picture
> Office of Technology Assessment 0  
> Queue Readers   0  
> Milnes  0  
> Kapoor  0  
> Manni   0  
> 
> Thanks for your help,
> Nishi

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UNION - different result when statements interchanged

[Kapoor, Nishikant]

I have a UNION whose statements when interchanged gives a different result. I 
can understand the change in the order of the rows, but how is it that 
'picture' gets the correct value in (a) but not in (b)? Here are the two 
queries and their results:

(a)
  (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE 
te.person_id = u.uId AND te.tId IN (1) LIMIT 5) 
UNION
  (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id 
= a.person_id AND te.tId IN (1) LIMIT 5);

last_name   picture
Kapoor  avataar02.png 
Manni   0 
Office of Technology Assessment 0 
Queue Readers   0 
Milnes  0 

(b)
  (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id 
= a.person_id AND te.tId IN (1) LIMIT 5)
UNION 
  (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE 
te.person_id = u.uId AND te.tId IN (1) LIMIT 5);

last_name   picture
Office of Technology Assessment 0  
Queue Readers   0  
Milnes  0  
Kapoor  0  
Manni   0  

Thanks for your help,
Nishi


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Speeding UNION with merging indexes

[Eli Hen]

You're right.. Your suggestion will speed it up.. but if you want to 
have LIMIT 1000,10 then you will have to retrieve 1010 rows from each 
sub-query then order the UNIONed rows and get the 10 rows you want. Here 
is just 2 sub-queries, but what if you got 10 UNIONed sub-queries.. and 
what if you use JOINs.. mabye merging the indexes will be faster..


If I knew how merging indexes is performed, mabye we could think of an 
actual algorithm to do this.. But, I believe the gurus already thought 
about this, so that's why I asked if this feature listed for the future 
releases?


Thanks :-)


[EMAIL PROTECTED] wrote:



I am not sure about index merging but you should be able to speed 
things up if you ORDER BY and LIMIT your inner queries as well:


(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5)
UNION
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5)
ORDER BY id LIMIT 0,5

To answer your UNION query, you only want 5 records in your results 
(the 5 with the lowest ID values). It doesn't matter if those 5 come 
from the first inner query or the second but you will use at most 5 
records from either. By reducing the number of records you need to 
UNION, you seriously decrease the processing required to achieve your 
outer goal. Granted, this is a rather special case but this is the 
second time this week that this pattern of query has appeared on the 
list so it can't be too special.


To get more abstract in analyzing an index merge process, can we 
discuss under which conditions it would be useful to merge indexes? 
 How would you describe the initial conditions of any UNION query that 
would describe a situation in which index merging would be of benefit 
to the execution of the query. I think it would be useful in more 
situations than when UNIONing the complete table query against two 
identical tables, if we can describe the decision in more general 
terms. Could you use index merging during JOINs or just UNIONs? What 
other types of queries could benefit from this being added to the 
query engine? Is it only useful in LIMIT queries?


Come on everyone, put on your thinking caps and work like DB 
developers for a bit and let's see if we can' t hash out a set of 
parameters that make sense. This sounds like a useful tool to add to 
the core engine but I think it needs a bit of thought about when to 
use it and when not to use it. What are your ideas?


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Eli Hen <[EMAIL PROTECTED]> wrote on 08/03/2005 04:44:55 PM:

> Hi,
>
> In the example you gave, it seems that MySQL doesn't merge the index of
> t1 in both sub-queries (which is the same index).. but it runs the
> sub-queries seperatedly, using the index on each sub-query seperatedly..
>
> Mabye I wasn't clear enough with my question.. let me phrase it again:
> Say I got 2 tables which are defined the same way but different names.
>
> mysql> SHOW CREATE TABLE t1 \G
> *** 1. row ***
>Table: t1
> Create Table: CREATE TABLE `t1` (
>   `id` int(11) NOT NULL auto_increment,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> mysql> SHOW CREATE TABLE t2 \G
> *** 1. row ***
>Table: t2
> Create Table: CREATE TABLE `t2` (
>   `id` int(11) NOT NULL,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
>
> mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2
> WHERE a='a') ORDER BY id LIMIT 0,5 \G
> *** 1. row ***
>id: 1
>   select_type: PRIMARY
> table: t1
>  type: ref
> possible_keys: a
>   key: a
>   key_len: 10
>   ref: const
>  rows: 1000
> Extra: Using where
> *** 2. row ***
>id: 2
>   select_type: UNION
> table: t2
>  type: ALL
> possible_keys: a
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 500
> Extra: Using where
> *** 3. row ***
>id: NULL
>   select_type: UNION RESULT
> table: 
>  type: ALL
> possible_keys: NULL
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: NULL
> Extra: Using filesort
> 3 rows in set (0.01 sec)
>
> MySQL executes each sub-query seperatedly (which returns 1000 and 500
> rows), then it combines them and use files

RE: Speeding UNION with merging indexes

[SGreen]

I am not sure about index merging but you should be able to speed things 
up if you ORDER BY and LIMIT your inner queries as well:

(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5) 
UNION 
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5) 
ORDER BY id LIMIT 0,5

To answer your UNION query, you only want 5 records in your results (the 5 
with the lowest ID values). It doesn't matter if those 5 come from the 
first inner query or the second but you will use at most 5 records from 
either. By reducing the number of records you need to UNION, you seriously 
decrease the processing required to achieve your outer goal. Granted, this 
is a rather special case but this is the second time this week that this 
pattern of query has appeared on the list so it can't be too special.

To get more abstract in analyzing an index merge process, can we discuss 
under which conditions it would be useful to merge indexes?  How would you 
describe the initial conditions of any UNION query that would describe a 
situation in which index merging would be of benefit to the execution of 
the query. I think it would be useful in more situations than when 
UNIONing the complete table query against two identical tables, if we can 
describe the decision in more general terms. Could you use index merging 
during JOINs or just UNIONs? What other types of queries could benefit 
from this being added to the query engine? Is it only useful in LIMIT 
queries?

Come on everyone, put on your thinking caps and work like DB developers 
for a bit and let's see if we can' t hash out a set of parameters that 
make sense. This sounds like a useful tool to add to the core engine but I 
think it needs a bit of thought about when to use it and when not to use 
it. What are your ideas?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Eli Hen <[EMAIL PROTECTED]> wrote on 08/03/2005 04:44:55 PM:

> Hi,
> 
> In the example you gave, it seems that MySQL doesn't merge the index of 
> t1 in both sub-queries (which is the same index).. but it runs the 
> sub-queries seperatedly, using the index on each sub-query seperatedly..
> 
> Mabye I wasn't clear enough with my question.. let me phrase it again:
> Say I got 2 tables which are defined the same way but different names.
> 
> mysql> SHOW CREATE TABLE t1 \G
> *** 1. row ***
>Table: t1
> Create Table: CREATE TABLE `t1` (
>   `id` int(11) NOT NULL auto_increment,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
> 
> mysql> SHOW CREATE TABLE t2 \G
> *** 1. row ***
>Table: t2
> Create Table: CREATE TABLE `t2` (
>   `id` int(11) NOT NULL,
>   `a` char(10) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `a` (`a`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
> 
> 
> mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2 
> WHERE a='a') ORDER BY id LIMIT 0,5 \G
> *** 1. row ***
>id: 1
>   select_type: PRIMARY
> table: t1
>  type: ref
> possible_keys: a
>   key: a
>   key_len: 10
>   ref: const
>  rows: 1000
> Extra: Using where
> *** 2. row ***
>id: 2
>   select_type: UNION
> table: t2
>  type: ALL
> possible_keys: a
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 500
> Extra: Using where
> *** 3. row ***
>id: NULL
>   select_type: UNION RESULT
> table: 
>  type: ALL
> possible_keys: NULL
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: NULL
> Extra: Using filesort
> 3 rows in set (0.01 sec)
> 
> MySQL executes each sub-query seperatedly (which returns 1000 and 500 
> rows), then it combines them and use filesort to order, then it limits 
> and give only the first 5 rows.
> This is very expensive for just 5 first rows that match the same WHERE 
> clause from 2 tables which have the SAME definition and indexes. It 
> might be possible to merge the indexes of t1 and t2 and speed up the 
> query... Is it possible? Does MySQL intend to do this?
> 
> [BTW: I gave just a private case. It might be examined to cover more 
cases.]
> 
> -thanks in advance
> 
> 
> >Hello.
> >
> >
> >MySQL uses indexes in queries which are parts of UNION. See:
> >
> >mysql> explain 

RE: Speeding UNION with merging indexes

[Eli Hen]

Hi,

In the example you gave, it seems that MySQL doesn't merge the index of 
t1 in both sub-queries (which is the same index).. but it runs the 
sub-queries seperatedly, using the index on each sub-query seperatedly..


Mabye I wasn't clear enough with my question.. let me phrase it again:
Say I got 2 tables which are defined the same way but different names.

mysql> SHOW CREATE TABLE t1 \G
*** 1. row ***
  Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL auto_increment,
 `a` char(10) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2 \G
*** 1. row ***
  Table: t2
Create Table: CREATE TABLE `t2` (
 `id` int(11) NOT NULL,
 `a` char(10) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2 
WHERE a='a') ORDER BY id LIMIT 0,5 \G

*** 1. row ***
  id: 1
 select_type: PRIMARY
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 10
 ref: const
rows: 1000
   Extra: Using where
*** 2. row *******
  id: 2
 select_type: UNION
   table: t2
type: ALL
possible_keys: a
 key: NULL
 key_len: NULL
 ref: NULL
rows: 500
   Extra: Using where
*** 3. row ***
  id: NULL
 select_type: UNION RESULT
   table: 
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: NULL
   Extra: Using filesort
3 rows in set (0.01 sec)

MySQL executes each sub-query seperatedly (which returns 1000 and 500 
rows), then it combines them and use filesort to order, then it limits 
and give only the first 5 rows.
This is very expensive for just 5 first rows that match the same WHERE 
clause from 2 tables which have the SAME definition and indexes. It 
might be possible to merge the indexes of t1 and t2 and speed up the 
query... Is it possible? Does MySQL intend to do this?


[BTW: I gave just a private case. It might be examined to cover more cases.]

-thanks in advance



Hello.


MySQL uses indexes in queries which are parts of UNION. See:

mysql> explain (select * from t1 where a=5) union (select * from t1 where 
a=3)\G;
*** 1. row ***
  id: 1
 select_type: PRIMARY
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 5
 ref: const
rows: 1
   Extra: Using where; Using index
*** 2. row ***
  id: 2
 select_type: UNION
   table: t1
type: ref
possible_keys: a
 key: a
 key_len: 5
 ref: const
rows: 1
   Extra: Using where; Using index
*** 3. row *******
  id: NULL
 select_type: UNION RESULT
   table: 
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: NULL
   Extra:
3 rows in set (0.00 sec)


mysql> show create table t1\G;
*** 1. row ***
  Table: t1
Create Table: CREATE TABLE `t1` (
 `a` int(11) default NULL,
 KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1




Hello,

MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION 
queries? If yes, for which version (assumed release time)?

Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system..

-thanks in advance.






--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@/stripped/
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
  <___/   www.mysql.com <http://www.mysql.com>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Speeding UNION with merging indexes

[Gleb Paharenko]

Hello.





MySQL uses indexes in queries which are parts of UNION. See:



mysql> explain (select * from t1 where a=5) union (select * from t1 where 
a=3)\G;

*** 1. row ***

   id: 1

  select_type: PRIMARY

table: t1

 type: ref

possible_keys: a

  key: a

  key_len: 5

  ref: const

 rows: 1

Extra: Using where; Using index

*** 2. row ***

   id: 2

  select_type: UNION

table: t1

 type: ref

possible_keys: a

  key: a

  key_len: 5

  ref: const

 rows: 1

Extra: Using where; Using index

*** 3. row ***

   id: NULL

  select_type: UNION RESULT

table: 

 type: ALL

possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

 rows: NULL

Extra:

3 rows in set (0.00 sec)





mysql> show create table t1\G;

*** 1. row ***

   Table: t1

Create Table: CREATE TABLE `t1` (

  `a` int(11) default NULL,

  KEY `a` (`a`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1







> Hello,

> 

> MySQL implemented index_merge in version 5...

> Does MySQL supports something like index_merge to speed up UNION 

> queries? If yes, for which version (assumed release time)?

> Does anyone know of other DBs systems that can speed up UNION queries?

> This issue is quite critical for our system..

> 

> -thanks in advance.

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Speeding UNION with merging indexes

[Eli Hen]

Hello,

MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION 
queries? If yes, for which version (assumed release time)?

Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system..

-thanks in advance.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UNION in JDBC - WAS Re: use of indexes

[Gleb Paharenko]

Hello.



Check with SHOW PROCESSLIST in which state MySQL thread which performs

the query is. See:

  http://dev.mysql.com/doc/mysql/en/show-processlist.html







Chris Faulkner <[EMAIL PROTECTED]> wrote:

> Hello again

> 

> Following on from this index question, the UNION worked. From a normal

> mysql client, it was returning my results sub-second. I am actually

> executing this over JDBC, using mysql-connector j.

> 

> WHen I put the SQL into my Java program - it takes a minute or so. I

> am logging the SQL and if I copy and paste it into my mysql client, it

> is fast. I can execute the query first in mysql and then in the JDBC

> client and I get the same so it is not caching. I've done a bit of

> searching but found nothing - any ideas ?

> 

> 

> Chris

> 

> On 7/22/05, Chris Faulkner <[EMAIL PROTECTED]> wrote:

>> That was exactly the problem. Thanks. MySQL can't use two indexes on

>> the same table at the same time. Thanks for the other suggestions but

>> I'll use this workaround.

>>=20

>> Chris

>>=20

>> On 7/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

>> > I believe the conflict here is the OR. Try this...

>> >

>> > select * from table

>> >   where field1 =3D 'VALUE1' and field2 like 'VALUE2%'

>> > union

>> > select * from table

>> >   where field3 =3D 'VALUE1' and field2 like 'VALUE2%'

>> >

>> > Currently, MySql can't utilize two indexes on the same table at

>> > the same time but it is on their list of to-do`s, this will be

>> > a cool feature. The UNION will allow you to use both composite

>> > indexes at the same time because it is two queries.

>> >

>> > Ed

>> >

>> > -Original Message-

>> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

>> > Sent: Friday, July 22, 2005 6:04 AM

>> > To: Chris Faulkner

>> > Cc: mysql@lists.mysql.com

>> > Subject: Re: use of indexes

>> >

>> > The system cannot used the index on field2 because it is the second hal=

> f

>> >

>> > of the index in both cases, and it can only use indexes in order. It

>> > cannot use the separate indexes on field 1 and field 2 because the are

>> > ORred together.

>> >

>> > If you rephrase your query

>> >

>> > SELECT * from table

>> >   WHERE field2 LIKE 'VALUE2%"

>> > AND ((field1 =3D 'VALUE1') OR (field3 =3D 'VALUE3')) ;

>> >

>> > it becomes obvious that an index on field2 will be used, followed by

>> > searches of the results field1 and field3 .

>> >

>> > As a matter of interest, what numbers of hits do you expect on each of

>> > the

>> > three terms separately? If the field2 hit is is pretty selective, it

>> > does

>> > not really matter what the others do.

>> >

>> > Alec

>> >

>> > Chris Faulkner <[EMAIL PROTECTED]>

>> > 22/07/2005 12:46

>> > Please respond to

>> > Chris Faulkner <[EMAIL PROTECTED]>

>> >

>> >

>> > To

>> > mysql@lists.mysql.com

>> > cc

>> >

>> > Subject

>> > Re: use of indexes

>> >

>> > Hi

>> >

>> > field2 is indexed. I have 2 indexes. One is on field1 and field2, the

>> > second indexes field3 and field2.

>> >

>> > You mean a separate index which only indexes field2 ? Ithought that

>> > the type of query I am doing is a good reason for doing composite

>> > indexes.

>> >

>> >

>> > Chris

>> >

>> > On 7/22/05, Eugene Kosov <[EMAIL PROTECTED]> wrote:

>> > > Chris Faulkner wrote:

>> > > > HI

>> > > >

>> > > > I have a query like this

>> > > >

>> > > > select * from table where (

>> > > > ( field1 =3D 'VALUE1' and field2 like 'VALUE2%' )

>> > > > OR

>> > > > ( field3 =3D 'VALUE1' and field2 like 'VALUE2%' )

>> > > > )

>> > > >

>> > > > I have created two composite indexes - one on field1 + field2 and

>> > one

>> > > > on field3 + field2. Explain on the SQL indicates that the indexes

>> > are

>> > > > possibly used. The query takes an age to run and looking at my log

>

UNION in JDBC - WAS Re: use of indexes

[Chris Faulkner]

Hello again

Following on from this index question, the UNION worked. From a normal
mysql client, it was returning my results sub-second. I am actually
executing this over JDBC, using mysql-connector j.

WHen I put the SQL into my Java program - it takes a minute or so. I
am logging the SQL and if I copy and paste it into my mysql client, it
is fast. I can execute the query first in mysql and then in the JDBC
client and I get the same so it is not caching. I've done a bit of
searching but found nothing - any ideas ?


Chris

On 7/22/05, Chris Faulkner <[EMAIL PROTECTED]> wrote:
> That was exactly the problem. Thanks. MySQL can't use two indexes on
> the same table at the same time. Thanks for the other suggestions but
> I'll use this workaround.
> 
> Chris
> 
> On 7/22/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > I believe the conflict here is the OR. Try this...
> >
> > select * from table
> >   where field1 = 'VALUE1' and field2 like 'VALUE2%'
> > union
> > select * from table
> >   where field3 = 'VALUE1' and field2 like 'VALUE2%'
> >
> > Currently, MySql can't utilize two indexes on the same table at
> > the same time but it is on their list of to-do`s, this will be
> > a cool feature. The UNION will allow you to use both composite
> > indexes at the same time because it is two queries.
> >
> > Ed
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Friday, July 22, 2005 6:04 AM
> > To: Chris Faulkner
> > Cc: mysql@lists.mysql.com
> > Subject: Re: use of indexes
> >
> > The system cannot used the index on field2 because it is the second half
> >
> > of the index in both cases, and it can only use indexes in order. It
> > cannot use the separate indexes on field 1 and field 2 because the are
> > ORred together.
> >
> > If you rephrase your query
> >
> > SELECT * from table
> >   WHERE field2 LIKE 'VALUE2%"
> > AND ((field1 = 'VALUE1') OR (field3 = 'VALUE3')) ;
> >
> > it becomes obvious that an index on field2 will be used, followed by
> > searches of the results field1 and field3 .
> >
> > As a matter of interest, what numbers of hits do you expect on each of
> > the
> > three terms separately? If the field2 hit is is pretty selective, it
> > does
> > not really matter what the others do.
> >
> > Alec
> >
> > Chris Faulkner <[EMAIL PROTECTED]>
> > 22/07/2005 12:46
> > Please respond to
> > Chris Faulkner <[EMAIL PROTECTED]>
> >
> >
> > To
> > mysql@lists.mysql.com
> > cc
> >
> > Subject
> > Re: use of indexes
> >
> > Hi
> >
> > field2 is indexed. I have 2 indexes. One is on field1 and field2, the
> > second indexes field3 and field2.
> >
> > You mean a separate index which only indexes field2 ? Ithought that
> > the type of query I am doing is a good reason for doing composite
> > indexes.
> >
> >
> > Chris
> >
> > On 7/22/05, Eugene Kosov <[EMAIL PROTECTED]> wrote:
> > > Chris Faulkner wrote:
> > > > HI
> > > >
> > > > I have a query like this
> > > >
> > > > select * from table where (
> > > > ( field1 = 'VALUE1' and field2 like 'VALUE2%' )
> > > > OR
> > > > ( field3 = 'VALUE1' and field2 like 'VALUE2%' )
> > > > )
> > > >
> > > > I have created two composite indexes - one on field1 + field2 and
> > one
> > > > on field3 + field2. Explain on the SQL indicates that the indexes
> > are
> > > > possibly used. The query takes an age to run and looking at my log
> > > > indicates a full table scan.
> > > >
> > > > I have also tried indexing just field1 and field3 separately but
> > this
> > > > doesn't help. I have run an analyze.
> > > >
> > > > Chris
> > > >
> > >
> > > Mysql use an index only if indexed field(s) present(s) in both OR
> > arguments..
> > > Sorry, but i can't find it in docs right now, so i can't give you any
> > helpful link.
> > >
> > > I think index on field2 may help you here..
> > >
> > >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Performance difference? : [SELECT ON Merge TableAB] vs [(SELECT ON Table A) UNION (SELECT ON TABLE B) ]

[Gleb Paharenko]

Hello.





I've searched in the archives at:

  http://lists.mysql.com/mysql



but the only thing I've found is that the speed difference is rather low. In my 
opinion, using UNION is a more flexible solution.









pow <[EMAIL PROTECTED]> wrote:

> Hi everyone,

> Take for example two identical tables A & B, and a MERGE table merging 

> both of them.

> Would there be any performance advantage if  I do a select (with a where 

> criteria on an indexed column) on the MERGE table, as opposed to doing a 

> union of two selects with the same WHERE criteria?

> 

> Many Thanks!

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Performance difference? : [SELECT ON Merge TableAB] vs [(SELECT ON Table A) UNION (SELECT ON TABLE B) ]

[pow]

Hi everyone,
Take for example two identical tables A & B, and a MERGE table merging 
both of them.
Would there be any performance advantage if  I do a select (with a where 
criteria on an indexed column) on the MERGE table, as opposed to doing a 
union of two selects with the same WHERE criteria?


Many Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: VARCHAR changes to INT - UNION

[Michael Stassen]

Eugene Kosov wrote:

Dennis Duggen wrote:


Hi list

Im new to the list so i hope it's the right place for my post.

For a projekt i am combining different tables. Everything seems to work,
but in some rows the "Kode" field VARCHAR(10) ends up as INT. I will try
to explain with an example.

Table1
46
47
48
67

Table2
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN

When i combine these i get

46
47
48
67
0
0
01
02

The desired result should be:

46
47
48
67
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN



According to:

http://dev.mysql.com/doc/mysql/en/union.html

coresponding columns of statements' results should have the same type.

Try to cnage order of SELECTs in your query.. I think you'll get what 
you want.


Right, the type of each column is determined by the first SELECT in the UNION. 
 Hence, your VARCHARs are converted to INTs because they are in an INT column 
based on the first SELECT.  You can fix this by making that column a string 
column of appropriate length.  Changing the order would work:


  INSERT INTO searchTemp
  (
SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline,
   Beskrivelse AS text, '', image_id FROM wesHoldKategori
  )
  UNION ALL
  (
SELECT 'content', id, headline, text, teaser, image_id FROM content
  );

Alternatively, you could keep the same order, but change the problem column to 
a string:


  INSERT INTO searchTemp
  (
SELECT 'content', CONCAT(id,'    '), headline, text, teaser, image_id
FROM content
  )
  UNION ALL
  (
SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline,
   Beskrivelse AS text, '', image_id FROM wesHoldKategori
  );

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: VARCHAR changes to INT - UNION

[Eugene Kosov]

Dennis Duggen wrote:

Hi list

Im new to the list so i hope it's the right place for my post.

For a projekt i am combining different tables. Everything seems to work,
but in some rows the "Kode" field VARCHAR(10) ends up as INT. I will try
to explain with an example.

Table1
46
47
48
67

Table2
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN

When i combine these i get

46
47
48
67
0
0
01
02

The desired result should be:

46
47
48
67
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN



According to:

http://dev.mysql.com/doc/mysql/en/union.html

coresponding columns of statements' results should have the same type.

Try to cnage order of SELECTs in your query.. I think you'll get what you want.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

VARCHAR changes to INT - UNION

[Dennis Duggen]

Hi list

Im new to the list so i hope it's the right place for my post.

For a projekt i am combining different tables. Everything seems to work,
but in some rows the "Kode" field VARCHAR(10) ends up as INT. I will try
to explain with an example.

Table1
46
47
48
67

Table2
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN

When i combine these i get

46
47
48
67
0
0
01
02

The desired result should be:

46
47
48
67
BBEGYNDER
BVILDIVAND
01ELITE
02SSKOLEN


== MySQL statements to create the combined table ==
===

DROP TABLE IF EXISTS searchTemp;

CREATE TABLE searchTemp (
`table` VARCHAR( 25 ) NOT NULL ,
`id` VARCHAR ( 10 ) NOT NULL ,
`headline` TEXT NOT NULL ,
`teaser` TEXT NOT NULL ,
`text` TEXT NOT NULL ,
`image_id` INT NOT NULL
);

ALTER TABLE
`searchTemp`
ADD FULLTEXT
(
`headline` ,
`teaser` ,
`text`
);

INSERT INTO searchTemp
(
SELECT 'content', id, headline, text, teaser, image_id FROM content
)
UNION ALL
(
SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline,
Beskrivelse AS text, '', image_id FROM wesHoldKategori
);


===

Anyone who can explain why this is happening and how i can solve it?

Thanks in advance

Dennis Duggen





--
Airconditioners and computers have one thing in common:
Once you open windows everything fails.
-- 
Firefox - Safer, Faster, Better
http://www.mozilla.org/products/firefox/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why would a UNION be 100x slower than the same SELECT...

[Kevin Burton]

Kevin Burton wrote:



Any idea whats going on and how I could fix this?


This seems like a bug in the SQL parser.  The LIMIT is only ignored in this one 
situation.

If I just add a:

UNION
(SELECT * FROM FOO LIMIT 0)

To the query will work correctly.

This might be an acceptable workaround

Kevin

--  



Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why would a UNION be 100x slower than the same SELECT...

[Rhino]

Why do you think you're using a UNION in your query? The keyword UNION
doesn't appear anywhere in your query. You don't even have a second query
being UNIONed to the first. All you've got is a pair of parentheses
surrounding your original query, which seems to perform okay.

For what it's worth, I don't see why a pair of parentheses would change the
performance but calling it a UNION just confuses the issue, in my view.

Rhino


- Original Message - 
From: "Kevin Burton" <[EMAIL PROTECTED]>
To: 
Sent: Monday, June 27, 2005 4:17 PM
Subject: Why would a UNION be 100x slower than the same SELECT...


> Here's a big problem I'm having.
>
> If I have a query like:
>
> SELECT * FROM FOO WHERE FOO.LAST_UPDATED < 1119898418779 AND
> FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10
>
> it only takes about 10ms or so to execute.
>
> but... if I rewrite it to wrap it in a union like so:
>
> ( SELECT * FROM FOO WHERE FOO.LAST_UPDATED
> < 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT
> 10 ) ORDER BY LAST_UPDATED DESC LIMIT 10
>
> then its 100x slower and takes about 1000ms
>
> No tmp disk tables were created (or at least thats what show status is
> telling me).
>
> Any idea whats going on and how I could fix this?
>
> Kevin
>
> -- 
>
>
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
> See irc.freenode.net #rojo if you want to chat.
>
> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
>
>Kevin A. Burton, Location - San Francisco, CA
>   AIM/YIM - sfburtonator,  Web - http://peerfear.org/
> GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why would a UNION be 100x slower than the same SELECT...

[Kevin Burton]

Kevin Burton wrote:



( SELECT * FROM FOO WHERE FOO.LAST_UPDATED
< 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT
10 ) ORDER BY LAST_UPDATED DESC LIMIT 10


OK.  I *totally* just figured it out!

WOW.

so.. the LIMIT in the first SELECT is *totally* ignored and the entire 
SQL expression is evaluated which is then given to the union. Evil I 
say! Pure evil!


I was able to figure this out because Handler_read_next was being 
incremented to the same value as the total number of rows in this 
expression.


Cool now at least I know why its screwing up.

Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Why would a UNION be 100x slower than the same SELECT...

[Kevin Burton]

Here's a big problem I'm having.

If I have a query like:

SELECT * FROM FOO WHERE FOO.LAST_UPDATED < 1119898418779 AND
FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10

it only takes about 10ms or so to execute.

but... if I rewrite it to wrap it in a union like so:

( SELECT * FROM FOO WHERE FOO.LAST_UPDATED
< 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT
10 ) ORDER BY LAST_UPDATED DESC LIMIT 10

then its 100x slower and takes about 1000ms

No tmp disk tables were created (or at least thats what show status is 
telling me).


Any idea whats going on and how I could fix this?

Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[mfatene]

Hi,
If we forget the first method which i mis-adviced, i can give a third which is
generic.

suppose that you have an indexed "type" column on each table (what i did). You
can work with 3 variables. If they are different, you query for a join, if they
are equal, you transform the join to a simple query.
The only condition is to add a where clause a the column "type" which will
retreive empty set for the non selected conditions.

Example :!

set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';

mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
->  union select id,@cat2 as selected, type from faq where [EMAIL 
PROTECTED]
-> union select id,@cat3 as selected, type from forum where [EMAIL 
PROTECTED];
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


When you have only one value, the same query gives :



mysql> set @cat1='news'; set @cat2='news'; set @cat3='news';
Query OK, 0 rows affected (0.00 sec)

here the 3 variables are the same, so 2 queries will find an empty set.

mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
    ->  union select id,@cat2 as selected, type from faq where [EMAIL 
PROTECTED]
-> union select id,@cat3 as selected, type from forum where [EMAIL 
PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)

performance will not be affected since the index will be used for non used
tables.

Hope that helps :o)

Mathias



Selon Sebastian <[EMAIL PROTECTED]>:

> Michael Stassen wrote:
>
> > [EMAIL PROTECTED] wrote:
> >
> >> Hi Sebastian;
> >> There is always crazy things somewhere.
> >> I'll give you two methods for that :
> >>
> >> mysql> select id,'news' as selected, type from news
> >> -> union select id,'faq' as selected, type from faq
> >> -> union select id,'forum' as selected, type from forum;
> >> +--+--+---+
> >> | id   | selected | type  |
> >> +--+--+---+
> >> |1 | news | news  |
> >> |2 | faq  | faq   |
> >> |3 | forum| forum |
> >> +--+--+---+
> >> 3 rows in set (0.00 sec)
> >>
> >>
> >> FIRST CRAZY METHOD :
> >> *
> >> mysql> set @cat='news';
> >> Query OK, 0 rows affected (0.00 sec)
> >>
> >> mysql> select * from (
> >> -> select id,'news' as selected, type from news
> >> -> union select id,'faq' as selected, type from faq
> >> -> union select id,'forum' as selected, type from forum
> >> -> ) Temp
> >> -> where [EMAIL PROTECTED];
> >> +--+--+--+
> >> | id   | selected | type |
> >> +--+--+--+
> >> |1 | news | news |
> >> +--+--+--+
> >> 1 row in set (0.00 sec)
> >>
> >>
> >> SECOND CRAZY METHOD (I prefer):
> >> *
> >>
> >> set @cat := 'news';
> >> set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> >> select @sql;
> >> prepare stmt from @sql ;
> >> execute stmt;
> >>
> >> +--+--+
> >> | id   | selected |
> >> +--+--+
> >> |1 | news |
> >> +--+--+
> >> 1 row in set (0.00 sec)
> >>
> >> deallocate prepare stmt;
> >>
> >>
> >> * another click with ?cat=faq
> >>
> >> set @cat := 'faq';
> >> set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> >> select @sql;
> >> prepare stmt from @sql ;
> >> execute stmt;
> >>
> >> mysql> execute stmt;
> >> +--+--+
> >> | id   | selected |
> >> +--+--+
> >> |2 | faq  |
> >> +--+--+
> >> 1 row in set (0.00 sec)
> >>
> >> deallocate prepare stmt;
> >>
> >> OTHER CRAZY METHODS 

Re: mysql UNION

[mfatene]

did'n arrive. re-submitted  -- sorry
Selon [EMAIL PROTECTED]:

> Hi,
> If we forget the first method which i mis-adviced, i can give a third which
> is
> generic.
>
> suppose that you have an indexed "type" column on each table (what i did).
> You
> can work with 3 variables. If they are different, you query for a join, if
> they
> are equal, you transform the join to a simple query.
> The only condition is to add a where clause a the column "type" which will
> retreive empty set for the non selected conditions.
>
> Example :!
>
> set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
>
> mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
> ->  union select id,@cat2 as selected, type from faq where [EMAIL 
> PROTECTED]
> -> union select id,@cat3 as selected, type from forum where
> [EMAIL PROTECTED];
> +--+--+---+
> | id   | selected | type  |
> +--+--+---+
> |1 | news | news  |
> |2 | faq  | faq   |
> |3 | forum| forum |
> +--+--+---+
> 3 rows in set (0.00 sec)
>
>
> When you have only one value, the same query gives :
> 
>
>
> mysql> set @cat1='news'; set @cat2='news'; set @cat3='news';
> Query OK, 0 rows affected (0.00 sec)
>
> here the 3 variables are the same, so 2 queries will find an empty set.
>
> mysql> select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
> ->  union select id,@cat2 as selected, type from faq where [EMAIL 
> PROTECTED]
> -> union select id,@cat3 as selected, type from forum where
> [EMAIL PROTECTED];
> +--+--+--+
> | id   | selected | type |
> +--+--+--+
> |1 | news | news |
> +--+--+--+
> 1 row in set (0.00 sec)
>
> performance will not be affected since the index will be used for non used
> tables.
>
> Hope that helps :o)
>
> Mathias
>
>
>
> Selon Sebastian <[EMAIL PROTECTED]>:
>
> > Michael Stassen wrote:
> >
> > > [EMAIL PROTECTED] wrote:
> > >
> > >> Hi Sebastian;
> > >> There is always crazy things somewhere.
> > >> I'll give you two methods for that :
> > >>
> > >> mysql> select id,'news' as selected, type from news
> > >> -> union select id,'faq' as selected, type from faq
> > >> -> union select id,'forum' as selected, type from forum;
> > >> +--+--+---+
> > >> | id   | selected | type  |
> > >> +--+--+---+
> > >> |1 | news | news  |
> > >> |2 | faq  | faq   |
> > >> |3 | forum| forum |
> > >> +--+--+---+
> > >> 3 rows in set (0.00 sec)
> > >>
> > >>
> > >> FIRST CRAZY METHOD :
> > >> *
> > >> mysql> set @cat='news';
> > >> Query OK, 0 rows affected (0.00 sec)
> > >>
> > >> mysql> select * from (
> > >> -> select id,'news' as selected, type from news
> > >> -> union select id,'faq' as selected, type from faq
> > >> -> union select id,'forum' as selected, type from forum
> > >> -> ) Temp
> > >> -> where [EMAIL PROTECTED];
> > >> +--+--+--+
> > >> | id   | selected | type |
> > >> +--+--+--+
> > >> |1 | news | news |
> > >> +--+--+--+
> > >> 1 row in set (0.00 sec)
> > >>
> > >>
> > >> SECOND CRAZY METHOD (I prefer):
> > >> *
> > >>
> > >> set @cat := 'news';
> > >> set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> > >> select @sql;
> > >> prepare stmt from @sql ;
> > >> execute stmt;
> > >>
> > >> +--+--+
> > >> | id   | selected |
> > >> +--+--+
> > >> |1 | news |
> > >> +--+--+
> > >> 1 row in set (0.00 sec)
> > >>
> > >> deallocate prepare stmt;
> > >>
> > >>
> > >> * another click with 

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat 
clause be present first in the where clause? since it decides if it 
should get results or not. for example:


WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND ('$cat' = 'news' OR 
@cat = '')


or:

WHERE ('$cat' = 'news' OR '$cat' = '') AND MATCH(...) AGAINST(... IN 
BOOLEAN MODE)


does it matter in terms of performance?


I'm not sure.  In every case, the $cat clause is either impossible or 
certain, so it can be optimized away (taking the whole query with it in 
the impossible case).  Hence, there will be no difference in the 
processing of tables once the execution plan has been chosen.  The only 
possible difference would be determined by how much time the optimizer 
wastes on other parts of the WHERE clause before noticing an impossible 
$cat part.  I don't know precisely how the optimizer does this, so I 
don't know if order makes a difference, nor which order would be best if 
it does.  You could try it both ways, but I doubt you'd detect any 
difference in a single query.  If I had to guess, though, I'd guess that 
if it makes any difference, then you are right that first is best.


Also, for completeness, note that if there is any more to your WHERE 
clause than just the MATCH AGAINST, you may need to use parentheses 
around the entire rest of the WHERE clause, aside from the $cat part. 
For example, AND is higher precedence than OR, so


  WHERE ('$cat' = 'news' OR '$cat' = '') AND cond1 OR cond2

wouldn't work the way you want.  To guard against that, I'd suggest 
always adding the parentheses around the rest of the conditions:


  WHERE ('$cat' = 'news' OR '$cat' = '') AND (other conditions)

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


Sebastian wrote:


Michael Stassen wrote:




Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
      WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"





Michael



you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 UNION
  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.



That was the point.  The query I gave, quoted above, works whether 
$cat is set or not (though I forgot to quote $cat in the comparisons 
-- sorry).  No need to modify it to use a mysql user variable, either. 
Taking into account the news that your real query is more complicated, 
this would look something like:


  $sql = "SELECT id, 'news' AS type, ..other_columns.. FROM news
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'news')
UNION
  SELECT id, 'faq' AS type, ..other_columns.. FROM faq
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'faq')
UNION
  SELECT id, 'forum' AS type, ..other_columns.. FROM forum
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'forum')";

Michael



Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat 
clause be present first in the where clause? since it decides if it 
should get results or not. for example:


WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND ('$cat' = 'news' OR 
@cat = '')


or:

WHERE ('$cat' = 'news' OR @cat = '') AND MATCH(...) AGAINST(... IN 
BOOLEAN MODE)


does it matter in terms of performance?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:



Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
      WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"




Michael


you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 UNION
  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.


That was the point.  The query I gave, quoted above, works whether $cat 
is set or not (though I forgot to quote $cat in the comparisons -- 
sorry).  No need to modify it to use a mysql user variable, either. 
Taking into account the news that your real query is more complicated, 
this would look something like:


  $sql = "SELECT id, 'news' AS type, ..other_columns.. FROM news
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'news')
UNION
  SELECT id, 'faq' AS type, ..other_columns.. FROM faq
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'faq')
UNION
  SELECT id, 'forum' AS type, ..other_columns.. FROM forum
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'forum')";

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:


So, why spend days trying to come up with complicated, ugly sql when 
it can be done in 5 minutes with simple, easy-to-read php?


Michael

i only posted snip of the query.. it is much larger and complex than 
just a SELECT. i am going MATCH, AGAINST, LEFT JOINS, and other 
operations for a search engine. doing it in the app (php in my case)


Well, that's the first problem.  When you ask about a simplified version 
of your problem, you run the risk of getting answers which apply to your 
simplified problem rather than your real problem.  Unless you are 
certain that answers to the simplified version will be generally 
applicable (apparently not the case here), you should post your real query.


would mean i would end up with a huge block of SQL code which would seem 
more ugly IMO..  


I don't see it.  Your basic query is longer and more complicated than 
you've shown, but you still have the same problem.  You need one query 
if $cat is set, 3 unioned queries if $cat is not.


.. on top of that i am paginating the results which would 
mean i would have to duplicate the code you posted above since there 
would be no point in selecting many other columns just to get a row 
count for the pagination part so i couldn't re-use the IF statement 
output as shown above.


But don't you have this problem anyway?  You need a pagination query and 
a full query, each of which is one version of the long, complicated 
query you describe, with "MATCH, AGAINST, LEFT JOINS, and other 
operations", or the other.  If the only difference is the list of 
columns selected, it shouldn't be too hard to put that in a variable and 
reuse the if..else.


Of course, ugliness, like beauty, is in the eye of the beholder. 
Perhaps you'll prefer the one-statement-in-sql version I sent in my 
first reply to Mathias' suggestions.  It's just a matter of adding 2 
conditions to each WHERE clause in the UNION, so I think it will meet 
your specs, even for your real query.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


[EMAIL PROTECTED] wrote:


Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :

mysql> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum;
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


FIRST CRAZY METHOD :
*
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (
-> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum
-> ) Temp
-> where [EMAIL PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)


SECOND CRAZY METHOD (I prefer):
*

set @cat := 'news';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

+--+--+
| id   | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;


* another click with ?cat=faq

set @cat := 'faq';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

mysql> execute stmt;
+--+--+
| id   | selected |
+--+--+
|2 | faq  |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;

OTHER CRAZY METHODS - coming emails :o)


A+
Mathias



The first method is horribly inefficient (and requires mysql 4.1+).  
It reads all 3 tables, unions the resulting rows, checks for (and 
removes) duplicate rows, then finally throws away roughly 2/3 of the 
results (the rows from the 2 unwanted tables.  Compare that to the 
simple query which only addresses the 1 desired table.  Mathias is 
aware of this, which is why he gives the second method.  It creates 
the simple, one-table query using the value of $cat to choose which 
table.


The big problem here is that neither of these methods actually do what 
you asked for.  That is, neither works if $cat is not set.  With both 
methods, you will get no rows unless $cat is set.  In fact, the second 
method will give a syntax eror, as there will be no table name in the 
FROM clause.


Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"

MySQL's optimizer should be smart enough to notice that if $cat is 
set, 2 of the 3 unioned queries will have impossible WHERE clauses, so 
those queries will be skipped, making this relatively efficient.


Even though this works, though, I think it's a bit of a hack.  It's 
clever SQL which allows two separate queries to masquerade as one, 
based on the value of $cat.  I really think the simple, separate query 
solution I sent in my previous message


  if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
  {
$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
  }
  else
  {
$sql = " SELECT id, 'news' AS type,  FROM news
UNION
 SELECT id, 'faq' AS type,  FROM faq
UNION
 SELECT id, 'forum' AS type,  FROM forum";
  }

is better, as it is clearer what we are doing (one query if we know 
the table, three queries unioned if we don't), so it should be easier 
to understand and maintain down the road (by you or the next guy).


Michael


you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 
UNION

  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Michael Stassen]

[EMAIL PROTECTED] wrote:


I agree with you,
just see that if ..
sql = "SELECT id, '$cat' AS type, ... FROM $cat";
is exactly what is done in prepare statement.


Yes, I acknowledged that in my next message (a reply to yours).


dynamic sql is better than application level statement preparation, when you use
stored procedure. but since this is the habit in mysql, i'll keep this for
oracle, and other rdbms.


It's not a question of habit.  Stored procedures don't exist in mysql 
until 5.0, which is still in beta and not for production use.  We should 
not assume that people use 5.0 unless they explicitly say they do.  If 
we don't assume 5.0, this point is moot.



P.S. haven't spent days to help. It is easy. And since it was a week-end :o)


Right.  That was a reference to the original poster's statement, "i am 
stuck on this for a few days already."



Mathias


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Michael Stassen]

[EMAIL PROTECTED] wrote:


Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :

mysql> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum;
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


FIRST CRAZY METHOD :
*
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (
-> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum
-> ) Temp
-> where [EMAIL PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)


SECOND CRAZY METHOD (I prefer):
*

set @cat := 'news';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

+--+--+
| id   | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;


* another click with ?cat=faq

set @cat := 'faq';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

mysql> execute stmt;
+--+--+
| id   | selected |
+--+--+
|2 | faq  |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;

OTHER CRAZY METHODS - coming emails :o)


A+
Mathias


The first method is horribly inefficient (and requires mysql 4.1+).  It 
reads all 3 tables, unions the resulting rows, checks for (and removes) 
duplicate rows, then finally throws away roughly 2/3 of the results (the 
rows from the 2 unwanted tables.  Compare that to the simple query which 
only addresses the 1 desired table.  Mathias is aware of this, which is 
why he gives the second method.  It creates the simple, one-table query 
using the value of $cat to choose which table.


The big problem here is that neither of these methods actually do what 
you asked for.  That is, neither works if $cat is not set.  With both 
methods, you will get no rows unless $cat is set.  In fact, the second 
method will give a syntax eror, as there will be no table name in the 
FROM clause.


Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"

MySQL's optimizer should be smart enough to notice that if $cat is set, 
2 of the 3 unioned queries will have impossible WHERE clauses, so those 
queries will be skipped, making this relatively efficient.


Even though this works, though, I think it's a bit of a hack.  It's 
clever SQL which allows two separate queries to masquerade as one, based 
on the value of $cat.  I really think the simple, separate query 
solution I sent in my previous message


  if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
  {
$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
  }
  else
  {
$sql = " SELECT id, 'news' AS type,  FROM news
UNION
 SELECT id, 'faq' AS type,  FROM faq
UNION
 SELECT id, 'forum' AS type,  FROM forum";
  }

is better, as it is clearer what we are doing (one query if we know the 
table, three queries unioned if we don't), so it should be easier to 
understand and maintain down the road (by you or the next guy).


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


Sebastian wrote:


Michael Stassen wrote:


Sebastian wrote:


i have a query with 3 union selects:

   SELECT id, 'news' AS type,  FROM news

   UNION
 SELECT id, 'faq' AS type, .... FROM faq

   UNION

   SELECT id, 'forum' AS type,  FROM forum

which works just fine and selects everything from all 3 tables.. 
but say i want to make a condition to only select from either 'faq' 
, 'news' or 'forum' how can i do this?


example, if a user visits a link suck as: page.php?cat=faq it will 
only select from 'faq' .. is this possible to do right in the 
query? when there is no ?cat= then all three selects run.


makes sense? i am stuck on this for a few days already.
thanks.



Why don't you do this in your app?  If cat is set, issue the 
appropriate single-table query, otherwise issue the union.  Surely 
that would be simpler than trying to build one multi-purpose query.


Michael



I was hoping i could do some crazy thing like WHERE type = 'faq' so i 
can do it all from one block of code.



Frankly, I don't see the benefit of this.  What is the payoff?  php 
was designed for this sort of conditional execution, sql wasn't.  
You've spent several days trying to find a way to do this in sql.  How 
long would it have taken you to write the if...else... statement in 
php? Let's see:


if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
  $sql = "SELECT id, '$cat' AS type, ... FROM $cat";
}
else
{
  $sql = " SELECT id, 'news' AS type,  FROM news
  UNION
   SELECT id, 'faq' AS type,  FROM faq
  UNION
   SELECT id, 'forum' AS type,  FROM forum";
}

So, why spend days trying to come up with complicated, ugly sql when 
it can be done in 5 minutes with simple, easy-to-read php?


Michael



i only posted snip of the query.. it is much larger and complex than 
just a SELECT. i am going MATCH, AGAINST, LEFT JOINS, and other 
operations for a search engine. doing it in the app (php in my case) 
would mean i would end up with a huge block of SQL code which would seem 
more ugly IMO.. on top of that i am paginating the results which would 
mean i would have to duplicate the code you posted above since there 
would be no point in selecting many other columns just to get a row 
count for a the pagination part so i couldn't re-use the IF statement 
output as shown above.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[mfatene]

I agree with you,
just see that if ..
sql = "SELECT id, '$cat' AS type, ... FROM $cat";
is exactly what is done in prepare statement.

dynamic sql is better than application level statement preparation, when you use
stored procedure. but since this is the a habit in mysql, i'll keep this for
oracle, and other rdbms.

P.S. haven't spent days to help. It is easy. And since it was a week-end :o)

Mathias

Selon Michael Stassen <[EMAIL PROTECTED]>:

> Sebastian wrote:
>
> > Michael Stassen wrote:
> >
> >> Sebastian wrote:
> >>
> >>> i have a query with 3 union selects:
> >>>
> >>>SELECT id, 'news' AS type,  FROM news
> >>>
> >>>UNION
> >>>  SELECT id, 'faq' AS type,  FROM faq
> >>>
> >>>UNION
> >>>
> >>>SELECT id, 'forum' AS type,  FROM forum
> >>>
> >>> which works just fine and selects everything from all 3 tables.. but
> >>> say i want to make a condition to only select from either 'faq' ,
> >>> 'news' or 'forum' how can i do this?
> >>>
> >>> example, if a user visits a link suck as: page.php?cat=faq it will
> >>> only select from 'faq' .. is this possible to do right in the query?
> >>> when there is no ?cat= then all three selects run.
> >>>
> >>> makes sense? i am stuck on this for a few days already.
> >>> thanks.
> >>>
> >>
> >> Why don't you do this in your app?  If cat is set, issue the
> >> appropriate single-table query, otherwise issue the union.  Surely
> >> that would be simpler than trying to build one multi-purpose query.
> >>
> >> Michael
> >
> > I was hoping i could do some crazy thing like WHERE type = 'faq' so i
> > can do it all from one block of code.
>
> Frankly, I don't see the benefit of this.  What is the payoff?  php was
> designed for this sort of conditional execution, sql wasn't.  You've
> spent several days trying to find a way to do this in sql.  How long
> would it have taken you to write the if...else... statement in php?
> Let's see:
>
> if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
> {
>$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
> }
> else
> {
>$sql = " SELECT id, 'news' AS type,  FROM news
>UNION
> SELECT id, 'faq' AS type,  FROM faq
>UNION
> SELECT id, 'forum' AS type,  FROM forum";
> }
>
> So, why spend days trying to come up with complicated, ugly sql when it
> can be done in 5 minutes with simple, easy-to-read php?
>
> Michael
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:


Sebastian wrote:


i have a query with 3 union selects:

   SELECT id, 'news' AS type,  FROM news

   UNION
 SELECT id, 'faq' AS type, .... FROM faq

   UNION

   SELECT id, 'forum' AS type,  FROM forum

which works just fine and selects everything from all 3 tables.. but 
say i want to make a condition to only select from either 'faq' , 
'news' or 'forum' how can i do this?


example, if a user visits a link suck as: page.php?cat=faq it will 
only select from 'faq' .. is this possible to do right in the query? 
when there is no ?cat= then all three selects run.


makes sense? i am stuck on this for a few days already.
thanks.



Why don't you do this in your app?  If cat is set, issue the 
appropriate single-table query, otherwise issue the union.  Surely 
that would be simpler than trying to build one multi-purpose query.


Michael


I was hoping i could do some crazy thing like WHERE type = 'faq' so i 
can do it all from one block of code.


Frankly, I don't see the benefit of this.  What is the payoff?  php was 
designed for this sort of conditional execution, sql wasn't.  You've 
spent several days trying to find a way to do this in sql.  How long 
would it have taken you to write the if...else... statement in php? 
Let's see:


if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
  $sql = "SELECT id, '$cat' AS type, ... FROM $cat";
}
else
{
  $sql = " SELECT id, 'news' AS type,  FROM news
  UNION
   SELECT id, 'faq' AS type,  FROM faq
  UNION
   SELECT id, 'forum' AS type,  FROM forum";
}

So, why spend days trying to come up with complicated, ugly sql when it 
can be done in 5 minutes with simple, easy-to-read php?


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[mfatene]

The second method is dynamic sql with prepare statement from string.

It's better for the reason that the query is generated to retrieve data from
just one table (not an union which implies 3 tables).

The day your tables will be huge, i'm sure you will use the second method.

two crazy people can find a method because only one hand can't aplaude !

Mathias

Selon Sebastian <[EMAIL PROTECTED]>:

> Hi, your second method is probably a little too confusing (advanced) for
> me to understand.
> I used your first method which works fine.. thanks for the crazy stuff,
> somtimes you need two crazy people to come up with a solution ;)
>
> [EMAIL PROTECTED] wrote:
>
> >Hi Sebastian;
> >There is always crazy things somewhere.
> >I'll give you two methods for that :
> >
> >mysql> select id,'news' as selected, type from news
> >-> union select id,'faq' as selected, type from faq
> >-> union select id,'forum' as selected, type from forum;
> >+--+--+---+
> >| id   | selected | type  |
> >+--+--+---+
> >|1 | news | news  |
> >|2 | faq  | faq   |
> >|3 | forum| forum |
> >+--+--+---+
> >3 rows in set (0.00 sec)
> >
> >
> >FIRST CRAZY METHOD :
> >*
> >mysql> set @cat='news';
> >Query OK, 0 rows affected (0.00 sec)
> >
> >mysql> select * from (
> >-> select id,'news' as selected, type from news
> >-> union select id,'faq' as selected, type from faq
> >-> union select id,'forum' as selected, type from forum
> >-> ) Temp
> >-> where [EMAIL PROTECTED];
> >+--+--+--+
> >| id   | selected | type |
> >+--+--+--+
> >|1 | news | news |
> >+--+--+--+
> >1 row in set (0.00 sec)
> >
> >
> >SECOND CRAZY METHOD (I prefer):
> >*
> >
> >
> >set @cat := 'news';
> >set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> >select @sql;
> >prepare stmt from @sql ;
> >execute stmt;
> >
> >+--+--+
> >| id   | selected |
> >+--+--+
> >|1 | news |
> >+--+--+
> >1 row in set (0.00 sec)
> >
> >deallocate prepare stmt;
> >
> >
> >* another click with ?cat=faq
> >
> >set @cat := 'faq';
> >set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
> >select @sql;
> >prepare stmt from @sql ;
> >execute stmt;
> >
> >mysql> execute stmt;
> >+--+--+
> >| id   | selected |
> >+--+--+
> >|2 | faq  |
> >+--+--+
> >1 row in set (0.00 sec)
> >
> >deallocate prepare stmt;
> >
> >
> >
> >
> >OTHER CRAZY METHODS - coming emails :o)
> >
> >
> >
> >A+
> >Mathias
> >
> >
> >
> >Selon Sebastian <[EMAIL PROTECTED]>:
> >
> >
> >
> >>Michael Stassen wrote:
> >>
> >>
> >>
> >>>Sebastian wrote:
> >>>
> >>>
> >>>
> >>>>i have a query with 3 union selects:
> >>>>
> >>>>   SELECT id, 'news' AS type,  FROM news
> >>>>
> >>>>   UNION
> >>>> SELECT id, 'faq' AS type,  FROM faq
> >>>>
> >>>>   UNION
> >>>>
> >>>>   SELECT id, 'forum' AS type,  FROM forum
> >>>>
> >>>>which works just fine and selects everything from all 3 tables.. but
> >>>>say i want to make a condition to only select from either 'faq' ,
> >>>>'news' or 'forum' how can i do this?
> >>>>
> >>>>example, if a user visits a link suck as: page.php?cat=faq it will
> >>>>only select from 'faq' .. is this possible to do right in the query?
> >>>>when there is no ?cat= then all three selects run.
> >>>>
> >>>>makes sense? i am stuck on this for a few days already.
> >>>>thanks.
> >>>>
> >>>>
> >>>>
> >>>Why don't you do this in your app?  If cat is set, issue the
> >>>appropriate single-table query, otherwise issue the union.  Surely
> >>>that would be simpler than trying to build one multi-purpose query.
> >>>
> >>>Michael
> >>>
> >>>
> >>I was hoping i could do some crazy thing like WHERE type = 'faq' so i
> >>can do it all from one block of code.
> >>
> >>
> >>
> >>
> >>
> >>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql UNION

[Sebastian]

Hi, your second method is probably a little too confusing (advanced) for 
me to understand.
I used your first method which works fine.. thanks for the crazy stuff, 
somtimes you need two crazy people to come up with a solution ;)


[EMAIL PROTECTED] wrote:


Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :

mysql> select id,'news' as selected, type from news
   -> union select id,'faq' as selected, type from faq
   -> union select id,'forum' as selected, type from forum;
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


FIRST CRAZY METHOD :
*
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (
   -> select id,'news' as selected, type from news
   -> union select id,'faq' as selected, type from faq
   -> union select id,'forum' as selected, type from forum
   -> ) Temp
   -> where [EMAIL PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)


SECOND CRAZY METHOD (I prefer):
*


set @cat := 'news';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

+--+--+
| id   | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;


* another click with ?cat=faq

set @cat := 'faq';
set @sql:=concat('select id,','''',@cat,'''',' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

mysql> execute stmt;
+--+--+
| id   | selected |
+--+--+
|2 | faq  |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;




OTHER CRAZY METHODS - coming emails :o)



A+
Mathias



Selon Sebastian <[EMAIL PROTECTED]>:

 


Michael Stassen wrote:

   


Sebastian wrote:

 


i have a query with 3 union selects:

  SELECT id, 'news' AS type,  FROM news

  UNION
SELECT id, 'faq' AS type,  FROM faq

  UNION

  SELECT id, 'forum' AS type,  FROM forum

which works just fine and selects everything from all 3 tables.. but
say i want to make a condition to only select from either 'faq' ,
'news' or 'forum' how can i do this?

example, if a user visits a link suck as: page.php?cat=faq it will
only select from 'faq' .. is this possible to do right in the query?
when there is no ?cat= then all three selects run.

makes sense? i am stuck on this for a few days already.
thanks.

   


Why don't you do this in your app?  If cat is set, issue the
appropriate single-table query, otherwise issue the union.  Surely
that would be simpler than trying to build one multi-purpose query.

Michael
 


I was hoping i could do some crazy thing like WHERE type = 'faq' so i
can do it all from one block of code.




   



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]