UNION Query

[nero]

Hi,

I have a little problem found in MySQL ???

Using:

SQLyog v3.03

MySQL 4.0.12-max-debug

windows98SE

I made the next query (long one):

SELECT 'db' as obj

, `db`.`host` as host

, `db`.`user` as user

, `db`.`db` as db

, 'xxxxxxxxxx' as tbl

, 'xx' as col

FROM `db`

WHERE `db`.`user` like '%' AND

( `db`.`Select_priv` ='y'

OR `db`.`Insert_priv` ='y'

OR `db`.`Update_priv` ='y'

OR `db`.`Delete_priv` ='y'

OR `db`.`Create_priv` ='y'

OR `db`.`Drop_priv` ='y'

OR `db`.`Grant_priv` ='y'

OR `db`.`References_priv`='y'

OR `db`.`Index_priv` ='y'

OR `db`.`Alter_priv` ='y')

UNION

SELECT 'tb' as obj

, `tables_priv`.`host` as host

, `tables_priv`.`user` as user

, `tables_priv`.`db` as db

, `tables_priv`.`Table_name` as tbl

, '—-' as col

FROM `tables_priv`

WHERE `tables_priv`.`user` like '%' AND

( `tables_priv`.`Table_priv` <>''

OR `tables_priv`.`Column_priv` <>''

)

UNION

SELECT 'cl' as obj

, `columns_priv`.`host` as host

, `columns_priv`.`user` as user

, `columns_priv`.`db` as db

, `columns_priv`.`Table_name` as tbl

, `columns_priv`.`Column_name` as col

FROM `columns_priv`

WHERE `columns_priv`.`user` like '%' AND

(

`columns_priv`.`Column_priv` <>''

)

From the time I worked with M$access I know the results from every

query should return the same amount of columns. MySQL want it to.

So i add to the first SELECT two dummy with xx as filler and it's working

but in first try i did not see the field contents were truncated.

Now the suprise, I thought it could be also a nice emty field.

After removing the xx as filler the columns were empty in rows

that have a content in that column.

Add one x as filler and the columns were only 1 char width.

Now i found that the first query determine the width (characters) for

all following rows in the following SELECT queries by UNION results.

M$Access gives the required result and the fields are not truncated.

Is this a bug in usage of UNION with MySQL???

For this one I can set a filler length for the dummy's because i know

how long the fields can/are but when i use it to make a overall report

on current running workflow table and with the UNION the old tables

it's not easy to know how big the filler must be.

BTW, this query is not useless because you can get a report for the user

where he has privileges something to do. With SQLyog user management

you have to select every database to see the tables and every table

to see the columns to check if he has some privilege.

This query will show you this in a glance if you finish the user filter

the propper way.

Any idee if i doing somthing wrong with this query or is it a little bug??

tia Nero

[lierduh]

This is one of the two surprises I have found with UNION under MYSQL.

I fixed this by updating to a newer version of MYSQL (4.0.12 non-max, noticed you are using the same version). Yes, I believe there are some bugs relating to UNION SELECT, seeing this has just been implemented not long ago, it does not surprise me at all. Well at least we can use UNION! 🙂

[nero]

Wel try some other way's give me this as result

I found it related to missing UNION in MySQL v3 as a solution.

The idee is simple, make a table with only 1 field in it

Enter in the field a ascending number as much of tables you want to use in a UNION.

Make the pseudo UNION like this:

select d.num

, ifnull(db.host,ifnull(tables_priv.host,columns_priv.host)) as hhost

, ifnull(db.user,ifnull(tables_priv.user,columns_priv.user)) as huser

, ifnull(db.db, ifnull(tables_priv.db, columns_priv.db)) as hdb

, ifnull( ifnull( tables_priv.Table_name, columns_priv.Table_name ) , 'x') as htbl

, ifnull( ifnull( ifnull( columns_priv.Column_name,'y' ) ,'z' ) ,'w' ) as hcol

FROM dummy as d

left join db on (d.num=0 and

`db`.`user` like '%' AND

( `db`.`Select_priv` ='y'

OR `db`.`Insert_priv` ='y'

OR `db`.`Update_priv` ='y'

OR `db`.`Delete_priv` ='y'

OR `db`.`Create_priv` ='y'

OR `db`.`Drop_priv` ='y'

OR `db`.`Grant_priv` ='y'

OR `db`.`References_priv`='y'

OR `db`.`Index_priv` ='y'

OR `db`.`Alter_priv` ='y')

)

left join tables_priv on (d.num=1 and

`tables_priv`.`user` like '%' AND

( `tables_priv`.`Table_priv` <>''

OR `tables_priv`.`Column_priv` <>''

)

)

left join columns_priv on (d.num=2 and

`columns_priv`.`user` like '%' AND

(

`columns_priv`.`Column_priv` <>''

)

)

where d.num<3;

For each loop on the dummy table you get a new where clause and that will do the UNION of the tables.

It is not my creation how this is working but it works very well.

And I believe it has some nice possibilties in advance of the UNION.

Nero is happy :ph34r: 😀

[Author]

Posts