I've been bitten by this issue once again, after upgrading a mysql server to v5.0.67. (with MyDAC 5.00.1.6 for Delphi7)
Its hard to create a sample for you. The query being executed is listed below, its quite complex, but you can see the Colours bit below, which is where the error pops up.
Note that If I trace the SQL with DB monitor (I see what is below), and execute the Query with "limit 1" on the mysql command line, it runs just fine.
for me is why when I add the Colour field to the TMyQuery in the fields editor, does it select largint instead of Int?
Code: Select all
SELECT J.Jcard_no as Job, J5.Jprev_no as Spec,
J.Cust_code as CustCode, asalmas.NAME as Customer,
J5.Print_ref, J5.Ord_no,
jstatus.Value as JobStatus, Ord_date, J5.Del_date1 as Delivery,
Film_col, atable.TABNAME as UnitOfSale,
J5.Quantity, J5.Overall_width, J5.Gus_width, J4.Cn_size, J5.Guage,
J5.Extrusion, J4.extruder, J4.Ex_weight, (J4.Ex_weight - IFNULL(J2.Ex_po_weight,0)) AS RemainingKgs,
J5.Slitting, J4.slitter, (J5.Quantity - IFNULL(J2.Cn_po_quantity,0)) AS RemainingCn,
J5.Conversion, J4.Converter, J5.Lamination,
J2.Sl_po_kgs, J2.Sl_po_meters, Lm_po_meters, lm_po_kgs,
J3.Printing, J3.printer, IF(J5.Pr_type=1,'Surface','Reverse') as P_Reverse, inktype.Value as Ink_Type,
material.value as pr_material,
IF(J3.pr_width>0, J3.pr_width, J4.Ex_overall_width/J6.Up) as Pr_width,
IF(J3.pr_thick>0, J3.pr_thick, J4.Ex_thick2) as Pr_thick,
IF(J3.pr_kgs>0, J3.pr_kgs, J4.Ex_weight) as Pr_kgs,
@_colours:=CAST(
(IF(Pr_colour1>0,1,0) +IF(Pr_colour2>0,1,0) +IF(Pr_colour3>0,1,0) +
IF(Pr_colour4>0,1,0) +IF(Pr_colour5>0,1,0) +IF(Pr_colour6>0,1,0) +
IF(Pr_colour7>0,1,0) +IF(Pr_colour8>0,1,0) ) AS UNSIGNED) AS Colours,
J4.Ex_meters,
@_pr_metres:= IF(IFNULL(J3.pr_m,0) >0, J3.pr_m,
IF(J5.Film_type=2, J4.Ex_meters*2*J6.Up, J4.Ex_meters)) as Pr_meters,
@_RemainingPr:= (@_pr_metres - IFNULL(J2.Pr_po_meters,0)) AS RemainingPr,
(@_RemainingPr/printer.av_speed/60) + (@_colours *printer.av_setup_time/60) as RemainingTimePr,
@_lm_metres:= CAST(IF(IFNULL(J5.Lm_meters,0) >0, J5.Lm_meters, @_pr_metres) AS UNSIGNED) as Lm_meters,
(@_lm_metres - IFNULL(J2.Lm_po_meters,0)) AS RemainingLm,
@_sl_metres:= CAST(IF(@_pr_metres >0, @_pr_metres, J4.Ex_meters) AS UNSIGNED) as Sl_meters,
(@_sl_metres - IFNULL(J2.Sl_po_kgs,0)) AS RemainingSl,
((@_sl_metres -IFNULL(J2.Sl_po_kgs,0)) / slitter.av_speed/60) as RemainingTimeSl,
((J4.Ex_weight -IFNULL(J2.Ex_po_weight,0)) / extruder.av_speed) as RemainTimeEx,
((J5.Quantity -IFNULL(J2.Cn_po_quantity,0)) / convertr.av_speed/60) as RemainingTimeCn,
prodex, prodsl, prodlm, prodpr, prodcn,
prodsex, prodssl, prodslm, prodspr, prodscn,
p_cyl.Value as PrintCylinder,
CAST((CONCAT(J5.Extrusion)) IN ('N','D') AS unsigned) AS PrReady,
CAST((CONCAT(J5.Extrusion,J3.Printing)) IN ('NN','ND','DN','DD') AS unsigned) AS CnReady,
CAST((CONCAT(J5.Extrusion,J3.Printing)) IN ('NN','ND','DN','DD') AS unsigned) AS LmReady,
CAST((CONCAT(J5.Extrusion,J3.Printing,J5.Lamination)) IN ('NNN','NND','NDN','NDD','DNN','DND','DDN','DDD') AS unsigned) AS SlReady,
'boranX' as Company
FROM jobcard J
LEFT JOIN jobcard2 J2 on J.Jcard_no=J2.Jcard_no
LEFT JOIN jobcard3 J3 on J.Jcard_no=J3.Jcard_no
LEFT JOIN jobcard4 J4 on J.Jcard_no=J4.Jcard_no
LEFT JOIN jobcard5 J5 on J.Jcard_no=J5.Jcard_no
LEFT JOIN jobcard6 J6 on J.Jcard_no=J6.Jcard_no
LEFT JOIN jstatus on J.Job_status=jstatus.Code
LEFT JOIN inktype on J3.Pr_ink1=inktype.Code
LEFT JOIN material on J3.pr_material=material.Code
LEFT JOIN asalmas on J.cust_code=asalmas.ACCOUNT
LEFT JOIN extruder on J4.extruder=extruder.code
LEFT JOIN convertr on J4.Converter=convertr.code
LEFT JOIN slitter on J4.slitter=slitter.code
LEFT JOIN printer on J3.printer=printer.code
LEFT JOIN p_cyl on (J3.printer=p_cyl.Printer and J3.Pr_cyl=p_cyl.Code)
LEFT JOIN atable on J5.Unit_code=atable.TABCODE and atable.TABLEKEY='UNI'
WHERE TO_DAYS(Ord_date)>TO_DAYS(NOW())-365 AND J.Job_status 0
AND J.Cust_code1441 AND J.Cust_code 2103
ORDER BY Ord_date