Bug #6931 Date Type column problem when using UNION-Table.

• Submitted: 2 Dec 2004 3:45
• Modified: 4 Feb 2005 13:26
• Reporter: Poh Chuan Sim
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.8
• OS: Any (any)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[2 Dec 2004 3:45] Poh Chuan Sim

Description:
i have two QUERY include Date type column.

QUERY 1:
(
    SELECT
      Item.*,
      SHTrx1.RefNo, SHTrx1.TrxType, SHTrx1.Name,
      SHTrx1.TrxDate AS SortDate
    FROM Item
      LEFT OUTER JOIN SDTrx AS SDTrx1 ON (Item.ItemLinkID = SDTrx1.ItemLinkID)
      INNER JOIN SHTrx AS SHTrx1 ON (SDTrx1.SHTrxLinkID = SHTrx1.SHTrxLinkID)
    ORDER BY Item.ICode, Item.SizeCode, SHTrx1.TrxDate
)
On the result, column 'SortDate' will be a Date Type column, it is correct.

QUERY 2:
(
    SELECT
      Item.*,
      'B/F' AS RefNo, 'BL' AS TrxType, 'Balance Brought Forward' AS Name,
      CAST('2004-12-31' AS DATE) AS SortDate
    FROM Item
      LEFT OUTER JOIN SDTrx ON (Item.ItemLinkID = SDTrx.ItemLinkID)
      INNER JOIN SHTrx ON (SDTrx.SHTrxLinkID = SHTrx.SHTrxLinkID)
    GROUP BY Item.ItemLinkID
    ORDER BY Item.ICode, Item.SizeCode, SHTrx.TrxDate
)
On the result, column 'SortDate' also be a Date Type column, it is correct.

But, when i UNION this two QUERY be a one QUERY, the column 'SortDate' will became
a STRING (CHAR) Type column.

JOIN QUERY:
(
    SELECT
      Item.*,
      SHTrx1.RefNo, SHTrx1.TrxType, SHTrx1.Name,
      SHTrx1.TrxDate AS SortDate
    FROM Item
      LEFT OUTER JOIN SDTrx AS SDTrx1 ON (Item.ItemLinkID = SDTrx1.ItemLinkID)
      INNER JOIN SHTrx AS SHTrx1 ON (SDTrx1.SHTrxLinkID = SHTrx1.SHTrxLinkID)
    ORDER BY Item.ICode, Item.SizeCode, SHTrx1.TrxDate
)
UNION
(
    SELECT
      Item.*,
      'B/F' AS RefNo, 'BL' AS TrxType, 'Balance Brought Forward' AS Name,
      CAST('2004-12-31' AS DATE) AS SortDate
    FROM Item
      LEFT OUTER JOIN SDTrx ON (Item.ItemLinkID = SDTrx.ItemLinkID)
      INNER JOIN SHTrx ON (SDTrx.SHTrxLinkID = SHTrx.SHTrxLinkID)
    GROUP BY Item.ItemLinkID
    ORDER BY Item.ICode, Item.SizeCode, SHTrx.TrxDate
)
ORDER BY ICode, SizeCode, SortDate

if not a bug, can you let me know how can show the 'SortDate' column as a Date Type column.

Attachment:
Table Structure:
CREATE TABLE IF NOT EXISTS Item (
  ItemLinkID        VARCHAR(32) BINARY NOT NULL,
  ICode             VARCHAR(12) NOT NULL,
  SizeCode          VARCHAR(9),
  ShortCode         VARCHAR(5),
  Status            ENUM('Active','NonActive','Obsoleted') DEFAULT 'Active',
  Description       TEXT,
  Brand             VARCHAR(10),
  GroupID           VARCHAR(10),
  SubGroupID        VARCHAR(10),
  ItemType          VARCHAR(10),
  UOM               VARCHAR(4),
  ListPrice         NUMERIC(12,2),
  MinRetailPrice    NUMERIC(12,2),
  MinTraderPrice    NUMERIC(12,2),
  CostPrice         NUMERIC(14,4),
  PRIMARY KEY (ItemLinkID),
  UNIQUE INDEX ByICodeSizeCode (ICode,SizeCode),
  INDEX ByShortCode (ShortCode),
  INDEX ByStatusICode (Status,ICode,SizeCode),
  INDEX ByBrandICode (Brand,ICode,SizeCode),
  INDEX ByGroupIDSubGroupIDICode (GroupID,SubGroupID,ICode,SizeCode)
);

