You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Xiao Meng <xi...@cs.sfu.ca> on 2014/12/10 00:43:10 UTC
Varchar without length in cast expression
Hi,
I noticed that the behavior of VARCHAR in cast expression changes in recent
drillbit server. In the cast expression, VARCHAR without length specifier
will be treated as varchar(1) now.
For example, the following query:
select (1234 as VARCHAR) from sys.drillbits
returns '1' instead of '1234'.
Is this change intended?
As a reference, SQL server treated it differently:
http://msdn.microsoft.com/en-CA/library/ms176089.aspx
"(For char/varchar), when n is not specified in a data definition or
variable declaration statement, the default length is 1. When n is not
specified when using the CAST and CONVERT functions, the default length is
30"
Best,
Xiao
Re: Varchar without length in cast expression
Posted by Abhishek Girish <ag...@mapr.com>.
I hit a similar issue and filed a JIRA to track this.
https://issues.apache.org/jira/browse/DRILL-1836
On Wed, Dec 10, 2014 at 8:40 AM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> As far as we know, most apps will specify a length when using the CAST
> expression. So I think this should be Okay for us.
>
> Best,
>
> Xiao
>
> On Tue, Dec 9, 2014 at 4:19 PM, Aman Sinha <as...@maprtech.com> wrote:
>
> > Prior to DRILL-1470 fix we were not honoring the varchar length. After
> > that fix we honor the length and that is why you are seeing the change in
> > behavior. Drill gets the original logical plan from Optiq/Calcite which
> > inserts the VARCHAR(1) and so Drill just uses that value. However,
> > VARCHAR(0) will give the intended behavior:
> >
> > 0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
> > cp.`tpch/region.parquet`;
> > +------------+
> > | EXPR$0 |
> > +------------+
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > +------------+
> >
> > This seems to be vendor specific. If we want Drill's behavior to be more
> > like Postgres, you could file a JIRA and we can figure out what can be
> > done.
> >
> > On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
> >
> > > Probably a good point since Postgres shows expected result:
> > > postgres=# select '1234'::varchar(10);
> > > varchar
> > > ---------
> > > 1234
> > > (1 row)
> > >
> > > postgres=# select '1234'::varchar(1);
> > > varchar
> > > ---------
> > > 1
> > > (1 row)
> > >
> > > postgres=# select '1234'::varchar;
> > > varchar
> > > ---------
> > > 1234
> > > (1 row)
> > >
> > > Thanks,
> > > Hao
> > >
> > >
> > >
> > >
> > >
> > > On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> > >
> > > > Hi,
> > > >
> > > > I noticed that the behavior of VARCHAR in cast expression changes in
> > > recent
> > > > drillbit server. In the cast expression, VARCHAR without length
> > specifier
> > > > will be treated as varchar(1) now.
> > > >
> > > > For example, the following query:
> > > >
> > > > select (1234 as VARCHAR) from sys.drillbits
> > > >
> > > > returns '1' instead of '1234'.
> > > >
> > > > Is this change intended?
> > > >
> > > > As a reference, SQL server treated it differently:
> > > >
> > > > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> > > >
> > > > "(For char/varchar), when n is not specified in a data definition or
> > > > variable declaration statement, the default length is 1. When n is
> not
> > > > specified when using the CAST and CONVERT functions, the default
> length
> > > is
> > > > 30"
> > > > Best,
> > > >
> > > > Xiao
> > > >
> > >
> >
>
Re: Varchar without length in cast expression
Posted by Abhishek Girish <ag...@mapr.com>.
I hit a similar issue and filed a JIRA to track this.
https://issues.apache.org/jira/browse/DRILL-1836
On Wed, Dec 10, 2014 at 8:40 AM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> As far as we know, most apps will specify a length when using the CAST
> expression. So I think this should be Okay for us.
>
> Best,
>
> Xiao
>
> On Tue, Dec 9, 2014 at 4:19 PM, Aman Sinha <as...@maprtech.com> wrote:
>
> > Prior to DRILL-1470 fix we were not honoring the varchar length. After
> > that fix we honor the length and that is why you are seeing the change in
> > behavior. Drill gets the original logical plan from Optiq/Calcite which
> > inserts the VARCHAR(1) and so Drill just uses that value. However,
> > VARCHAR(0) will give the intended behavior:
> >
> > 0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
> > cp.`tpch/region.parquet`;
> > +------------+
> > | EXPR$0 |
> > +------------+
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > | 1234 |
> > +------------+
> >
> > This seems to be vendor specific. If we want Drill's behavior to be more
> > like Postgres, you could file a JIRA and we can figure out what can be
> > done.
> >
> > On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
> >
> > > Probably a good point since Postgres shows expected result:
> > > postgres=# select '1234'::varchar(10);
> > > varchar
> > > ---------
> > > 1234
> > > (1 row)
> > >
> > > postgres=# select '1234'::varchar(1);
> > > varchar
> > > ---------
> > > 1
> > > (1 row)
> > >
> > > postgres=# select '1234'::varchar;
> > > varchar
> > > ---------
> > > 1234
> > > (1 row)
> > >
> > > Thanks,
> > > Hao
> > >
> > >
> > >
> > >
> > >
> > > On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> > >
> > > > Hi,
> > > >
> > > > I noticed that the behavior of VARCHAR in cast expression changes in
> > > recent
> > > > drillbit server. In the cast expression, VARCHAR without length
> > specifier
> > > > will be treated as varchar(1) now.
> > > >
> > > > For example, the following query:
> > > >
> > > > select (1234 as VARCHAR) from sys.drillbits
> > > >
> > > > returns '1' instead of '1234'.
> > > >
> > > > Is this change intended?
> > > >
> > > > As a reference, SQL server treated it differently:
> > > >
> > > > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> > > >
> > > > "(For char/varchar), when n is not specified in a data definition or
> > > > variable declaration statement, the default length is 1. When n is
> not
> > > > specified when using the CAST and CONVERT functions, the default
> length
> > > is
> > > > 30"
> > > > Best,
> > > >
> > > > Xiao
> > > >
> > >
> >
>
Re: Varchar without length in cast expression
Posted by Xiao Meng <xi...@cs.sfu.ca>.
As far as we know, most apps will specify a length when using the CAST
expression. So I think this should be Okay for us.
Best,
Xiao
On Tue, Dec 9, 2014 at 4:19 PM, Aman Sinha <as...@maprtech.com> wrote:
> Prior to DRILL-1470 fix we were not honoring the varchar length. After
> that fix we honor the length and that is why you are seeing the change in
> behavior. Drill gets the original logical plan from Optiq/Calcite which
> inserts the VARCHAR(1) and so Drill just uses that value. However,
> VARCHAR(0) will give the intended behavior:
>
> 0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
> cp.`tpch/region.parquet`;
> +------------+
> | EXPR$0 |
> +------------+
> | 1234 |
> | 1234 |
> | 1234 |
> | 1234 |
> | 1234 |
> +------------+
>
> This seems to be vendor specific. If we want Drill's behavior to be more
> like Postgres, you could file a JIRA and we can figure out what can be
> done.
>
> On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
>
> > Probably a good point since Postgres shows expected result:
> > postgres=# select '1234'::varchar(10);
> > varchar
> > ---------
> > 1234
> > (1 row)
> >
> > postgres=# select '1234'::varchar(1);
> > varchar
> > ---------
> > 1
> > (1 row)
> >
> > postgres=# select '1234'::varchar;
> > varchar
> > ---------
> > 1234
> > (1 row)
> >
> > Thanks,
> > Hao
> >
> >
> >
> >
> >
> > On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> >
> > > Hi,
> > >
> > > I noticed that the behavior of VARCHAR in cast expression changes in
> > recent
> > > drillbit server. In the cast expression, VARCHAR without length
> specifier
> > > will be treated as varchar(1) now.
> > >
> > > For example, the following query:
> > >
> > > select (1234 as VARCHAR) from sys.drillbits
> > >
> > > returns '1' instead of '1234'.
> > >
> > > Is this change intended?
> > >
> > > As a reference, SQL server treated it differently:
> > >
> > > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> > >
> > > "(For char/varchar), when n is not specified in a data definition or
> > > variable declaration statement, the default length is 1. When n is not
> > > specified when using the CAST and CONVERT functions, the default length
> > is
> > > 30"
> > > Best,
> > >
> > > Xiao
> > >
> >
>
Re: Varchar without length in cast expression
Posted by Xiao Meng <xi...@cs.sfu.ca>.
As far as we know, most apps will specify a length when using the CAST
expression. So I think this should be Okay for us.
Best,
Xiao
On Tue, Dec 9, 2014 at 4:19 PM, Aman Sinha <as...@maprtech.com> wrote:
> Prior to DRILL-1470 fix we were not honoring the varchar length. After
> that fix we honor the length and that is why you are seeing the change in
> behavior. Drill gets the original logical plan from Optiq/Calcite which
> inserts the VARCHAR(1) and so Drill just uses that value. However,
> VARCHAR(0) will give the intended behavior:
>
> 0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
> cp.`tpch/region.parquet`;
> +------------+
> | EXPR$0 |
> +------------+
> | 1234 |
> | 1234 |
> | 1234 |
> | 1234 |
> | 1234 |
> +------------+
>
> This seems to be vendor specific. If we want Drill's behavior to be more
> like Postgres, you could file a JIRA and we can figure out what can be
> done.
>
> On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
>
> > Probably a good point since Postgres shows expected result:
> > postgres=# select '1234'::varchar(10);
> > varchar
> > ---------
> > 1234
> > (1 row)
> >
> > postgres=# select '1234'::varchar(1);
> > varchar
> > ---------
> > 1
> > (1 row)
> >
> > postgres=# select '1234'::varchar;
> > varchar
> > ---------
> > 1234
> > (1 row)
> >
> > Thanks,
> > Hao
> >
> >
> >
> >
> >
> > On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> >
> > > Hi,
> > >
> > > I noticed that the behavior of VARCHAR in cast expression changes in
> > recent
> > > drillbit server. In the cast expression, VARCHAR without length
> specifier
> > > will be treated as varchar(1) now.
> > >
> > > For example, the following query:
> > >
> > > select (1234 as VARCHAR) from sys.drillbits
> > >
> > > returns '1' instead of '1234'.
> > >
> > > Is this change intended?
> > >
> > > As a reference, SQL server treated it differently:
> > >
> > > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> > >
> > > "(For char/varchar), when n is not specified in a data definition or
> > > variable declaration statement, the default length is 1. When n is not
> > > specified when using the CAST and CONVERT functions, the default length
> > is
> > > 30"
> > > Best,
> > >
> > > Xiao
> > >
> >
>
Re: Varchar without length in cast expression
Posted by Aman Sinha <as...@maprtech.com>.
Prior to DRILL-1470 fix we were not honoring the varchar length. After
that fix we honor the length and that is why you are seeing the change in
behavior. Drill gets the original logical plan from Optiq/Calcite which
inserts the VARCHAR(1) and so Drill just uses that value. However,
VARCHAR(0) will give the intended behavior:
0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
cp.`tpch/region.parquet`;
+------------+
| EXPR$0 |
+------------+
| 1234 |
| 1234 |
| 1234 |
| 1234 |
| 1234 |
+------------+
This seems to be vendor specific. If we want Drill's behavior to be more
like Postgres, you could file a JIRA and we can figure out what can be
done.
On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
> Probably a good point since Postgres shows expected result:
> postgres=# select '1234'::varchar(10);
> varchar
> ---------
> 1234
> (1 row)
>
> postgres=# select '1234'::varchar(1);
> varchar
> ---------
> 1
> (1 row)
>
> postgres=# select '1234'::varchar;
> varchar
> ---------
> 1234
> (1 row)
>
> Thanks,
> Hao
>
>
>
>
>
> On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
>
> > Hi,
> >
> > I noticed that the behavior of VARCHAR in cast expression changes in
> recent
> > drillbit server. In the cast expression, VARCHAR without length specifier
> > will be treated as varchar(1) now.
> >
> > For example, the following query:
> >
> > select (1234 as VARCHAR) from sys.drillbits
> >
> > returns '1' instead of '1234'.
> >
> > Is this change intended?
> >
> > As a reference, SQL server treated it differently:
> >
> > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> >
> > "(For char/varchar), when n is not specified in a data definition or
> > variable declaration statement, the default length is 1. When n is not
> > specified when using the CAST and CONVERT functions, the default length
> is
> > 30"
> > Best,
> >
> > Xiao
> >
>
Re: Varchar without length in cast expression
Posted by Aman Sinha <as...@maprtech.com>.
Prior to DRILL-1470 fix we were not honoring the varchar length. After
that fix we honor the length and that is why you are seeing the change in
behavior. Drill gets the original logical plan from Optiq/Calcite which
inserts the VARCHAR(1) and so Drill just uses that value. However,
VARCHAR(0) will give the intended behavior:
0: jdbc:drill:zk=local> select cast('1234' as varchar(0)) from
cp.`tpch/region.parquet`;
+------------+
| EXPR$0 |
+------------+
| 1234 |
| 1234 |
| 1234 |
| 1234 |
| 1234 |
+------------+
This seems to be vendor specific. If we want Drill's behavior to be more
like Postgres, you could file a JIRA and we can figure out what can be
done.
On Tue, Dec 9, 2014 at 3:55 PM, Hao Zhu <hz...@maprtech.com> wrote:
> Probably a good point since Postgres shows expected result:
> postgres=# select '1234'::varchar(10);
> varchar
> ---------
> 1234
> (1 row)
>
> postgres=# select '1234'::varchar(1);
> varchar
> ---------
> 1
> (1 row)
>
> postgres=# select '1234'::varchar;
> varchar
> ---------
> 1234
> (1 row)
>
> Thanks,
> Hao
>
>
>
>
>
> On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
>
> > Hi,
> >
> > I noticed that the behavior of VARCHAR in cast expression changes in
> recent
> > drillbit server. In the cast expression, VARCHAR without length specifier
> > will be treated as varchar(1) now.
> >
> > For example, the following query:
> >
> > select (1234 as VARCHAR) from sys.drillbits
> >
> > returns '1' instead of '1234'.
> >
> > Is this change intended?
> >
> > As a reference, SQL server treated it differently:
> >
> > http://msdn.microsoft.com/en-CA/library/ms176089.aspx
> >
> > "(For char/varchar), when n is not specified in a data definition or
> > variable declaration statement, the default length is 1. When n is not
> > specified when using the CAST and CONVERT functions, the default length
> is
> > 30"
> > Best,
> >
> > Xiao
> >
>
Re: Varchar without length in cast expression
Posted by Hao Zhu <hz...@maprtech.com>.
Probably a good point since Postgres shows expected result:
postgres=# select '1234'::varchar(10);
varchar
---------
1234
(1 row)
postgres=# select '1234'::varchar(1);
varchar
---------
1
(1 row)
postgres=# select '1234'::varchar;
varchar
---------
1234
(1 row)
Thanks,
Hao
On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> Hi,
>
> I noticed that the behavior of VARCHAR in cast expression changes in recent
> drillbit server. In the cast expression, VARCHAR without length specifier
> will be treated as varchar(1) now.
>
> For example, the following query:
>
> select (1234 as VARCHAR) from sys.drillbits
>
> returns '1' instead of '1234'.
>
> Is this change intended?
>
> As a reference, SQL server treated it differently:
>
> http://msdn.microsoft.com/en-CA/library/ms176089.aspx
>
> "(For char/varchar), when n is not specified in a data definition or
> variable declaration statement, the default length is 1. When n is not
> specified when using the CAST and CONVERT functions, the default length is
> 30"
> Best,
>
> Xiao
>
Re: Varchar without length in cast expression
Posted by Hao Zhu <hz...@maprtech.com>.
Probably a good point since Postgres shows expected result:
postgres=# select '1234'::varchar(10);
varchar
---------
1234
(1 row)
postgres=# select '1234'::varchar(1);
varchar
---------
1
(1 row)
postgres=# select '1234'::varchar;
varchar
---------
1234
(1 row)
Thanks,
Hao
On Tue, Dec 9, 2014 at 3:43 PM, Xiao Meng <xi...@cs.sfu.ca> wrote:
> Hi,
>
> I noticed that the behavior of VARCHAR in cast expression changes in recent
> drillbit server. In the cast expression, VARCHAR without length specifier
> will be treated as varchar(1) now.
>
> For example, the following query:
>
> select (1234 as VARCHAR) from sys.drillbits
>
> returns '1' instead of '1234'.
>
> Is this change intended?
>
> As a reference, SQL server treated it differently:
>
> http://msdn.microsoft.com/en-CA/library/ms176089.aspx
>
> "(For char/varchar), when n is not specified in a data definition or
> variable declaration statement, the default length is 1. When n is not
> specified when using the CAST and CONVERT functions, the default length is
> 30"
> Best,
>
> Xiao
>