Andy Jefferson <[EMAIL PROTECTED]> wrote: > > I'm trying to do a simple UNION in MySQL (4.0.11a on Linux). I am trying the > following ... > > If I do > SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Product" AS > JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON > THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND > THIS.PRICE < .15E3 > i get > +------------+--------------------------------------+-------+ > | PRODUCT_ID | JPOXMETADATA | ID | > +------------+--------------------------------------+-------+ > | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | > +------------+--------------------------------------+-------+ > > If I also do > SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Products.Book" AS > JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON > THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE < .15E3; > i get > +------------+--------------------------------------------+-------+ > | PRODUCT_ID | JPOXMETADATA | ID | > +------------+--------------------------------------------+-------+ > | 1 | net.ajsoft.WebShop.Inventory.Products.Book | P_003 | > | 2 | net.ajsoft.WebShop.Inventory.Products.Book | P_004 | > +------------+--------------------------------------------+-------+ > > Yet when I do > SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Product" AS > JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON > THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND > THIS.PRICE < .15E3 > UNION > SELECT THIS.PRODUCT_ID,"net.ajsoft.WebShop.Inventory.Products.Book" AS > JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON > THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE < .15E3; > i get > +------------+--------------------------------------+-------+ > | PRODUCT_ID | JPOXMETADATA | ID | > +------------+--------------------------------------+-------+ > | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | > | 1 | net.ajsoft.WebShop.Inventory.Product | P_003 | > | 2 | net.ajsoft.WebShop.Inventory.Product | P_004 | > +------------+--------------------------------------+-------+ > > Notice that the JPOXMETADATA column is incorrect for PRODUCT_ID=1 and 2.
JPOXMETADATA in the first SELECT is shorter than in the second SELECT. So, MySQL just truncate "net.ajsoft.WebShop.Inventory.Products.Book", because type of JPOXMETADATA is defined from the first query in the UNION. Swap the queries for correct result. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
