Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

cast(varchar as date) should allow leading and trailing spaces #10076

Closed
mbasmanova opened this issue Jun 5, 2024 · 0 comments
Closed

cast(varchar as date) should allow leading and trailing spaces #10076

mbasmanova opened this issue Jun 5, 2024 · 0 comments
Labels
bug Something isn't working triage Newly created issue that needs attention.

Comments

@mbasmanova
Copy link
Contributor

Bug description

Presto allows leading and trailing spaces. Velox allows leading spaces, but doesn't allow trailing spaces.

presto> select cast('2024-1-1 ' as date);
   _col0
------------
 2024-01-01
(1 row)

presto> select cast(' 2024-1-1' as date);
   _col0
------------
 2024-01-01
(1 row)

CC: @pedroerp @amitkdutta @kgpai @kagamiori

System information

n/a

Relevant logs

No response
@mbasmanova mbasmanova added bug Something isn't working triage Newly created issue that needs attention. labels Jun 5, 2024
mbasmanova added a commit to mbasmanova/velox-1 that referenced this issue Jun 6, 2024
@mbasmanova @facebook-github-bot
Allow trailing spaces in cast(varchar as date)
863be01 
Summary:
Presto CAST(varchar AS date) allows leading and trailing spaces. Velox used to
allow leading spaces, but not trailing spaces.

date(x) function is an alias for CAST. Update the implementation of date(x) to
match CAST.

from_iso8601_date(x) function is different from CAST. It doesn't allow leading
or trailing whitespaces, but allows partial dates, e.g. '2024', '2024-01'. Velox
used to allow leading spaces. 

Here are some examples of CAST and from_iso8601_date:

```
presto> select
        ->   x, try(from_iso8601_date(x)) as from_iso, try(cast(x as date)) as "cast"
        -> from (values
        ->   '',
        ->   '2024-01-12',
        ->   '2024-01',
        ->   '2024',
        ->   '2024-1-2',
        ->   '2024-1',
        ->   '2024',
        ->   ' 2024-1-3',
        ->   '  2024-1-3  ',
        ->   '2024-1-3   '
        -> ) as t(x)
        -> ;
      x       |  from_iso  |    cast
--------------+------------+------------
              | NULL       | NULL
 2024-01-12   | 2024-01-12 | 2024-01-12
 2024-01      | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
 2024-1-2     | 2024-01-02 | 2024-01-02
 2024-1       | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
  2024-1-3    | NULL       | 2024-01-03
   2024-1-3   | NULL       | 2024-01-03
 2024-1-3     | NULL       | 2024-01-03
(10 rows)
```

Fixes facebookincubator#10076

Fixes facebookincubator#10061

