From: Rik Wasmus
Date: February 13 2012 5:11pm
Subject: Re: weird difference in workbench and CLI query
List-Archive: http://lists.mysql.com/mysql/226790
Message-Id: <201202131811.53820.rik@grib.nl>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit

> When I run this query in workbench:
> select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
> s.ExamDate 'SJH Exam Date' from chestcon_log c
> left join exams e on CONCAT("000",c.acc_number) = e.LastWordAccNum
> left join sjhreports_ s on c.acc_number = s.AccessionNumber
> WHERE
> c.timestamp_exam <= CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
> '-1' DAY) AS DATETIME) AND c.timestamp_exam >=
> CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
> DATETIME)
> 
> I get this:
> 7330565	NULL	 2012-02-01
> 6604419	 2011-01-25	NULL
> 
> but when I run the same query in a shell script, on a Linux box or OS X, I
> get this: 7330565	NULL	2012-02-01
> 6604419	NULL	NULL

I see mixes of single ' and double "... And this is the line that fails, the 
only occurance of ":
    left join exams e on CONCAT("000",c.acc_number) = e.LastWordAccNum

Which my crystal ball tells me is probably a sign of improper escapes in shell 
scripts. What happens if you replace that line with:

    left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum
-- 
Rik Wasmus