The code below highlights a TRANSFO 2

[baltman]

The code below highlights a TRANSFORM/SQL 'curiosity'- at least in VFP 7. Is there any reason why the 2nd and 3rd SQL should be different? As far as I'm concerned, the 2nd (which returns only 5 supposed unique values) is wrong and exposes a bug. Is this issue addressed in VFP 8 or planned in 9?

Brian

CREATE TABLE test (testdate d, testdate2 c(6))

FOR x = 1 TO 100
APPEND BLANK
ENDFOR

REPLACE ALL testdate WITH {12/31/2003}-RECNO()*150
REPLACE ALL testdate2 WITH (TRANSFORM(YEAR(testdate))+PADL(TRANSFORM(MONTH(testdate)),2,"0&quot)

SELECT dist (TRANSFORM(YEAR(testdate),'####')+PADL(TRANSFORM(MONTH(testdate),'##'),2,"0&quot);
GROUP BY 1 FROM test nowait&& returns 5 unique values
?"using TRANSFORM with an explicit format on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values"

SELECT dist (TRANSFORM(YEAR(testdate))+PADL(TRANSFORM(MONTH(testdate)),2,"0&quot);
GROUP BY 1 FROM test nowait&& returns 5 unique values
?"using TRANSFORM without an explicit format on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values"

SELECT dist testdate2 ;
GROUP BY 1 FROM test nowait&& returns 100 unique values
?"using TRANSFORM without an explicit format into a chr field first on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values"

DROP TABLE test

 

[BPeisch]

I'm not exactly sure why, but your GROUP BY is messing you up, and is completely unnecessary. DISTINCT will only pull unique values from the source. GROUP BY will remove duplicates, which DISTINCT already did. When I removed the GROUP BY clauses, it returned the same number of results all three times.


-BP (Barbara Peisch)

http://www.peisch.com

 

[craigsboyd]

It is odd the way that having the Group By 1 clause on the second SQL causes the query to only look at the first 3 characters of the year. It certainly make aggregate value fields come out wrong given the way in which it behaves. Wonder why it only looks 3 characters in when doing this?

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[baltman]

It does look like to GROUP BY is a problem when combined with the TRANSFORM(YEAR()). So maybe this is a bug?

I run distinct SQLs all the time... and just add a GROUP BY as a matter of habit.

It's never been an issue before

It may have something to do with the TRANSFORM somehow thinking there's a decimal in the YEAR() and taking the RTRIM(STR(YEAR(testdate)),9) instead of RTRIM(STR(YEAR(testdate)),10)... how it seems the TRANSFORM says it works when there's no format specified for numbers.

If no one can suggest a reason for why this SHOULD be occuring, I'd just as soon try to get this addressed in Europa.

Thanks,
Brian

 

[craigsboyd]

i was first wondering whether there would ever be a time that this potential bug would cause a problem, given the use of distinct and group by in the example given...however, then I tried it without the distinct clause and with an aggregate value field thrown in and found that the problem still exists. It is related to the transform being performed on a numeric value without a format and group by being used in with this. For some reason the group by starts grouping by just the first 3 digits...it grabs the first 1960's date, first 1970's date, 1980's, 1990's...and so forth. It would indeed cause problems for a developer if he/she were unaware that this condition exists. I am trying to think of anyways that this peculiar behavior could be useful...does it allow us to do somethings that wouldn't otherwise be as easy or possible?

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[BPeisch]

I reported this as a bug. Let's hope it's fixed in Europa.


-BP (Barbara Peisch)

http://www.peisch.com

 

[baltman]

Or at least not ported to CAST()...

 

[ramani]

Hi

I do not find anything wrong with VFP's behaviour.

To me, the reasons seems to be wrong comparison and usage.


syntax for
TRANSFORM(eExpression, [cFormatCodes])

If you omit cFormatCodes, Visual FoxPro performs a default transformation on eExpression. The following table describes the transformation performed for each data type eExpression can assume: ....

If Numeric (includes Double, Float, or Integer data types)

Trailing zeros are removed from the decimal portion of a numeric value. If the numeric value is a whole number, a decimal point is not included in the transformed value (for example, 4.0 is transformed to 4). If the numeric value is less than one but greater than negative one, zero is included before the decimal point (for example, .4 is transformed to 0.4).


The above Red coloured is the VFPs manual.


Now
PADL(TRANSFORM(MONTH(testdate),'##'),2,"0&quot)
is not same as
PADL(TRANSFORM(MONTH(testdate)),2,"0&quot)


The format masks used (##) always produces a resultant two character result, thus ending PADL a surplus code doing nothing. On the conttry, if an ALLTRIM() used around that TRANSFORM().. as in
PADL(ALLT(TRANSFORM(MONTH(testdate),'##')),2,"0&quot) will give the desired result.

For single digit months, the above results will be different.. TRANSFORM will produce " 1" in one case and in another case it will be "1", and doing a PADL will make make the difference in results.

In my view there is no bug in VFP or nothing strange in this part of the code above, .........
OR may be I am looking at something which you are all not looking and dont understand what you are all looking.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[ramani]

HI

Just thought of providing what I tested..

************************************************
CREATE TABLE test (testdate d, testdate2 c(6))

FOR x = 1 TO 100
APPEND BLANK
ENDFOR
clear
REPLACE ALL testdate WITH {^2003-12-31}-RECNO()*150
REPLACE ALL testdate2 WITH (TRANSFORM(YEAR(testdate))+PADL(TRANSFORM(MONTH(testdate)),2,"0&quot)

SELECT dist (TRANSFORM(YEAR(testdate),'####')+PADL(TRANSFORM(MONTH(testdate),'##'),2,"0&quot);
GROUP BY 1 FROM test && returns 5 unique values
?"using TRANSFORM + PADL with an explicit format on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values."


SELECT dist (TRANSFORM(YEAR(testdate),'####')+TRANSFORM(MONTH(testdate),'##'));
GROUP BY 1 FROM test && returns 100 unique values
?"using TRANSFORM with an explicit format on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values. PADL removal has no effect on the result."


SELECT dist (PADL(TRANSFORM(YEAR(testdate)),4,"0&quot+PADL(TRANSFORM(MONTH(testdate)),2,"0&quot) AS testnew;
GROUP BY 1 FROM test && returns 100 unique values
?"using TRANSFORM without an explicit format on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values"

SELECT dist testdate2 ;
GROUP BY 1 FROM test && returns 100 unique values
?"using TRANSFORM without an explicit format into a chr field first on a yyyy/mm distict returns "+TRANSFORM(RECCOUNT())+" unique values"

DROP TABLE test
******************************************
But the resultant tables are not the same.. since in one case "0" are padded, and in others they are left as spaces. I removed the nowaits to make the distinction visible.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[craigsboyd]

Ramani,

I am not really following your trail of logic here. In your latest post you don't even include an example of the code that produces the bug (well you add a PADL as a workaround). Add this to your tests and I think you'll see that there is indeed a bug:

CREATE TABLE test (testdate d, testdate2 c(6))

FOR x = 1 TO 100
 APPEND BLANK
ENDFOR
clear
 REPLACE ALL testdate WITH {^2003-12-31}-RECNO()*150
 REPLACE ALL testdate2 WITH (TRANSFORM(YEAR(testdate))+PADL(TRANSFORM(MONTH(testdate)),2,"0"))
 
 SELECT dist TRANSFORM(YEAR(testdate)) AS testnew, Count(testdate) as mycount;
 GROUP BY 1 FROM test && returns 2 unique values
 
SELECT test
BROWSE &&Look at test table to see that query was wrong
DROP TABLE test

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[BPeisch]

Here's the response I got from Aleksey (one of the members of the VFP development team) on this issue:

This is a well known problem - if an expression yields result with different length from one record to another, the result is unpredictable. If expression (TRANSFORM(YEAR(testdate))+PADL(TRANSFORM(MONTH(testdate)),2,"0&quot) is evaluated against an empty date, it yields "000". This is exactly what happens with this query. When GROUP BY key is being set up, the expression is evaluated against a blank record (this happens even if there are no blank records in the table).



-BP (Barbara Peisch)

http://www.peisch.com

 

[craigsboyd]

Well that explains why it only looks at the first 3 characters in the original example...thank you for that BPeisch. Star for your connections!

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[BPeisch]

A star for my connections? Thanks, but it kinda gives me an unfair advantage, dontcha think?


-BP (Barbara Peisch)

http://www.peisch.com

 

[craigsboyd]

Nah, it gives this forum just one more member that is useful, insightful, kind, helpful, intelligent, and one hell of a top notch VFP Programmer! If that isn't worth a star then I'm giving all of my stars back.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[DSummZZZ]

BPeisch,

Then how about a star just for taking the initiative to delve deeper into the issue, and get the clarification from the VFP folks.



-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[BPeisch]

OK, thanks guys. BTW, Aleksey mentioned that in VFP 9, this will give an error message instead of returning incorrect results.


-BP (Barbara Peisch)

http://www.peisch.com

 

[baltman]

Sure, I assume most experienced SQLers are familiar with the fact that the 1st return length can set the width of the result field, but my example doesn't have any empty dates!

Either way, by adding a '+replicate(chr(32),6)' did make the problem go away.

Just curious where the empty date comes from... the eof()?

Brian

 

[BPeisch]

Aleksey did say, "(this happens even if there are no blank records in the table)." So it doesn't matter that you didn't have any blank dates.



-BP (Barbara Peisch)

http://www.peisch.com