#// Stock Transaction Header
CREATE TABLE IF NOT EXISTS SHTrx (
  SHTrxLinkID     VARCHAR(32) BINARY NOT NULL,  #// This can be from other sources: billing, stock take etc.
  SourceLinkID    VARCHAR(32) BINARY,
  SourceID        VARCHAR(2),
  TrxType         VARCHAR(2) NOT NULL,   #// IS=Issue, RE=Receive, AD=Adjustment (In/Out)
  RefNo           VARCHAR(12) NOT NULL,  #// AO=Adjustment Out , OP=Opening balance
  TrxDate         DATE,
  TrdType         VARCHAR(1),
  TCode           VARCHAR(8),
  Name            VARCHAR(40),
  Remark          TEXT,
  Login           VARCHAR(10),
  ModDate         DATE,
  PRIMARY KEY (SHTrxLinkID),
  UNIQUE INDEX ByTrxTypeRefNo (TrxType,RefNo),
  INDEX ByRefNo (RefNo),
  INDEX ByTrxTypeTrxDateRefNo (TrxType,TrxDate,RefNo),
  INDEX BySHTrxLinkIDTrxType (SHTrxLinkID,TrxType),
  INDEX ByTrxDate (TrxDate),
  INDEX BySourceLinkID (SourceLinkID),
  INDEX BySourceIDSourceLinkID (SourceID,SourceLinkID)
);

#// Stock Transaction Detail
CREATE TABLE IF NOT EXISTS SDTrx (
  SHTrxLinkID     VARCHAR(32) BINARY NOT NULL,
  SDTrxLinkID     VARCHAR(32) BINARY NOT NULL,   #// This can be from other sources: billing, stock take etc.
  TrxDate         DATE,
  LineNo          DOUBLE UNSIGNED DEFAULT 0,
  WHLoc           VARCHAR(2) DEFAULT '',
  ItemLinkID      VARCHAR(32) BINARY,
  UOM             VARCHAR(5),
  TrxTypeFactor   NUMERIC(1),     #// IS = -1, RE,AD = +1
  Qty             NUMERIC(12,2) DEFAULT 0,
  UPrice          NUMERIC(12,2) DEFAULT 0,
  NetAmt          NUMERIC(12,2) DEFAULT 0,
  Cost            NUMERIC(14,4) DEFAULT 0,
  Remark          TEXT,
  Flags           VARCHAR(2),
  Login           VARCHAR(10),
  ModDate         DATE,
  PRIMARY KEY (SDTrxLinkID),
  UNIQUE INDEX BySHTrxLinkIDLineNoSDTrxLinkID (SHTrxLinkID,LineNo,SDTrxLinkID),
  INDEX ByWHLocItemLinkIDTrxDate (WHLoc,ItemLinkID,TrxDate)
#  INDEX ByItemLinkIDTrxDateTrxType (ItemLinkID,TrxDate,TrxType),               #// use query to overcome
#  INDEX ByTrxTypeItemLinkIDTrxDate (TrxType,ItemLinkID,TrxDate),               #// use query to overcome
#  INDEX ByTrdTypeTCodeItemLinkIDTrxDate (TrdType,TCode,ItemLinkID,TrxDate)     #// use query to overcome
);

How to repeat:
i am using Delphi 5 to develop Application.

[4 Feb 2005 13:26] Sergei Glukhov

Fixed in 4.10 tree