UNION Query

[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