SELECT SQL + TRANSFORM(numeric fields) sometimes give no results 1

[vgulielmus]

1 STR() - ok
2 TRANSFORM with mask - ok
3 TRASNFORM without mask - wrong
4 TRASNFORM without mask but using intermediate results (cursors) - ok

Proof:

**************
* numeric
CLOSE DATABASES ALL 
CREATE CURSOR cCur (cField c(10),nField n(10,2))
INSERT INTO cCur (cField,nField) VALUES ("PO01",300)
INSERT INTO cCur (cField,nField) VALUES ("PO01",200)
INSERT INTO cCur (cField,nField) VALUES ("PO01",100)
INSERT INTO cCur (cField,nField) VALUES ("PO02",300)
INSERT INTO cCur (cField,nField) VALUES ("PO02",500)

* Ok
SELECT * FROM cCur WHERE STR(nField,10,2)=;
				(SELECT MIN(STR(nField,10,2)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT * FROM cCur WHERE TRANSFORM(nField,"@R 9999999.99")=;
				(SELECT MIN(TRANSFORM(nField,"@R 9999999.99")) FROM cCur jm WHERE jm.cField=cCur.cField)
*  No result
SELECT * FROM cCur WHERE TRANSFORM(nField)=;
				(SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT cField,transform(nField) as nField FROM cCur INTO cursor _1
SELECT cField,MIN(transform(nField)) as nField FROM cCur GROUP BY 1 INTO cursor _2

SELECT * FROM _1 WHERE nField=;
				(SELECT nField FROM _2 WHERE _2.cField=_1.cField)
				
**************
* integer
CLOSE DATABASES ALL 
CREATE CURSOR cCur (cField c(10),iField n(10))
INSERT INTO cCur (cField,iField) VALUES ("PO01",300)
INSERT INTO cCur (cField,iField) VALUES ("PO01",200)
INSERT INTO cCur (cField,iField) VALUES ("PO01",100)
INSERT INTO cCur (cField,iField) VALUES ("PO02",300)
INSERT INTO cCur (cField,iField) VALUES ("PO02",500)

* Ok
SELECT * FROM cCur WHERE STR(iField)=;
				(SELECT MIN(STR(iField)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT * FROM cCur WHERE TRANSFORM(iField,"@R 9999999")=;
				(SELECT MIN(TRANSFORM(iField,"@R 9999999")) FROM cCur jm WHERE jm.cField=cCur.cField)
* No result
SELECT * FROM cCur WHERE TRANSFORM(iField)=;
				(SELECT MIN(TRANSFORM(iField)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT cField,transform(iField) as iField FROM cCur INTO cursor _1
SELECT cField,MIN(transform(iField)) as iField FROM cCur GROUP BY 1 INTO cursor _2

SELECT * FROM _1 WHERE iField=;
				(SELECT iField FROM _2 WHERE _2.cField=_1.cField)
				
**************
* double
CLOSE DATABASES ALL 
CREATE CURSOR cCur (cField c(10),dField b(2))
INSERT INTO cCur (cField,dField) VALUES ("PO01",300)
INSERT INTO cCur (cField,dField) VALUES ("PO01",200)
INSERT INTO cCur (cField,dField) VALUES ("PO01",100)
INSERT INTO cCur (cField,dField) VALUES ("PO02",300)
INSERT INTO cCur (cField,dField) VALUES ("PO02",500)

* Ok
SELECT * FROM cCur WHERE STR(dField,10,2)=;
				(SELECT MIN(STR(dField,10,2)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT * FROM cCur WHERE TRANSFORM(dField,"@R 9999999.99")=;
				(SELECT MIN(TRANSFORM(dField,"@R 9999999.99")) FROM cCur jm WHERE jm.cField=cCur.cField)
* No result
SELECT * FROM cCur WHERE TRANSFORM(dField)=;
				(SELECT MIN(TRANSFORM(dField)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT cField,transform(dField) as dField FROM cCur INTO cursor _1
SELECT cField,MIN(transform(dField)) as dField FROM cCur GROUP BY 1 INTO cursor _2

SELECT * FROM _1 WHERE dField=;
				(SELECT dField FROM _2 WHERE _2.cField=_1.cField)
				
**************
* currency
CLOSE DATABASES ALL 
CREATE CURSOR cCur (cField c(10),yField Y)
INSERT INTO cCur (cField,yField) VALUES ("PO01",300)
INSERT INTO cCur (cField,yField) VALUES ("PO01",200)
INSERT INTO cCur (cField,yField) VALUES ("PO01",100)
INSERT INTO cCur (cField,yField) VALUES ("PO02",300)
INSERT INTO cCur (cField,yField) VALUES ("PO02",500)

* Ok
SELECT * FROM cCur WHERE STR(yField,10,2)=;
				(SELECT MIN(STR(yField,10,2)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT * FROM cCur WHERE TRANSFORM(yField,"@R 9999999.99")=;
				(SELECT MIN(TRANSFORM(yField,"@R 9999999.99")) FROM cCur jm WHERE jm.cField=cCur.cField)
* No result
SELECT * FROM cCur WHERE TRANSFORM(yField)=;
				(SELECT MIN(TRANSFORM(yField)) FROM cCur jm WHERE jm.cField=cCur.cField)
* Ok
SELECT cField,transform(yField) as yField FROM cCur INTO cursor _1
SELECT cField,MIN(transform(yField)) as yField FROM cCur GROUP BY 1 INTO cursor _2

SELECT * FROM _1 WHERE yField=;
				(SELECT yField FROM _2 WHERE _2.cField=_1.cField)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

The behaviour occurs only on conjuction with SET ANSI ON

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

I believe I found the cause.
TRANSFORM(nField) is Character(3) while MIN(TRANSFORM(nField)) is Character.
Both of the following shows the result even when SET ANSI is ON

SET ANSI ON
SELECT * FROM cCur WHERE CAST(TRANSFORM(nField) as C)=;
				(SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.cField=cCur.cField)

SELECT * FROM cCur WHERE TRANSFORM(nField)=;
				(SELECT CAST(MIN(TRANSFORM(nField)) as C(3)) FROM cCur jm WHERE jm.cField=cCur.cField)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Transform shortens the computed string. That's the big difference to STR() and Transform with a mask.

I would never use or recommend to use an expression like MIN(Transform(field)) for a result field, but a padded result value.

I can't tell what happens and why, but doing this, I see an intermediate cursor with a field min_exp_1 as char(1). with "1" and "3" instead of the min values "100" and "300". Of course that fails to compare with "100" and "300".

Set Ansi On

CREATE CURSOR cCur (cField c(10),nField n(10,2))
INSERT INTO cCur (cField,nField) VALUES ("PO01",300)
INSERT INTO cCur (cField,nField) VALUES ("PO01",200)
INSERT INTO cCur (cField,nField) VALUES ("PO01",100)
INSERT INTO cCur (cField,nField) VALUES ("PO02",300)
INSERT INTO cCur (cField,nField) VALUES ("PO02",500)


*  No result
SELECT * FROM cCur WHERE TRANSFORM(nField)=;
            (SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.cField=cCur.cField) And singlestep()
            
Function singlestep()
   Set Step On
   Return .T.

I assume that cursor with the Char(1) field comes from executing SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.cField=cCur.cField.

The recommende solution for any expressions in queries ptentially having a varied lengths, is to pad them:

Set Ansi On

CREATE CURSOR cCur (cField c(10),nField n(10,2))
INSERT INTO cCur (cField,nField) VALUES ("PO01",300)
INSERT INTO cCur (cField,nField) VALUES ("PO01",200)
INSERT INTO cCur (cField,nField) VALUES ("PO01",100)
INSERT INTO cCur (cField,nField) VALUES ("PO02",300)
INSERT INTO cCur (cField,nField) VALUES ("PO02",500)

*  This way it works:
SELECT * FROM cCur WHERE PADL(TRANSFORM(nField),10)=;
            (SELECT PADL(MIN(TRANSFORM(nField)),10) FROM cCur jm WHERE jm.cField=cCur.cField)

Bye, Olaf.

 

[Dan Freeman]

Transform shortens the computed string. That's the big difference to STR() and Transform with a mask.

To fill in the other side of that, Str() without the 2nd and 3rd parameters is usually the same as PADL( ,10) while Transform() with no mask is Alltrim(Str()).

Use different values in comparisons and you'll get different results. <shrug>

 

[vgulielmus]

Indeed, in the subquery the type of the two combined function MIN(TRANSFORM()) is Character or Character(1).

It's somehow connected with this particular type of subquery, because the following query, not very different from the previous one, behaves normally.

SELECT * FROM cCur WHERE TRANSFORM(nField) IN ;
            (SELECT MIN(TRANSFORM(nField)) FROM cCur GROUP BY cField)

Also in my first post I showed that :

SELECT cField,MIN(transform(nField)) as nField FROM cCur GROUP BY 1 INTO cursor _2

behaves normally

while this query :

SELECT *,(SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE cCur.cField=jm.cField) FROM cCur

once again truncates the result

but the followings

SELECT *,(SELECT MIN(TRANSFORM(nField)) FROM cCur jm ) FROM cCur 
SELECT *,(SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.nField=100) FROM cCur
iii=100
SELECT *,(SELECT MIN(TRANSFORM(nField)) FROM cCur jm WHERE jm.nField=m.iii) FROM cCur

are ok

The bottom line is to be cautious when TRANSFORM(numeric) is used in subqueries and to avoid troubles it's better to format the result.


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Vilhelm-Ion,

>in the subquery the type of the two combined function MIN(TRANSFORM()) is Character or Character(1)
Yes, and why VFP decides for C(1) here is really the question.

Applying the query debugging with set step on in a function often shows a bit more than you can see from the overall query result. In a normal query VFP has to "guess" the result type needed for expressions and often enough does that from the first record, but with that explanation you would still expect C(3) from your test data.

Anyway, the overall recommendation is to choose any expression in queries in a way VFP does not need to guess or conduct or infer a type and especially the length and precision.

It's okay to use a where clause of the form WHERE TRANSFORM(value) = "10.1", as in this case the TRANSFORM is not creating a field. The TRANSFORM() in the subquery seems to be processed wrong, so that part of the sql engine of VFP has some problems, at least I'd call it a flaw, if not a bug. But we can workaround with PADL or masks, or CAST to a defined type. With numeric values you can also provide a hint for the desired precision by multiplying with 1 with the desired number of decimals, eg Select 1.0000*top from browser creates an N(13,4) field from the integer field multiplied by 1.0000. Integer could be as long as N(11) in the negative range of billion, so for 4 decimals including the decimal point you'd need N(16,4) to cover the full integer range, but at least N(13,4) is going in the right direction regarding the precision. You have best control via CAST and other transformation functions like TRANSFORM, when used in a deterministic way, eg not depending on locale settings.

Overall it's a good hint to care for such things, but the recommendation is given from time to time and therefore a problem many are already aware of. That case is very specific and I would only have thought of padding the Transform result, because the numeric type range can give varying lengths results, not because I'd expected C(1). Anyway you don't stumble upon this, if you live by this unwritten rule.

Bye, Olaf.

 

[vgulielmus]

Your interest and hints helped and inspired me. Thanks again.

Additional tests reveals that the issue is happening when 3 ingredients are present:

- A SELECT phrase with a subquery which must return a single value
- The subquery has a TRANSFORM(numeric)
- The subquery has a where clause which involves one of the master table / cursor

SET ANSI is important only if the subquery result is compared with something
MIN is irrelevant

It has something to do with initial value of a variable.
The effect is similar with the one described by this example:

CREATE CURSOR aa (ii I)
INSERT INTO aa VALUES (-1)
INSERT INTO aa VALUES (10)
SELECT IIF(ii<0,0,ii) FROM aa

Follows some relevant test I made.

1 First I investigate the relevance of the MIN() function and I saw the issue appearing even when aggregate functions are not present.

CREATE Table cTest (ii I AUTOINC,nn N(7,2))
INSERT INTO cTest (nn) VALUES (100)
INSERT INTO cTest (nn) VALUES (1.0)
INSERT INTO cTest (nn) VALUES (1.00)
INSERT INTO cTest (nn) VALUES (1.2)
INSERT INTO cTest (nn) VALUES (1.20)
INSERT INTO cTest (nn) VALUES (1.23)
INSERT INTO cTest (nn) VALUES (12)
INSERT INTO cTest (nn) VALUES (123)
INSERT INTO cTest (nn) VALUES (1234)
INSERT INTO cTest (nn) VALUES (12345)
INSERT INTO cTest (nn) VALUES (123456)
INSERT INTO cTest (nn) VALUES (1234567)
INSERT INTO cTest (nn) VALUES (12345678)
INSERT INTO cTest (nn) VALUES (123456789)
INSERT INTO cTest (nn) VALUES (1234.5)
INSERT INTO cTest (nn) VALUES (1234.56)
INSERT INTO cTest (nn) VALUES (12345.6)
INSERT INTO cTest (nn) VALUES (12345.67)
INSERT INTO cTest (nn) VALUES (123456.7)
INSERT INTO cTest (nn) VALUES (123456.78)
CLOSE DATABASES ALL 

SELECT "MIN(TRANSFORM",*,(SELECT MIN(nn) FROM cTest cT2 WHERE cT2.ii=cT1.ii),(SELECT MIN(TRANSFORM(nn)) FROM cTest cT2 WHERE cT2.ii=cT1.ii) FROM cTest cT1
SELECT "MAX(TRANSFORM",*,(SELECT MAX(nn) FROM cTest cT2 WHERE cT2.ii=cTest.ii),(SELECT MAX(TRANSFORM(nn)) FROM cTest cT2 WHERE cT2.ii=cTest.ii) FROM cTest
SELECT "TRANSFORM(MIN",*,(SELECT MIN(nn) FROM cTest cT2 WHERE cT2.ii=cTest.ii),(SELECT TRANSFORM(MIN(nn)) FROM cTest cT2 WHERE cT2.ii=cTest.ii) FROM cTest
SELECT "TRANSFORM",*,(SELECT nn FROM cTest cT2 WHERE cT2.ii=cT1.ii),(SELECT TRANSFORM(nn) FROM cTest cT2 WHERE cT2.ii=cT1.ii) FROM cTest cT1

2 Then I investigate the need of the WHERE clause. I tried queries without WHERE and with HAVING or without both WHERE and HAVING, but unfortunately VFP does not support such kind of queries.

CREATE Table cTest (ii I AUTOINC,nn N(7,2))
INSERT INTO cTest (nn) VALUES (100)
INSERT INTO cTest (nn) VALUES (1.0)
INSERT INTO cTest (nn) VALUES (1.00)
INSERT INTO cTest (nn) VALUES (1.2)
INSERT INTO cTest (nn) VALUES (1.20)
INSERT INTO cTest (nn) VALUES (1.23)
INSERT INTO cTest (nn) VALUES (12)
INSERT INTO cTest (nn) VALUES (123)
INSERT INTO cTest (nn) VALUES (1234)
INSERT INTO cTest (nn) VALUES (12345)
INSERT INTO cTest (nn) VALUES (123456)
INSERT INTO cTest (nn) VALUES (1234567)
INSERT INTO cTest (nn) VALUES (12345678)
INSERT INTO cTest (nn) VALUES (123456789)
INSERT INTO cTest (nn) VALUES (1234.5)
INSERT INTO cTest (nn) VALUES (1234.56)
INSERT INTO cTest (nn) VALUES (12345.6)
INSERT INTO cTest (nn) VALUES (12345.67)
INSERT INTO cTest (nn) VALUES (123456.7)
INSERT INTO cTest (nn) VALUES (123456.78)

SELECT "MIN(TRANSFORM+HAVING",*,(SELECT MIN(nn) FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cTest.ii),(SELECT MIN(TRANSFORM(nn)) FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cTest.ii) FROM cTest 
SELECT "MIN(TRANSFORM+HAVING",*,(SELECT ii FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cTest.ii),(SELECT TRANSFORM(ii) FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cTest.ii) FROM cTest	

SELECT "TRANSFORM(SUM - cond",*,(SELECT SUM(cT2.nn+cTest.nn) FROM cTest cT2),(SELECT TRANSFORM(SUM(cT2.nn+cTest.nn)) FROM cTest cT2) FROM cTest
SELECT "simple",*,(SELECT cT2.nn+cTest.nn FROM cTest cT2 WHERE cT2.ii=1),(SELECT TRANSFORM(cT2.nn+cTest.nn) FROM cTest cT2 WHERE cT2.ii=1) FROM cTest WHERE cTest.ii<3

at this point, the number of stars made me to understand two things:
- the behavior has something to do with initial value of a variable
- the where clause is essential
- this kind of queries are not supported by VFP, because if I close the table, the queries failed ("column ... is not found"), and if I try :

SELECT "MIN(TRANSFORM+HAVING",*,(SELECT MIN(nn) FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cT1.ii),(SELECT MIN(TRANSFORM(nn)) FROM cTest cT2 GROUP BY ii HAVING MAX(cT2.ii)=cT1.ii) FROM cTest cT1
SELECT "TRANSFORM(SUM - cond",*,(SELECT SUM(cT2.nn+cT1.nn) FROM cTest cT2),(SELECT TRANSFORM(SUM(cT2.nn+cT1.nn)) FROM cTest cT2) FROM cTest cT1
SELECT "simple",*,(SELECT cT2.nn+cT1.nn FROM cTest cT2 WHERE cT2.ii=1),(SELECT TRANSFORM(cT2.nn+cT1.nn) FROM cTest cT2 WHERE cT2.ii=1) FROM cTest cT1 WHERE cT1.ii<3

I got the error message "Alias cT1 is not found"

3 Finally I investigate if the issue appears when are involved subqueries which must return a single value :

SET ANSI ON
CREATE Table cTest (ii I AUTOINC,nn N(7,2))
INSERT INTO cTest (nn) VALUES (1234)
INSERT INTO cTest (nn) VALUES (12345.6)
CLOSE DATABASES ALL

SELECT * FROM cTest WHERE TRANSFORM(cTest.nn) IN (SELECT TRANSFORM(cT2.nn) FROM cTest cT2 WHERE ii=cTest.ii)
SELECT * FROM cTest WHERE TRANSFORM(cTest.nn) = (SELECT TRANSFORM(cT2.nn) FROM cTest cT2 WHERE ii=cTest.ii)
* The following query is the first of the previous two plus the result of the TRANSFORM(cTest.nn) and the result of the subquery 
SELECT *,TRANSFORM(cTest.nn),(SELECT TRANSFORM(cT2.nn) FROM cTest cT2 WHERE ii=cTest.ii) FROM cTest WHERE TRANSFORM(cTest.nn) IN (SELECT TRANSFORM(cT2.nn) FROM cTest cT2 WHERE ii=cTest.ii)

The last two strengthen the conclusions that the subquery must include a WHERE clause and the behavior has something to do with initial value of a variable

SELECT *,TRANSFORM(cTest.nn),(SELECT TRANSFORM(SUM(cT2.nn))+TRANSFORM(cTest.nn) FROM cTest cT2) FROM cTest 
SELECT *,(SELECT TRANSFORM(SUM(cT2.nn)+cTest.nn) FROM cTest cT2) FROM cTest WHERE TRANSFORM(cTest.nn) <= (SELECT TRANSFORM(SUM(cT2.nn)+cTest.nn) FROM cTest cT2)

P.S.
CHARACTER and CHARACTER(1) has the same effect in the CAST() function

?CAST("abc" as CHARACTER)
?CAST("abc" as CHARACTER(1))

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

I forgot to say I liked very much yoursinglestep() function.

That's odd. I can't give you a star for each reply?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

That's odd. I can't give you a star for each reply?

The system does not permit you to give more than one star to one person in one thread.

But not to worry. It's clear to us all that Olaf has gone to a lot of trouble to understand this problem (as you have yourself), and that you appreciate his help.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

Thanks.
Knowing the conditions, now it's very easy to reproduce the issue.
Here are another examples

CLEAR ALL
SET SAFETY OFF 
CREATE TABLE aa (ii I,aa C(3))
INSERT INTO aa VALUES (2,"300")
INSERT INTO aa VALUES (5,"400")

CREATE TABLE bb (ii I,bb N(7,2),cc C(3))
INSERT INTO bb VALUES (5,400,"400")
INSERT INTO bb VALUES (8,500,"500")
CLOSE DATABASES ALL 

SELECT * FROM aa WHERE aa.aa==(SELECT TRANSFORM(bb.bb) FROM bb WHERE bb.ii=aa.ii) 
SELECT *,(SELECT TRANSFORM(bb.bb) FROM bb WHERE bb.ii=aa.ii) FROM aa 


SELECT * FROM bb WHERE bb.cc==(SELECT TRANSFORM(bb2.bb) FROM bb bb2 WHERE bb2.ii=bb.ii) 
SELECT *,(SELECT TRANSFORM(bb2.bb) FROM bb bb2 WHERE bb2.ii=bb.ii) FROM bb

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

It seems that this (bad) behaviour is specific to VFP9.
Just runned the code from the previous reply in VFP6 (the first query from each pair).

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

> specific to VFP9
MOst likely, because only VFP9 allows many of the subqueries. We can assume this new feature of allowing more subqueries and in more places than ever, was added without adjusting the type infering part of the SQL engine. Nobody will fix this. I can try VFP7, but not 8. Anyway, nobody will fix this and there are workarounds. Always enforce the result type you want, don't ever use varying length expressions resulting in fields, even in intermediate results, and you're on the safe side.

Bye, Olaf.

 

[vgulielmus]

...there are workarounds

I agree, it's not a big deal. I came across it by chance, by trying to provide an alternative (and sophisticate) solution to a simple question.
But it was fun to investigate


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania