Bug #3104 SUM(columnX) AS columnX with join doesn't give values..

• Submitted: 8 Mar 2004 5:19
• Modified: 8 Mar 2004 8:47
• Reporter: Azza Azza69
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1.alpha
• OS: Windows (Win2k)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[8 Mar 2004 5:19] Azza Azza69

Description:
The following query (see how to repeat) gives me values in all columns except the "Picked" and the "PreAllocated" ones.
If I change the column aliases to "Picked2" and "PreAllocated2", I get values.  

How to repeat:
* Here's a small dump of the table structures and some test data:
CREATE TABLE stock_ordersd (
  UID INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  UID_Order INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  UID_Batch INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  Ordered MEDIUMINT(9) NOT NULL DEFAULT '0',
  PreAllocated MEDIUMINT(9) NOT NULL DEFAULT '0',
  PickState ENUM('Instructed','Waiting','Picked') NOT NULL DEFAULT 'Waiting',
  Picked MEDIUMINT(9) NOT NULL DEFAULT '0',
  UID_Picker INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (UID),
  UNIQUE KEY ixUID_Order (UID_Order, UID_Batch)
);

INSERT INTO stock_ordersd (UID, UID_Order, UID_Batch, Ordered, PreAllocated, PickState, Picked, UID_Picker) VALUES 
  (11,3,9,2,1,'Picked',2,1),
  (28,3,1,3,2,'Picked',2,1),
  (13,3,3,1,3,'Picked',1,1),
  (14,3,2,3,4,'Picked',3,1),
  (15,3,4,2,5,'Picked',2,1),
  (16,3,6,1,6,'Waiting',1,1),
  (17,3,8,1,7,'Waiting',0,1),
  (18,3,7,5,8,'Waiting',4,0);

CREATE TABLE stock_batches (
  UID INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  Code VARCHAR(10) DEFAULT NULL,
  UID_Site INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  UID_Item INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  PartNumber VARCHAR(15) DEFAULT NULL,
  UID_Supplier INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  Outer_Qty MEDIUMINT(9) NOT NULL DEFAULT '0',
  Inner_Qty DECIMAL(10,3) NOT NULL DEFAULT '0.000',
  BestBefore DATE DEFAULT NULL,
  Allocated MEDIUMINT(9) NOT NULL DEFAULT '0',
  InPick MEDIUMINT(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (UID),
  KEY ixUID_SiteItem (UID_Site, UID_Item)
);

INSERT INTO stock_batches (UID, Code, UID_Site, UID_Item, PartNumber, UID_Supplier, Outer_Qty, Inner_Qty, BestBefore, Allocated, InPick) VALUES 
  (1,'B/00000112',2,27628,'',394,10,20,NULL,7,0),
  (2,'B/00000113',2,36734,'',394,9,100,NULL,6,0),
  (3,'B/00000114',2,36734,'',394,1,99,NULL,1,0),
  (4,'B/00000115',2,47350,'',394,3,150,NULL,3,0),
  (5,'B/00000116',2,47350,'',394,1,50,NULL,1,0),
  (6,'B/00000117',2,47350,'',394,1,23,NULL,1,0),
  (7,'B/00000118',2,18146,'',394,10,69,NULL,7,0),
  (8,'B/00000119',2,18146,'',394,1,8,NULL,1,0),
  (9,'B/00000120',2,83,'DD',394,3,50,NULL,3,0);
----
* THIS QUERY GIVES 0's:
SELECT
  sod.UID_Batch,
  SUM(sod.Ordered) AS Ordered,
  SUM(sod.PreAllocated)*sb.Inner_Qty AS PreAllocated,
  SUM(sod.Picked)*sb.Inner_Qty AS Picked
FROM
  stock_ordersd sod
LEFT JOIN
  stock_batches sb ON sb.UID=sod.UID_Batch
WHERE
  sod.UID_Order=:prmUID_Order
GROUP BY
  UID_Batch
----
* THIS QUERY GIVES VALUES:
SELECT
  sod.UID_Batch,
  SUM(sod.Ordered) AS Ordered,
  SUM(sod.PreAllocated)*sb.Inner_Qty AS PreAllocated2,
  SUM(sod.Picked)*sb.Inner_Qty AS Picked2
FROM
  stock_ordersd sod
LEFT JOIN
  stock_batches sb ON sb.UID=sod.UID_Batch
WHERE
  sod.UID_Order=:prmUID_Order
GROUP BY
  UID_Batch

[8 Mar 2004 8:47] Dean Ellis

I cannot repeat this with 4.1.2 (both queries return the same results); this has likely already been corrected, but please update this if you continue to see this problem when 4.1.2 is released.

[8 Mar 2004 8:52] Azza Azza69

Will do!  Any idea on release timescale of 4.12?