search

aws / aws-tools-for-powershell

The AWS Tools for PowerShell lets developers and administrators manage their AWS services from the PowerShell scripting environment.
Apache License 2.0
235 stars 77 forks source link

AWS.Tools.RedshiftDataAPIService: problem with bool and float4 colunms #280

Closed dburtsev closed 1 year ago

dburtsev commented 1 year ago

Describe the bug

Hello When I run the query SELECT TRUE AS clmn1, CAST(2.2 AS FLOAT4) AS clmn2, CAST(2.3 AS FLOAT) AS clmn3; I got the wrong result for bool and float4 columns

Expected Behavior

True, 2.2, 2.3

Current Behavior

False, 0, 2.3,

Reproduction Steps

$ErrorActionPreference="Stop"
Set-PSDebug -Strict

If (-Not(Get-Module -Name AWS.Tools.RedshiftDataAPIService))
    {
        Import-Module -Name AWS.Tools.RedshiftDataAPIService
    }

[string]$ClusterIdentifier = 'qwe'
[string]$database = 'qwe'
[string]$user = 'qwe'
[string]$sql = 'SELECT TRUE AS clmn1, CAST(2.2 AS FLOAT4) AS clmn2, CAST(2.3 AS FLOAT) AS clmn3;' 
[Amazon.RedshiftDataAPIService.Model.ExecuteStatementResponse]$Response1 = Send-RSDStatement -ClusterIdentifier $ClusterIdentifier -Database $database -DbUser $user -Sql $sql -Select '*'
[Amazon.RedshiftDataAPIService.Model.DescribeStatementResponse]$Response2 = Get-RSDStatement -Id $Response1.Id
[string]$status = $Response2.Status
while ($status -in 'PICKED','STARTED','SUBMITTED') {
    $Response2 = Get-RSDStatement -Id $Response1.Id
    $status = $Response2.Status
}
if($status -ne 'FINISHED') {
    throw ("Expect FINISHED got " + $status) 
}
[Amazon.RedshiftDataAPIService.Model.GetStatementResultResponse]$Response3 = Get-RSDStatementResult -Id $Response1.Id
[System.Collections.Generic.List[Amazon.RedshiftDataAPIService.Model.ColumnMetadata]]$Columns = $Response3.ColumnMetadata
$cc = $Columns.Count
Write-Host ("we have {0} colums" -f $cc)
for($i = 0; $i -lt $cc; $i++) {
    Write-Host -NoNewline ($Columns[$i].Name + ' ')
    Write-Host -NoNewline ($Columns[$i].TypeName + ' ')
}
Write-Host ([Environment]::NewLine)
[System.Collections.Generic.List[System.Collections.Generic.List[Amazon.RedshiftDataAPIService.Model.Field]]]$Records = $Response3.Records
foreach($Record in $Records) {
    # get num of colums
    for($i = 0; $i -lt $Record.Count; $i++){
        if($Record[$i].IsNull) { Write-Host -NoNewline 'NULL'}
        else {
            Switch($Columns[$i].TypeName)
            {
                {'int2','int','int4','int8' -eq $_} { Write-Host -NoNewline (($Record[$i].LongValue).ToString() + ', '); break }
                {'boolean','bool' -eq $_} { Write-Host -NoNewline (($Record[$i].BooleanValue).ToString() + ', '); break }
                {'real','float','float4','float8','double' -eq $_} {Write-Host -NoNewline (($Record[$i].DoubleValue).ToString() + ', '); break}
                default {Write-Host -NoNewline ($Record[$i].StringValue + ', '); break}
            }
        }          
    }
Write-Host ""
}

Possible Solution

No response

Additional Information/Context

No response

AWS Tools for PowerShell version used

AWS.Tools.RedshiftDataAPIService version 4.1.135.0

PowerShell version used

Name Value

PSVersion 7.2.5 PSEdition Core GitCommitId 7.2.5 OS Microsoft Windows 10.0.19042 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

Operating System and version

Windows 10

ashishdhingra commented 1 year ago

@dburtsev Good afternoon. Thanks for reporting the issue. The issue appears to be reproducible. When I examine the records returned, it shows the following:

BlobValue    : 
BooleanValue : False
DoubleValue  : 0
IsNull       : False
LongValue    : 0
StringValue  : true

