You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by JiaTao Tao <ta...@gmail.com> on 2020/10/15 14:00:15 UTC
why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
Regards!
Aron Tao
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by JiaTao Tao <ta...@gmail.com>.
Hi Julian
I test a bit, Java/C/MySql/Spark/Hive/ support double/float mod, but pg
seems not to support this. There's no oracle in my hand, so the oracle is
not tested yet.
Regards!
Aron Tao
Julian Hyde <jh...@apache.org> 于2020年10月20日周二 上午1:33写道：
> Calcite aims to cover the SQL standard, but it is not limited to it.
>
> So, given that the MOD operator in the standard does not cover DOUBLE,
> the conversation should shift to which other DBs support it. Is there
> a consensus as to the semantics? Are there related features that we
> should do too?
>
> Julian
>
> On Mon, Oct 19, 2020 at 7:57 AM Alessandro Solimando
> <al...@gmail.com> wrote:
> >
> > Hello Aron,
> > sorry I forgot to cover the part regarding the modulo operation.
> >
> > Calcite again follows the standard on that respect, and the SQL standard
> > says:
> >
> > <modulus expression> operates on two exact numeric arguments with scale 0
> > > (zero) and returns
> > > the modulus (remainder) of the first argument divided by the second
> > > argument as an exact
> > > numeric with scale 0 (zero).
> >
> >
> > I am under the impression that you are trying to apply the modulus (mod)
> > operator over an approximate number, which is not supported as we have
> seen
> > above.
> >
> > Best regards,
> > Alessandro
> >
> > On Fri, 16 Oct 2020 at 05:02, JiaTao Tao <ta...@gmail.com> wrote:
> >
> > > Thanks, Alessandro Solimando
> > > Got it, very clear explanation.
> > > My main question here becomes that calcite doesn't support double mod.
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > Alessandro Solimando <al...@gmail.com> 于2020年10月15日周四
> > > 下午10:32写道：
> > >
> > > > Hi Aron Tao,
> > > > Calcite adheres to SQL standard in that respect (citing):
> > > >
> > > > The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are
> collectively
> > > > > referred to
> > > > > as exact numeric types.
> > > >
> > > >
> > > >
> > > > The data types FLOAT, REAL, and DOUBLE PRECISION are collectively
> > > referred
> > > > > to as approx-
> > > > > imate numeric types.
> > > >
> > > >
> > > > In practice the way we represent a number can lead to an exact
> (scale +
> > > > precision) or an approximate (base + mantissa) representation, which
> > > suits
> > > > very different needs.
> > > >
> > > > Best regards,
> > > > Alessandro
> > > >
> > > > On Thu, 15 Oct 2020 at 16:16, JiaTao Tao <ta...@gmail.com>
> wrote:
> > > >
> > > > > In calcite, you can not mod doubles. In Java, you can, in C you
> can't
> > > use
> > > > > "%" to mod doubles, but you can use fmod.
> > > > >
> > > > > Regards!
> > > > >
> > > > > Aron Tao
> > > > >
> > > > >
> > > > > JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
> > > > >
> > > > > > org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> > > > > > org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
> > > > > >
> > > > > > Regards!
> > > > > >
> > > > > > Aron Tao
> > > > > >
> > > > >
> > > >
> > >
>
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by Julian Hyde <jh...@apache.org>.
Calcite aims to cover the SQL standard, but it is not limited to it.
So, given that the MOD operator in the standard does not cover DOUBLE,
the conversation should shift to which other DBs support it. Is there
a consensus as to the semantics? Are there related features that we
should do too?
Julian
On Mon, Oct 19, 2020 at 7:57 AM Alessandro Solimando
<al...@gmail.com> wrote:
>
> Hello Aron,
> sorry I forgot to cover the part regarding the modulo operation.
>
> Calcite again follows the standard on that respect, and the SQL standard
> says:
>
> <modulus expression> operates on two exact numeric arguments with scale 0
> > (zero) and returns
> > the modulus (remainder) of the first argument divided by the second
> > argument as an exact
> > numeric with scale 0 (zero).
>
>
> I am under the impression that you are trying to apply the modulus (mod)
> operator over an approximate number, which is not supported as we have seen
> above.
>
> Best regards,
> Alessandro
>
> On Fri, 16 Oct 2020 at 05:02, JiaTao Tao <ta...@gmail.com> wrote:
>
> > Thanks, Alessandro Solimando
> > Got it, very clear explanation.
> > My main question here becomes that calcite doesn't support double mod.
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > Alessandro Solimando <al...@gmail.com> 于2020年10月15日周四
> > 下午10:32写道：
> >
> > > Hi Aron Tao,
> > > Calcite adheres to SQL standard in that respect (citing):
> > >
> > > The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are collectively
> > > > referred to
> > > > as exact numeric types.
> > >
> > >
> > >
> > > The data types FLOAT, REAL, and DOUBLE PRECISION are collectively
> > referred
> > > > to as approx-
> > > > imate numeric types.
> > >
> > >
> > > In practice the way we represent a number can lead to an exact (scale +
> > > precision) or an approximate (base + mantissa) representation, which
> > suits
> > > very different needs.
> > >
> > > Best regards,
> > > Alessandro
> > >
> > > On Thu, 15 Oct 2020 at 16:16, JiaTao Tao <ta...@gmail.com> wrote:
> > >
> > > > In calcite, you can not mod doubles. In Java, you can, in C you can't
> > use
> > > > "%" to mod doubles, but you can use fmod.
> > > >
> > > > Regards!
> > > >
> > > > Aron Tao
> > > >
> > > >
> > > > JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
> > > >
> > > > > org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> > > > > org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
> > > > >
> > > > > Regards!
> > > > >
> > > > > Aron Tao
> > > > >
> > > >
> > >
> >
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by Alessandro Solimando <al...@gmail.com>.
Hello Aron,
sorry I forgot to cover the part regarding the modulo operation.
Calcite again follows the standard on that respect, and the SQL standard
says:
<modulus expression> operates on two exact numeric arguments with scale 0
> (zero) and returns
> the modulus (remainder) of the first argument divided by the second
> argument as an exact
> numeric with scale 0 (zero).
I am under the impression that you are trying to apply the modulus (mod)
operator over an approximate number, which is not supported as we have seen
above.
Best regards,
Alessandro
On Fri, 16 Oct 2020 at 05:02, JiaTao Tao <ta...@gmail.com> wrote:
> Thanks, Alessandro Solimando
> Got it, very clear explanation.
> My main question here becomes that calcite doesn't support double mod.
>
> Regards!
>
> Aron Tao
>
>
> Alessandro Solimando <al...@gmail.com> 于2020年10月15日周四
> 下午10:32写道：
>
> > Hi Aron Tao,
> > Calcite adheres to SQL standard in that respect (citing):
> >
> > The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are collectively
> > > referred to
> > > as exact numeric types.
> >
> >
> >
> > The data types FLOAT, REAL, and DOUBLE PRECISION are collectively
> referred
> > > to as approx-
> > > imate numeric types.
> >
> >
> > In practice the way we represent a number can lead to an exact (scale +
> > precision) or an approximate (base + mantissa) representation, which
> suits
> > very different needs.
> >
> > Best regards,
> > Alessandro
> >
> > On Thu, 15 Oct 2020 at 16:16, JiaTao Tao <ta...@gmail.com> wrote:
> >
> > > In calcite, you can not mod doubles. In Java, you can, in C you can't
> use
> > > "%" to mod doubles, but you can use fmod.
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
> > >
> > > > org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> > > > org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
> > > >
> > > > Regards!
> > > >
> > > > Aron Tao
> > > >
> > >
> >
>
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by JiaTao Tao <ta...@gmail.com>.
Thanks, Alessandro Solimando
Got it, very clear explanation.
My main question here becomes that calcite doesn't support double mod.
Regards!
Aron Tao
Alessandro Solimando <al...@gmail.com> 于2020年10月15日周四
下午10:32写道：
> Hi Aron Tao,
> Calcite adheres to SQL standard in that respect (citing):
>
> The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are collectively
> > referred to
> > as exact numeric types.
>
>
>
> The data types FLOAT, REAL, and DOUBLE PRECISION are collectively referred
> > to as approx-
> > imate numeric types.
>
>
> In practice the way we represent a number can lead to an exact (scale +
> precision) or an approximate (base + mantissa) representation, which suits
> very different needs.
>
> Best regards,
> Alessandro
>
> On Thu, 15 Oct 2020 at 16:16, JiaTao Tao <ta...@gmail.com> wrote:
>
> > In calcite, you can not mod doubles. In Java, you can, in C you can't use
> > "%" to mod doubles, but you can use fmod.
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
> >
> > > org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> > > org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> >
>
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by Alessandro Solimando <al...@gmail.com>.
Hi Aron Tao,
Calcite adheres to SQL standard in that respect (citing):
The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are collectively
> referred to
> as exact numeric types.
The data types FLOAT, REAL, and DOUBLE PRECISION are collectively referred
> to as approx-
> imate numeric types.
In practice the way we represent a number can lead to an exact (scale +
precision) or an approximate (base + mantissa) representation, which suits
very different needs.
Best regards,
Alessandro
On Thu, 15 Oct 2020 at 16:16, JiaTao Tao <ta...@gmail.com> wrote:
> In calcite, you can not mod doubles. In Java, you can, in C you can't use
> "%" to mod doubles, but you can use fmod.
>
> Regards!
>
> Aron Tao
>
>
> JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
>
> > org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> > org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
> >
> > Regards!
> >
> > Aron Tao
> >
>
Re: why DECIMAL is in SqlTypeName#EXACT_TYPES but DOUBLE is in SqlTypeName#APPROX_TYPES
Posted by JiaTao Tao <ta...@gmail.com>.
In calcite, you can not mod doubles. In Java, you can, in C you can't use
"%" to mod doubles, but you can use fmod.
Regards!
Aron Tao
JiaTao Tao <ta...@gmail.com> 于2020年10月15日周四 下午10:00写道：
> org.apache.calcite.sql.type.SqlTypeName#APPROX_TYPES
> org.apache.calcite.sql.type.SqlTypeName#NUMERIC_TYPES
>
> Regards!
>
> Aron Tao
>