It's actually mentioned in the user comments in the online manual (was there after the other reply looking for MINUS support),
that in a union, in any column that is a literal, the top most query defines the datatype (non-literals obviously use the column type) for the column. I agree it's not expected behavior, but it is documented (classical 'works as designed, but you may not like the design' scenerio).... > -----Original Message----- > From: Andy Jefferson [mailto:[EMAIL PROTECTED] > Sent: Friday, September 12, 2003 6:19 AM > To: [EMAIL PROTECTED] > Subject: Re: UNION > > > >> 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. > > Thanks, thats a workaround and I can move on but certainly is > NOT accepted > behaviour in RDBMS. > > Is there a plan for fixing this ? i.e Is there a list of > known bugs with > MySQL that I can see somewhere so I can report it or monitor it ? > > > -- > Andy > > > -- > 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]