BlobValue    : 
BooleanValue : False
DoubleValue  : 0
IsNull       : False
LongValue    : 0
StringValue  : 2.2

BlobValue    : 
BooleanValue : False
DoubleValue  : 2.3
IsNull       : False
LongValue    : 0
StringValue  :

So looks like the values for 1st and 2nd columns are returned as StringValue. This is resulting your logic to print incorrect values for the 1st two columns.

I followed Response Logging in AWS Tools for Windows PowerShell to enable response logging. Here is the log:

Amazon Information: 0 : Resolved DefaultConfigurationMode for RegionEndpoint [us-west-2] to [Legacy].
Amazon Information: 0 : Starting a process with the following ProcessInfo: UseShellExecute - False RedirectStandardError - True, RedirectStandardOutput - True, CreateNoWindow - True
Amazon Information: 1 : Process started
Amazon Information: 0 : Process ends with exitcode - 0
Amazon Verbose: 0 : Received response (truncated to 1024 bytes): [{"ClusterIdentifier":"redshift-cluster-1","CreatedAt":1.659657772149E9,"Database":"dev","DbUser":"awsuser","Id":"77855ecb-f8e5-4930-811c-2e068c0b8ba6"}]
Amazon Information: 1 : Request metrics: AsyncCall = True; CanonicalRequest = POST\n/\n\ncontent-type:application/x-amz-json-1.1\nhost:redshift-data.us-west-2.amazonaws.com\nuser-agent:AWSPowerShell.Common/4.1.141.0 .NET_Core/5.0.4 OS/Darwin_21.5.0_Darwin_Kernel_Version_21.5.0:_Tue_Apr_26_21:08:22_PDT_2022;_root:xnu-8020.121.3~4/RELEASE_X86_64 PowerShellCore/7.-1 ClientAsync\nx-amz-api-version:2019-12-20\nx-amz-content-sha256:4d60ea440d7d573b5215e9d5fed60bd79e4a90c810634a8792a5d38cb2905639\nx-amz-date:20220805T000251Z\nx-amz-security-token:IQoJb3JpZ2luX2VjEFgaCXVzLWVhc3QtMSJHMEUCIQDteBNiCj5BqHAAzQ1+ZEKugniAVhl82D+NV0bYr1961QIgPr7CIZbg33eBq5N6oGcOFLVMrX1bdFVsWCw2kYDEkzIqpgIIsf//////////ARABGgwxMzk0ODA2MDI5ODMiDGibtlKcQboTKtELeCr6ATGUd2t4lFkwzGkeQcLHgqTqucau7YuyTb8fvVBifoCPJ5j7eMj8pKLvWovYEMcmO1AbjBmP2LXYBaNfZWpt0yQMBg8xmKfrwikuuznvcn8bFOZG9QJWdUUfgfW/DKxCgJgaFLo7LPix9kzr7bO/mNWDMCPOneySF4el4E39LcQF0J7EnvtSV5Y4bhrLx090wHVddxcmFbj/2fAP6RBKpww7dVIOzZVHpPZlRsbLTv1938b7VxJ+BswsUg8JekdxqBoNYSYQZU5tez4FQ00sIeOK3FdTLBJXCQPN6g6Mlz5N+qhG3T39Yoe+YSHQtCdnhE5uEJTh+z+3Ujswq7yxlwY6nQEEEqfwk+sY8eC2JYoFKIrHavDJkSDyoJSxhA922Ch6XjGpxQtDs1LyQV5xKPIrT9Lox6WUKaWuShfJDRysiRk3XPmtnpyayF5d5echKOS2FHQMnjWP5SEH+9jL3zUwXUXZzZL4VVjkLDrY3Kep9bFlPga3KDJn/7GgIQNOvfozYa8ti/CydsmPhXI8tqazNdy/rt+K+CaoNq/uX2eN\nx-amz-target:RedshiftData.ExecuteStatement\n\ncontent-type;host;user-agent;x-amz-api-version;x-amz-content-sha256;x-amz-date;x-amz-security-token;x-amz-target\n4d60ea440d7d573b5215e9d5fed60bd79e4a90c810634a8792a5d38cb2905639; StringToSign = AWS4-HMAC-SHA256\n20220805T000251Z\n20220805/us-west-2/redshift-data/aws4_request\n4eabc76a6aa16a4f8b3dce25abb1129408ab5a570df4a0a1d60a5bee70c3f6db; ServiceName = Amazon.RedshiftDataAPIService; ServiceEndpoint = https://redshift-data.us-west-2.amazonaws.com/; MethodName = ExecuteStatementRequest; RequestSize = 167; StatusCode = OK; BytesProcessed = 151; AWSRequestID = 490c0cd9-8c30-420e-965b-5363f914eed8; CredentialsRequestTime = 00:00:11.6660911; RequestSigningTime = 00:00:00.0002002; HttpRequestTime = 00:00:00.4806224; ResponseUnmarshallTime = 00:00:00.0001507; ResponseProcessingTime = 00:00:00.0004880; ClientExecuteTime = 00:00:12.1483281; 
Amazon Information: 1 : Resolved DefaultConfigurationMode for RegionEndpoint [us-west-2] to [Legacy].
Amazon Information: 2 : Starting a process with the following ProcessInfo: UseShellExecute - False RedirectStandardError - True, RedirectStandardOutput - True, CreateNoWindow - True
Amazon Information: 3 : Process started
Amazon Information: 1 : Process ends with exitcode - 0
Amazon Verbose: 2 : Received response (truncated to 1024 bytes): [{"ClusterIdentifier":"redshift-cluster-1","CreatedAt":1.659657772149E9,"Duration":6544293,"HasResultSet":true,"Id":"77855ecb-f8e5-4930-811c-2e068c0b8ba6","QueryString":"SELECT TRUE AS clmn1, CAST(2.2 AS FLOAT4) AS clmn2, CAST(2.3 AS FLOAT) AS clmn3;","RedshiftPid":1073873137,"RedshiftQueryId":-1,"ResultRows":1,"ResultSize":32,"Status":"FINISHED","UpdatedAt":1.659657772673E9}]
Amazon Information: 3 : Request metrics: AsyncCall = True; CanonicalRequest = POST\n/\n\ncontent-type:application/x-amz-json-1.1\nhost:redshift-data.us-west-2.amazonaws.com\nuser-agent:AWSPowerShell.Common/4.1.141.0 .NET_Core/5.0.4 OS/Darwin_21.5.0_Darwin_Kernel_Version_21.5.0:_Tue_Apr_26_21:08:22_PDT_2022;_root:xnu-8020.121.3~4/RELEASE_X86_64 PowerShellCore/7.-1 ClientAsync\nx-amz-api-version:2019-12-20\nx-amz-content-sha256:7bd2c5faf6d546b93e5c796be2c3ddca1f46e9aa442ac230e9d1760298f74261\nx-amz-date:20220805T000310Z\nx-amz-security-token:IQoJb3JpZ2luX2VjEFgaCXVzLWVhc3QtMSJHMEUCIAUrkyUOweb4TVt9olJt1XdCCBg93tsyvFZb+k2zB1fFAiEA/CxqrNwfEjuVA/enrbP6+Mug1Ms0ALGaxHk6Vp4x/5sqpgIIsf//////////ARABGgwxMzk0ODA2MDI5ODMiDBxvPORi+rLK+uZmgyr6AfY9RLCuDzMRb0dSBEWLHtkeCWcv8WGuA8FFvC7PVEzcxmGsUCEeCq0TPboXCsrq3h9fodcHlH+0fRhdpHzQ5TxES51VVs+qApstIqeIsHILMWlIu+X+SSQp6BDKUc7kVbF+d/1ME+g0p9A3vrkcr0afgGnBgWtVTRGAle6unGvYM0U8iAWzNPCrVonUkL6K3VUhMLsxNl0s/JD54G+n4enfPeIZg4yQxlEg4Roe98NT880NTLb0O4Uib4eP7ksvXpzgQ1JCVtUIiKsppRKAd5eQw3WqCrfABV95eciioEo6IgdGqSUPaiuzVEmDcAwSRmzgcsbLD6ckgsEwvryxlwY6nQH7dorOyBI8ixZLXfZbey3Em/H2pPZxKsqoY3wLjPBSFSPS7o1VbqQpnCEagF+LBpXHxXTb1VTDLU2R+Szgr9QHKSa211c7mzn6NnWIWPtg+xzE4VvVXryF76c1aeHa7fSVdfN3gMnutXXkvLkm/+16ook74jLzjM5WwNwviTfpcqUhr1Dhe2N4K2Wz8HEfoKOkgQLURXBsp2alDHeO\nx-amz-target:RedshiftData.DescribeStatement\n\ncontent-type;host;user-agent;x-amz-api-version;x-amz-content-sha256;x-amz-date;x-amz-security-token;x-amz-target\n7bd2c5faf6d546b93e5c796be2c3ddca1f46e9aa442ac230e9d1760298f74261; StringToSign = AWS4-HMAC-SHA256\n20220805T000310Z\n20220805/us-west-2/redshift-data/aws4_request\nfa831963ee696928b45444172d650d3eb0b2e92db2c6115909da1403f1635c72; ServiceName = Amazon.RedshiftDataAPIService; ServiceEndpoint = https://redshift-data.us-west-2.amazonaws.com/; MethodName = DescribeStatementRequest; RequestSize = 45; StatusCode = OK; BytesProcessed = 377; AWSRequestID = 498a8451-8da4-4d8b-a864-7a3762480c64; CredentialsRequestTime = 00:00:11.7090901; RequestSigningTime = 00:00:00.0002619; HttpRequestTime = 00:00:00.3007802; ResponseUnmarshallTime = 00:00:00.0001519; ResponseProcessingTime = 00:00:00.0004330; ClientExecuteTime = 00:00:12.0111357; 
Amazon Information: 2 : Resolved DefaultConfigurationMode for RegionEndpoint [us-west-2] to [Legacy].
Amazon Information: 4 : Starting a process with the following ProcessInfo: UseShellExecute - False RedirectStandardError - True, RedirectStandardOutput - True, CreateNoWindow - True
Amazon Information: 5 : Process started
Amazon Information: 2 : Process ends with exitcode - 0
Amazon Verbose: 4 : Received response (truncated to 1024 bytes): [{"ColumnMetadata":[{"isCaseSensitive":false,"isCurrency":false,"isSigned":false,"label":"clmn1","length":0,"name":"clmn1","nullable":1,"precision":1,"scale":0,"schemaName":"","tableName":"","typeName":"bool"},{"isCaseSensitive":false,"isCurrency":false,"isSigned":true,"label":"clmn2","length":0,"name":"clmn2","nullable":1,"precision":8,"scale":8,"schemaName":"","tableName":"","typeName":"float4"},{"isCaseSensitive":false,"isCurrency":false,"isSigned":true,"label":"clmn3","length":0,"name":"clmn3","nullable":1,"precision":17,"scale":17,"schemaName":"","tableName":"","typeName":"float8"}],"Records":[[{"stringValue":"true"},{"stringValue":"2.2"},{"doubleValue":2.3}]],"TotalNumRows":1}]
Amazon Information: 5 : Request metrics: AsyncCall = True; CanonicalRequest = POST\n/\n\ncontent-type:application/x-amz-json-1.1\nhost:redshift-data.us-west-2.amazonaws.com\nuser-agent:AWSPowerShell.Common/4.1.141.0 .NET_Core/5.0.4 OS/Darwin_21.5.0_Darwin_Kernel_Version_21.5.0:_Tue_Apr_26_21:08:22_PDT_2022;_root:xnu-8020.121.3~4/RELEASE_X86_64 PowerShellCore/7.-1 ClientAsync\nx-amz-api-version:2019-12-20\nx-amz-content-sha256:7bd2c5faf6d546b93e5c796be2c3ddca1f46e9aa442ac230e9d1760298f74261\nx-amz-date:20220805T000331Z\nx-amz-security-token:IQoJb3JpZ2luX2VjEFgaCXVzLWVhc3QtMSJIMEYCIQD33cNN9v57qoeh+PEXvP/UVa5bxCvXd8+YpnUle0sIGAIhAI3h8A6ZgifJF7z+TG0McdS9Wr40c3sLtO7/tim72wh2KqYCCLH//////////wEQARoMMTM5NDgwNjAyOTgzIgwp5DTLdOmKfhVlPYgq+gEYLiyyNsPmbEZDbMNnRLjfFMsK44cGhPmrTuFvE/D25dusP7MoXsOangjSe7dBk6UEI1/mjK/x/FY4tluYpfNe1rK1piLTqkktAdRbTuxpCHO+sQGM7bVnkOvR4Q6AlC0RBv3DhRvVTp0ekg4at7tlyBqEczJNR96VhlWXuRdNXwnV+7rl1CAyUPxAALVrtg7b8IDj5CSi6cQon1iwLOUfGNB8KUZpQ2+yIUy5h57+NYIZ70oG//pxKiiXy1GmaleDsu4V+EVjwoq6r7NfKU9qWJU+58KLVTavjKAKCQcDd7NYGwAcGaLTIG5+1yYIZVxRdZH6Jx2iPVG/MNO8sZcGOpwB53Mjbpyzxe3ExI1KeRauwifeyX7SWN4GHipjlU8B9bHQirOYx4S4TaThWbfudmp8sEBlM0H9eAxy3pQP9Uy/NqsKSwfaFuay3UB6QAUEr9KzAN7kmFS+Wik0L05ChYTQ3ZcuJjAf8rwNvtBJWg7u0opX9lQ/JxJi0cTWRo8XngHKpjRIvLZIKiNoAndwUoDJSzgyeSuoIy/X5Zm4\nx-amz-target:RedshiftData.GetStatementResult\n\ncontent-type;host;user-agent;x-amz-api-version;x-amz-content-sha256;x-amz-date;x-amz-security-token;x-amz-target\n7bd2c5faf6d546b93e5c796be2c3ddca1f46e9aa442ac230e9d1760298f74261; StringToSign = AWS4-HMAC-SHA256\n20220805T000331Z\n20220805/us-west-2/redshift-data/aws4_request\n2330786066c066979331a1fef7f449095b27496d9b7fec7b368c1df97eea429e; ServiceName = Amazon.RedshiftDataAPIService; ServiceEndpoint = https://redshift-data.us-west-2.amazonaws.com/; MethodName = GetStatementResultRequest; RequestSize = 45; StatusCode = OK; BytesProcessed = 690; AWSRequestID = c368eee2-f6d6-4a70-8f5f-35f82732c747; CredentialsRequestTime = 00:00:13.6789315; RequestSigningTime = 00:00:00.0001947; HttpRequestTime = 00:00:00.3231847; ResponseUnmarshallTime = 00:00:00.0002145; ResponseProcessingTime = 00:00:00.0004347; ClientExecuteTime = 00:00:14.0033798;

