Non deterministic result with Table API SQL

[Flavio Pompermaier]

Hi to all,
I'm using Flink 1.6.1 and I get different results when running the same query on the same static dataset. There are times that I get a 'NaN' as result of a select field-expression, while other times I get a valid double. How is this possible?
This seems to happen only when I execute a complex query while it does not  happen when I isolate the 2 select clause causing the error (i.e.:

SELECT 

ROUND(STDDEV_POP(CAST(imposteResidue AS DOUBLE)),2),

SUM(CASE WHEN field1 IS NULL THEN 1 ELSE 0 END) 

FROM T1)

Best,

Flavio

[Timo Walther]

Hi Flavio,

do you execute this query in a batch or stream execution environment?

In any case this sounds very strange to me. But is it guarateed that it is not the fault of the connector?

Regars,
Timo


Am 31.10.18 um 14:54 schrieb Flavio Pompermaier:

Hi to all,
I'm using Flink 1.6.1 and I get different results when running the same query on the same static dataset. There are times that I get a 'NaN' as result of a select field-expression, while other times I get a valid double. How is this possible?
This seems to happen only when I execute a complex query while it does not  happen when I isolate the 2 select clause causing the error (i.e.:

SELECT 

ROUND(STDDEV_POP(CAST(imposteResidue AS DOUBLE)),2),

SUM(CASE WHEN field1 IS NULL THEN 1 ELSE 0 END) 

FROM T1)

Best,

Flavio

[Flavio Pompermaier]

I read a Parquet file from the filesystem. The input rows are always read in the same way, but results are different.

My query is very big and maybe this affects somehow the query execution:

