Good point. It is because in case the expression is evaluated after the data is materialized (yours) and the other the expression is evaluated at the same time the data is materialized (mine). In the case that they are evaluated simultaneously, we're treating as an INT until the data gets to big. As such, my query works, yours doesn't, and neither does mine once the value gets beyond the data type capacity:
SELECT STRING_BINARY(CONVERT_TO(2147483648, 'INT')) as i from (VALUES (1)); I'm not sure if this is a bug or a feature. On Wed, Jul 22, 2015 at 5:00 PM, Ted Dunning <ted.dunn...@gmail.com> wrote: > On Wed, Jul 22, 2015 at 4:51 PM, Jacques Nadeau <jacq...@dremio.com> > wrote: > > > SELECT > > STRING_BINARY(CONVERT_TO(1, 'INT')) as i, > > STRING_BINARY(CONVERT_TO(1, 'INT_BE')) as i_be, > > STRING_BINARY(CONVERT_TO(1, 'BIGINT')) as l, > > STRING_BINARY(CONVERT_TO(1, 'BIGINT')) as l_be, > > STRING_BINARY(CONVERT_TO(1, 'INT_HADOOPV')) as l_be > > from (VALUES (1)); > > > > > -------------------+-------------------+-----------------------------------+-----------------------------------+--------+ > > | i | i_be | l > > | l_be | l_be0 | > > > > > +-------------------+-------------------+-----------------------------------+-----------------------------------+--------+ > > | \x01\x00\x00\x00 | \x00\x00\x00\x01 | > \x01\x00\x00\x00\x00\x00\x00\x00 > > | \x01\x00\x00\x00\x00\x00\x00\x00 | \x01 | > > > > > +-------------------+-------------------+-----------------------------------+-----------------------------------+--------+ > > > > ... > > > > >select convert_to(x, 'INT') from (values 1000) tbl(x); > > > > The problem here is that Drill treats all number literals as BIGINT. > > You're trying to convert to a four byte encoding. This doesn't work > since > > your input type requires eight bytes of precision. You either need to > cast > > to lower precision or change your encoding to BIGINT or some other large > > precision encoding such as BIGINT_HADOOPV or BIGINT_BE. > > > > So why does your convert_to(1,'int') work and my convert_to(x,'int') fail? >
