Bug 2245575

Summary: [rgw][s3select]: wrong output seen for query "select trim(LEADING '1' from '111abcdef111') from s3object;"
Product: [Red Hat Storage] Red Hat Ceph Storage Reporter: Hemanth Sai <hmaheswa>
Component: RGWAssignee: gal salomon <gsalomon>
Status: CLOSED ERRATA QA Contact: Hemanth Sai <hmaheswa>
Severity: high Docs Contact: Rivka Pollack <rpollack>
Priority: unspecified    
Version: 7.0CC: akraj, ceph-eng-bugs, cephqe-warriors, gsalomon, mbenjamin, tserlin, vereddy
Target Milestone: ---Flags: gsalomon: needinfo-
gsalomon: needinfo-
Target Release: 7.0   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: ceph-18.2.0-127.el9cp Doc Type: Bug Fix
Doc Text:
.The `select trim (LEADING '1' from '111abcdef111') from s3object;` query now works when capitals are used in query Previously, if `LEADING` or `TRAILING` were written in all capitals, the string would not properly read, causing a float type to be referred to as a string type, thus leading to a wrong output. With this fix, type checking is introduced before completing the query, and `LEADING` and `TRAILING` work written either capitalized or in lower case.
 Story Points: ---
Clone Of: Environment:
Last Closed: 2023-12-13 15:24:32 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Hemanth Sai 2023-10-23 04:55:04 UTC 
Description of problem:
wrong output seen for query "select trim(LEADING '1' from '111abcdef111')  from s3object;"
"LEADING" and "TRAILING" in caps are removing characters from both sides

[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ aws s3api --endpoint-url http://10.0.210.182:80 select-object-content  --bucket bkt1 --key polarion_data.csv --expression-type 'SQL' --input-serialization '{"CSV": {}, "CompressionType": "NONE"}' --output-serialization '{"CSV": {}}' --expression "select trim(LEADING '1' from '111abcdef111')  from s3object;" /dev/stdout
abcdef
abcdef
abcdef
abcdef
abcdef
abcdef
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$

[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ aws s3api --endpoint-url http://10.0.210.182:80 select-object-content  --bucket bkt1 --key polarion_data.csv --expression-type 'SQL' --input-serialization '{"CSV": {}, "CompressionType": "NONE"}' --output-serialization '{"CSV": {}}' --expression "select trim(TRAILING '1' from '111abcdef111')  from s3object;" /dev/stdout
abcdef
abcdef
abcdef
abcdef
abcdef
abcdef
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ 



but "leading" and "trailing" are working fine:

[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ aws s3api --endpoint-url http://10.0.210.182:80 select-object-content  --bucket bkt1 --key polarion_data.csv --expression-type 'SQL' --input-serialization '{"CSV": {}, "CompressionType": "NONE"}' --output-serialization '{"CSV": {}}' --expression "select trim(trailing '1' from '111abcdef111')  from s3object;" /dev/stdout
111abcdef
111abcdef
111abcdef
111abcdef
111abcdef
111abcdef
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ 
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ 
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$ aws s3api --endpoint-url http://10.0.210.182:80 select-object-content  --bucket bkt1 --key polarion_data.csv --expression-type 'SQL' --input-serialization '{"CSV": {}, "CompressionType": "NONE"}' --output-serialization '{"CSV": {}}' --expression "select trim(leading '1' from '111abcdef111')  from s3object;" /dev/stdout
abcdef111
abcdef111
abcdef111
abcdef111
abcdef111
abcdef111
[cephuser@ceph-hmaheswa-reef-v0q7fq-node6 ~]$


Version-Release number of selected component (if applicable):
ceph version 18.2.0-86.el9cp

How reproducible:
always

Steps to Reproduce:
1.deploy rhcs7.0 ceph cluster
2.upload an s3select object using aws-cli
3.execute the query "select trim(LEADING '1' from '111abcdef111')  from s3object;" and verify the results

Actual results:
"LEADING" and "TRAILING" in caps are removing characters from both sides
but "leading" and "trailing" are working fine

Expected results:
should work correctly in caps as well: "LEADING", "TRAILING"

Additional info:
rgw node: 10.0.210.182
creds: root/passwd ; cephuser/cephuser
Comment 1 gal salomon 2023-11-02 07:21:12 UTC 
the issue is resolved in the following PR
https://github.com/ceph/ceph/pull/54298
Comment 2 gal salomon 2023-11-15 16:14:14 UTC 
the issue is resolved in the following PR
https://github.com/ceph/ceph/pull/54298
Comment 8 errata-xmlrpc 2023-12-13 15:24:32 UTC 
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory (Red Hat Ceph Storage 7.0 Bug Fix update), and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2023:7780
Comment 10 gal salomon 2024-02-04 10:04:18 UTC 
Rivka,
this BZ describes the fix for syntax-issue that causes a wrong output (trim operator).
(type checking is a different issue)