UM(CASE WHEN isComplete(nome,sesso,cfPiva,cognome,immobili,deceduto,dataNascita,luogoNascita,impostePagate,immobiliTorino,imposteResidue,imposteDaPagare,redditoImponibile,latitudine_Ala,longitudine_Ala) THEN 1 ELSE 0 END),COUNT(DISTINCT CAST (nome AS VARCHAR)||CAST (sesso AS VARCHAR)||CAST (cfPiva AS VARCHAR)||CAST (cognome AS VARCHAR)||CAST (immobili AS VARCHAR)||CAST (deceduto AS VARCHAR)||CAST (dataNascita AS VARCHAR)||CAST (luogoNascita AS VARCHAR)||CAST (impostePagate AS VARCHAR)||CAST (immobiliTorino AS VARCHAR)||CAST (imposteResidue AS VARCHAR)||CAST (imposteDaPagare AS VARCHAR)||CAST (redditoImponibile AS VARCHAR)||CAST (latitudine_Ala AS VARCHAR)||CAST (longitudine_Ala AS VARCHAR)),COUNT(DISTINCT nome),SUM(CASE WHEN nome IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(nome AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN nome IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(nome AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(nome AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(nome AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(nome AS VARCHAR))),MAX(nome),MIN(nome),CAST(NULL AS DOUBLE),SUM(CASE WHEN nome IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT sesso),SUM(CASE WHEN sesso IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(sesso AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN sesso IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(sesso AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(sesso AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(sesso AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(sesso AS VARCHAR))),MAX(sesso),MIN(sesso),CAST(NULL AS DOUBLE),SUM(CASE WHEN sesso IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT cfPiva),SUM(CASE WHEN cfPiva IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(cfPiva AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN cfPiva IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(cfPiva AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(cfPiva AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(cfPiva AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(cfPiva AS VARCHAR))),MAX(cfPiva),MIN(cfPiva),CAST(NULL AS DOUBLE),SUM(CASE WHEN cfPiva IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT cognome),SUM(CASE WHEN cognome IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(cognome AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN cognome IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(cognome AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(cognome AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(cognome AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(cognome AS VARCHAR))),MAX(cognome),MIN(cognome),CAST(NULL AS DOUBLE),SUM(CASE WHEN cognome IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT immobili),SUM(CASE WHEN immobili IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(immobili AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN immobili IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(immobili AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(immobili AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(immobili AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(immobili AS VARCHAR))),MAX(immobili),MIN(immobili),ROUND(AVG(CAST(immobili AS DOUBLE)),2),SUM(CASE WHEN immobili IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(immobili AS DOUBLE)),2),ROUND(VAR_POP(CAST(immobili AS DOUBLE)),2),COUNT(DISTINCT deceduto),SUM(CASE WHEN deceduto IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(deceduto AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN deceduto IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(deceduto AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(deceduto AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(deceduto AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(deceduto AS VARCHAR))),MAX(deceduto),MIN(deceduto),CAST(NULL AS DOUBLE),SUM(CASE WHEN deceduto IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT dataNascita),SUM(CASE WHEN dataNascita IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(dataNascita AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN dataNascita IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(dataNascita AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(dataNascita AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(dataNascita AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(dataNascita AS VARCHAR))),MAX(dataNascita),MIN(dataNascita),CAST(NULL AS DOUBLE),SUM(CASE WHEN dataNascita IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT luogoNascita),SUM(CASE WHEN luogoNascita IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(luogoNascita AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN luogoNascita IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(luogoNascita AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(luogoNascita AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(luogoNascita AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(luogoNascita AS VARCHAR))),MAX(luogoNascita),MIN(luogoNascita),CAST(NULL AS DOUBLE),SUM(CASE WHEN luogoNascita IS NULL THEN 1 ELSE 0 END),CAST(NULL AS DOUBLE),CAST(NULL AS DOUBLE),COUNT(DISTINCT impostePagate),SUM(CASE WHEN impostePagate IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(impostePagate AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN impostePagate IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(impostePagate AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(impostePagate AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(impostePagate AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(impostePagate AS VARCHAR))),MAX(impostePagate),MIN(impostePagate),ROUND(AVG(CAST(impostePagate AS DOUBLE)),2),SUM(CASE WHEN impostePagate IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(impostePagate AS DOUBLE)),2),ROUND(VAR_POP(CAST(impostePagate AS DOUBLE)),2),COUNT(DISTINCT immobiliTorino),SUM(CASE WHEN immobiliTorino IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(immobiliTorino AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN immobiliTorino IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(immobiliTorino AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(immobiliTorino AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(immobiliTorino AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(immobiliTorino AS VARCHAR))),MAX(immobiliTorino),MIN(immobiliTorino),ROUND(AVG(CAST(immobiliTorino AS DOUBLE)),2),SUM(CASE WHEN immobiliTorino IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(immobiliTorino AS DOUBLE)),2),ROUND(VAR_POP(CAST(immobiliTorino AS DOUBLE)),2),COUNT(DISTINCT imposteResidue),SUM(CASE WHEN imposteResidue IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(imposteResidue AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN imposteResidue IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(imposteResidue AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(imposteResidue AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(imposteResidue AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(imposteResidue AS VARCHAR))),MAX(imposteResidue),MIN(imposteResidue),ROUND(AVG(CAST(imposteResidue AS DOUBLE)),2),SUM(CASE WHEN imposteResidue IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(imposteResidue AS DOUBLE)),2),ROUND(VAR_POP(CAST(imposteResidue AS DOUBLE)),2),COUNT(DISTINCT imposteDaPagare),SUM(CASE WHEN imposteDaPagare IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(imposteDaPagare AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN imposteDaPagare IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(imposteDaPagare AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(imposteDaPagare AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(imposteDaPagare AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(imposteDaPagare AS VARCHAR))),MAX(imposteDaPagare),MIN(imposteDaPagare),ROUND(AVG(CAST(imposteDaPagare AS DOUBLE)),2),SUM(CASE WHEN imposteDaPagare IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(imposteDaPagare AS DOUBLE)),2),ROUND(VAR_POP(CAST(imposteDaPagare AS DOUBLE)),2),COUNT(DISTINCT redditoImponibile),SUM(CASE WHEN redditoImponibile IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(redditoImponibile AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN redditoImponibile IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(redditoImponibile AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(redditoImponibile AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(redditoImponibile AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(redditoImponibile AS VARCHAR))),MAX(redditoImponibile),MIN(redditoImponibile),ROUND(AVG(CAST(redditoImponibile AS DOUBLE)),2),SUM(CASE WHEN redditoImponibile IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(redditoImponibile AS DOUBLE)),2),ROUND(VAR_POP(CAST(redditoImponibile AS DOUBLE)),2),COUNT(DISTINCT latitudine_Ala),SUM(CASE WHEN latitudine_Ala IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(latitudine_Ala AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN latitudine_Ala IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(latitudine_Ala AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(latitudine_Ala AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(latitudine_Ala AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(latitudine_Ala AS VARCHAR))),MAX(latitudine_Ala),MIN(latitudine_Ala),ROUND(AVG(CAST(latitudine_Ala AS DOUBLE)),2),SUM(CASE WHEN latitudine_Ala IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(latitudine_Ala AS DOUBLE)),2),ROUND(VAR_POP(CAST(latitudine_Ala AS DOUBLE)),2),COUNT(DISTINCT longitudine_Ala),SUM(CASE WHEN longitudine_Ala IS NOT NULL AND (CHAR_LENGTH (TRIM(CAST(longitudine_Ala AS VARCHAR))) = 0 ) THEN 1 ELSE 0 END),SUM(CASE WHEN longitudine_Ala IS NOT NULL AND ( CHAR_LENGTH (TRIM(CAST(longitudine_Ala AS VARCHAR))) > 0 ) THEN 1 ELSE 0 END),MAX(CHAR_LENGTH(CAST(longitudine_Ala AS VARCHAR))),ROUND(AVG(CAST (CHAR_LENGTH(CAST(longitudine_Ala AS VARCHAR)) AS DOUBLE)),2),MIN(CHAR_LENGTH(CAST(longitudine_Ala AS VARCHAR))),MAX(longitudine_Ala),MIN(longitudine_Ala),ROUND(AVG(CAST(longitudine_Ala AS DOUBLE)),2),SUM(CASE WHEN longitudine_Ala IS NULL THEN 1 ELSE 0 END),ROUND(STDDEV_POP(CAST(longitudine_Ala AS DOUBLE)),2),ROUND(VAR_POP(CAST(longitudine_Ala AS DOUBLE)),2),SUM(CASE WHEN isBoolean(CAST(nome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(nome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(nome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(nome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(nome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(sesso AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(sesso AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(sesso AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(sesso AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(sesso AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(cfPiva AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(cfPiva AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(cfPiva AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(cfPiva AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(cfPiva AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(cognome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(cognome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(cognome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(cognome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(cognome AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(immobili AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(immobili AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(immobili AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(immobili AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(immobili AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(deceduto AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(deceduto AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(deceduto AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(deceduto AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(deceduto AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(dataNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(dataNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(dataNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(dataNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(dataNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(luogoNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(luogoNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(luogoNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(luogoNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(luogoNascita AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(impostePagate AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(impostePagate AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(impostePagate AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(impostePagate AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(impostePagate AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(immobiliTorino AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(immobiliTorino AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(immobiliTorino AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(immobiliTorino AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(immobiliTorino AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(imposteResidue AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(imposteResidue AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(imposteResidue AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(imposteResidue AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(imposteResidue AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(imposteDaPagare AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(imposteDaPagare AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(imposteDaPagare AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(imposteDaPagare AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(imposteDaPagare AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(redditoImponibile AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(redditoImponibile AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(redditoImponibile AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(redditoImponibile AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(redditoImponibile AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(latitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(latitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(latitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(latitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(latitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isBoolean(CAST(longitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDouble(CAST(longitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isInteger(CAST(longitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isLong(CAST(longitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END),SUM(CASE WHEN isDate(CAST(longitudine_Ala AS VARCHAR)) THEN 1 ELSE 0 END)

Best,

Flavio

On Wed, Oct 31, 2018 at 3:05 PM Timo Walther <[hidden email]> wrote:

Hi Flavio,

do you execute this query in a batch or stream execution environment?

In any case this sounds very strange to me. But is it guarateed that it is not the fault of the connector?

Regars,
Timo


Am 31.10.18 um 14:54 schrieb Flavio Pompermaier:

Hi to all,
I'm using Flink 1.6.1 and I get different results when running the same query on the same static dataset. There are times that I get a 'NaN' as result of a select field-expression, while other times I get a valid double. How is this possible?
This seems to happen only when I execute a complex query while it does not  happen when I isolate the 2 select clause causing the error (i.e.:

SELECT 

ROUND(STDDEV_POP(CAST(imposteResidue AS DOUBLE)),2),

SUM(CASE WHEN field1 IS NULL THEN 1 ELSE 0 END) 

FROM T1)

Best,

Flavio

[Flavio Pompermaier]

Adding more rows to the dataset lead to a deterministic error. My tests says that the problem arise when adding the STDDEV_POP to the query..

Do you think it could be possible that there's a concurrency problem in its implementation?

[Timo Walther]

As far as I know STDDEV_POP is translated into basic aggregate functions
(SUM/AVG/COUNT). But if this error is reproducible in a little test
case, we should definitely track this in JIRA.


Am 31.10.18 um 16:43 schrieb Flavio Pompermaier:
> Adding more rows to the dataset lead to a deterministic error. My
> tests says that the problem arise when adding the STDDEV_POP to the
> query..
> Do you think it could be possible that there's a concurrency problem
> in its implementation?
>

[Flavio Pompermaier]

Here it is the JIRA ticket and, attached to if, the Flink (Java) job to reproduce the error: https://issues.apache.org/jira/browse/FLINK-10795

On Wed, Oct 31, 2018 at 4:46 PM Timo Walther <[hidden email]> wrote:

As far as I know STDDEV_POP is translated into basic aggregate functions
(SUM/AVG/COUNT). But if this error is reproducible in a little test
case, we should definitely track this in JIRA.


Am 31.10.18 um 16:43 schrieb Flavio Pompermaier:
> Adding more rows to the dataset lead to a deterministic error. My
> tests says that the problem arise when adding the STDDEV_POP to the
> query..
> Do you think it could be possible that there's a concurrency problem
> in its implementation?
>

[Fabian Hueske-2]

Thanks Flavio for reporting the error helping to debug it.

A job to reproduce the error is very valuable :-)

Best, Fabian

Am Mo., 5. Nov. 2018 um 14:38 Uhr schrieb Flavio Pompermaier <[hidden email]>:

Here it is the JIRA ticket and, attached to if, the Flink (Java) job to reproduce the error: https://issues.apache.org/jira/browse/FLINK-10795

On Wed, Oct 31, 2018 at 4:46 PM Timo Walther <[hidden email]> wrote:

As far as I know STDDEV_POP is translated into basic aggregate functions
(SUM/AVG/COUNT). But if this error is reproducible in a little test
case, we should definitely track this in JIRA.


Am 31.10.18 um 16:43 schrieb Flavio Pompermaier:
> Adding more rows to the dataset lead to a deterministic error. My
> tests says that the problem arise when adding the STDDEV_POP to the
> query..
> Do you think it could be possible that there's a concurrency problem
> in its implementation?
>