[resolved] SQL query returning different results within talend

[Anonymous]

Hi, I have a tMysqlInput component that runs this query :
SELECT IF (LENGTH(MONTH(a.currentDate)) = 2,CONCAT(YEAR(a.currentDate), "-",
MONTH(a.currentDate), "-00"), CONCAT(YEAR(a.currentDate), "-0", MONTH(a.currentDate),
"-00")) AS currentMonth, SUM(dataCount) AS dataCount
FROM dw_stats_newAnnonceCount_daily a
WHERE MONTH(a.currentDate) = MONTH(CURDATE())
GROUP BY MONTH(a.currentDate)

When executed directly on the server, it gives me the appropriate results :
currentMonth dataCount
2010-04-00 171416

When executed withing the component, it gives me weird stuff :
2010-03-31|171416
The data count is accurate but for some reasons, my date workaround doesn't work well with talend.
Maybe a bug but not sure.
Any idea anyone?

[Anonymous]

😕

[Anonymous]

Some update :
Actually, my query is in the repository and when I run it from there, it gives me the correct result too.
Really seems to be some strange bug from what I can judge.

[Anonymous]

Hello
It's weird, anyway it should display '00' at the end of date, Can you upload some screenshots of job?
I try to reproduce the problem.
Best regards
Shong

[Anonymous]

hi Shong, please see the attached screenshots.
Thanks,

[Anonymous]

Some update :
While waiting, I tried to create another query leading to the same results and I came up with this :
SELECT CAST(CONCAT(SUBSTR(a.currentDate, 1, 8), '00') AS DATE) AS currentMonth, SUM(dataCount) AS dataCount
FROM dw_stats_newAnnonceCount_daily a
WHERE MONTH(a.currentDate) = MONTH(CURDATE())
GROUP BY MONTH(a.currentDate)
Again, once executed on the server itself, it gives the correct result : 2010-04-00
And again once executed within the talend component, it returns 2010-03-31
Another info is that now, even when executed from within the repository, it gives the wrong result while the first query was returning the good one.
Please note that with this query, if I change '00' for '01', it seems to work, returning 2010-04-01 so it seems that there is something in java that mabye stops you from have a date like xxxx-yy-00
Anybody can confirm this information?
As for the first request, even If I change '00' for '01', I still get the weird result.

[Anonymous]

If you return this value as a Date (Java Type) the day 00 don't exist and Java create a Date itself (close from the result) to don't return a DataFormatException.
If you return this valus as a String, I guess you'll have the expected result because Java will not try to return a Valid data and the String would be : 2010-04-00.
Feel free to turn the Schema definition to String for this FIELD in your tDBInput component.
Best regards,

[Anonymous]

Ok. I guess the whole problem since the beginning is that SQL let you have a date like 2010-04-00 but Java doesnt. I guess I'll try to play with data types so date are considered as string.

[Anonymous · Accepted solution]

Correct. Your SQL expression return your expecting value without any validation.
In Java the data type like Data, Float or others will return only a Date or Float data in those fields otherwise it will return a Exception DateFormatException, NumberFormatException etc....
If you change in your input component to String, Java will build your SQL return conform to your result without any interpretation.
Best regards;

[Anonymous]

Problem solved! Now getting what I was expecting to.
Thank you cantoine.