Differential Revision: D58217666
@mbasmanova mbasmanova mentioned this issue Jun 6, 2024
Closed
mbasmanova added a commit to mbasmanova/velox-1 that referenced this issue Jun 6, 2024
@mbasmanova @facebook-github-bot
Allow trailing spaces in cast(varchar as date) (facebookincubator#10077)
3986e23 
Summary:

Presto CAST(varchar AS date) allows leading and trailing spaces. Velox used to
allow leading spaces, but not trailing spaces.

date(x) function is an alias for CAST. Update the implementation of date(x) to
match CAST.

from_iso8601_date(x) function is different from CAST. It doesn't allow leading
or trailing whitespaces, but allows partial dates, e.g. '2024', '2024-01'. Velox
used to allow leading spaces. 

Here are some examples of CAST and from_iso8601_date:

```
presto> select
        ->   x, try(from_iso8601_date(x)) as from_iso, try(cast(x as date)) as "cast"
        -> from (values
        ->   '',
        ->   '2024-01-12',
        ->   '2024-01',
        ->   '2024',
        ->   '2024-1-2',
        ->   '2024-1',
        ->   '2024',
        ->   ' 2024-1-3',
        ->   '  2024-1-3  ',
        ->   '2024-1-3   '
        -> ) as t(x)
        -> ;
      x       |  from_iso  |    cast
--------------+------------+------------
              | NULL       | NULL
 2024-01-12   | 2024-01-12 | 2024-01-12
 2024-01      | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
 2024-1-2     | 2024-01-02 | 2024-01-02
 2024-1       | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
  2024-1-3    | NULL       | 2024-01-03
   2024-1-3   | NULL       | 2024-01-03
 2024-1-3     | NULL       | 2024-01-03
(10 rows)
```

Fixes facebookincubator#10076

Fixes facebookincubator#10061

Differential Revision: D58217666
Joe-Abraham pushed a commit to Joe-Abraham/velox that referenced this issue Jun 7, 2024
@mbasmanova @Joe-Abraham
Allow trailing spaces in cast(varchar as date) (facebookincubator#10077)
efabc11 
Summary:
Pull Request resolved: facebookincubator#10077

Presto CAST(varchar AS date) allows leading and trailing spaces. Velox used to
allow leading spaces, but not trailing spaces.

date(x) function is an alias for CAST. Update the implementation of date(x) to
match CAST.

from_iso8601_date(x) function is different from CAST. It doesn't allow leading
or trailing whitespaces, but allows partial dates, e.g. '2024', '2024-01'. Velox
used to allow leading spaces.

Here are some examples of CAST and from_iso8601_date:

```
presto> select
        ->   x, try(from_iso8601_date(x)) as from_iso, try(cast(x as date)) as "cast"
        -> from (values
        ->   '',
        ->   '2024-01-12',
        ->   '2024-01',
        ->   '2024',
        ->   '2024-1-2',
        ->   '2024-1',
        ->   '2024',
        ->   ' 2024-1-3',
        ->   '  2024-1-3  ',
        ->   '2024-1-3   '
        -> ) as t(x)
        -> ;
      x       |  from_iso  |    cast
--------------+------------+------------
              | NULL       | NULL
 2024-01-12   | 2024-01-12 | 2024-01-12
 2024-01      | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
 2024-1-2     | 2024-01-02 | 2024-01-02
 2024-1       | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
  2024-1-3    | NULL       | 2024-01-03
   2024-1-3   | NULL       | 2024-01-03
 2024-1-3     | NULL       | 2024-01-03
(10 rows)
```

Fixes facebookincubator#10076

Fixes facebookincubator#10061

Reviewed By: xiaoxmeng, pedroerp

Differential Revision: D58217666

fbshipit-source-id: 7e8f3e7076eb0ae8c38314ddeab2e87d20f2ed4b
Joe-Abraham pushed a commit to Joe-Abraham/velox that referenced this issue Jun 7, 2024
@mbasmanova @Joe-Abraham
Allow trailing spaces in cast(varchar as date) (facebookincubator#10077)
335c7bd 
Summary:
Pull Request resolved: facebookincubator#10077

Presto CAST(varchar AS date) allows leading and trailing spaces. Velox used to
allow leading spaces, but not trailing spaces.

date(x) function is an alias for CAST. Update the implementation of date(x) to
match CAST.

from_iso8601_date(x) function is different from CAST. It doesn't allow leading
or trailing whitespaces, but allows partial dates, e.g. '2024', '2024-01'. Velox
used to allow leading spaces.

Here are some examples of CAST and from_iso8601_date:

```
presto> select
        ->   x, try(from_iso8601_date(x)) as from_iso, try(cast(x as date)) as "cast"
        -> from (values
        ->   '',
        ->   '2024-01-12',
        ->   '2024-01',
        ->   '2024',
        ->   '2024-1-2',
        ->   '2024-1',
        ->   '2024',
        ->   ' 2024-1-3',
        ->   '  2024-1-3  ',
        ->   '2024-1-3   '
        -> ) as t(x)
        -> ;
      x       |  from_iso  |    cast
--------------+------------+------------
              | NULL       | NULL
 2024-01-12   | 2024-01-12 | 2024-01-12
 2024-01      | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
 2024-1-2     | 2024-01-02 | 2024-01-02
 2024-1       | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
  2024-1-3    | NULL       | 2024-01-03
   2024-1-3   | NULL       | 2024-01-03
 2024-1-3     | NULL       | 2024-01-03
(10 rows)
```

Fixes facebookincubator#10076

Fixes facebookincubator#10061

Reviewed By: xiaoxmeng, pedroerp

Differential Revision: D58217666

fbshipit-source-id: 7e8f3e7076eb0ae8c38314ddeab2e87d20f2ed4b
deepashreeraghu pushed a commit to deepashreeraghu/velox that referenced this issue Jun 13, 2024
@mbasmanova @Deepa8
Allow trailing spaces in cast(varchar as date) (facebookincubator#10077)
72b2f4b 
Summary:
Pull Request resolved: facebookincubator#10077

Presto CAST(varchar AS date) allows leading and trailing spaces. Velox used to
allow leading spaces, but not trailing spaces.

date(x) function is an alias for CAST. Update the implementation of date(x) to
match CAST.

from_iso8601_date(x) function is different from CAST. It doesn't allow leading
or trailing whitespaces, but allows partial dates, e.g. '2024', '2024-01'. Velox
used to allow leading spaces.

Here are some examples of CAST and from_iso8601_date:

```
presto> select
        ->   x, try(from_iso8601_date(x)) as from_iso, try(cast(x as date)) as "cast"
        -> from (values
        ->   '',
        ->   '2024-01-12',
        ->   '2024-01',
        ->   '2024',
        ->   '2024-1-2',
        ->   '2024-1',
        ->   '2024',
        ->   ' 2024-1-3',
        ->   '  2024-1-3  ',
        ->   '2024-1-3   '
        -> ) as t(x)
        -> ;
      x       |  from_iso  |    cast
--------------+------------+------------
              | NULL       | NULL
 2024-01-12   | 2024-01-12 | 2024-01-12
 2024-01      | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
 2024-1-2     | 2024-01-02 | 2024-01-02
 2024-1       | 2024-01-01 | NULL
 2024         | 2024-01-01 | NULL
  2024-1-3    | NULL       | 2024-01-03
   2024-1-3   | NULL       | 2024-01-03
 2024-1-3     | NULL       | 2024-01-03
(10 rows)
```

Fixes facebookincubator#10076

Fixes facebookincubator#10061

Reviewed By: xiaoxmeng, pedroerp

Differential Revision: D58217666

fbshipit-source-id: 7e8f3e7076eb0ae8c38314ddeab2e87d20f2ed4b
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working triage Newly created issue that needs attention.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Allow trailing spaces in cast(varchar as date) mbasmanova/velox-1
1 participant
@mbasmanova