If you see the JSON response:

[
    {
        "ColumnMetadata": [
            {
                "isCaseSensitive": false,
                "isCurrency": false,
                "isSigned": false,
                "label": "clmn1",
                "length": 0,
                "name": "clmn1",
                "nullable": 1,
                "precision": 1,
                "scale": 0,
                "schemaName": "",
                "tableName": "",
                "typeName": "bool"
            },
            {
                "isCaseSensitive": false,
                "isCurrency": false,
                "isSigned": true,
                "label": "clmn2",
                "length": 0,
                "name": "clmn2",
                "nullable": 1,
                "precision": 8,
                "scale": 8,
                "schemaName": "",
                "tableName": "",
                "typeName": "float4"
            },
            {
                "isCaseSensitive": false,
                "isCurrency": false,
                "isSigned": true,
                "label": "clmn3",
                "length": 0,
                "name": "clmn3",
                "nullable": 1,
                "precision": 17,
                "scale": 17,
                "schemaName": "",
                "tableName": "",
                "typeName": "float8"
            }
        ],
        "Records": [
            [
                {
                    "stringValue": "true"
                },
                {
                    "stringValue": "2.2"
                },
                {
                    "doubleValue": 2.3
                }
            ]
        ],
        "TotalNumRows": 1
    }
]

The records are returned as appropriate values by the service. Hence, this appears to be Service API issue. I will try to follow up with service team for more details about the above output.

References:

Thanks, Ashish

ashishdhingra commented 1 year ago

P69276557

ashishdhingra commented 1 year ago

@dburtsev The fix is implemented by the service team. Please feel free to validate. Closing this ticket.

github-actions[bot] commented 1 year ago

⚠️COMMENT VISIBILITY WARNING⚠️

Comments on closed issues are hard for our team to see. If you need more assistance, please either tag a team member or open a new issue that references this one. If you wish to keep having a conversation with other community members under this issue feel free to do so.