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.

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

SELECT statement throws str_cast exception #2847

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

SELECT statement throws str_cast exception #2847

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2011-07-29 17:17:58 +0200
From: Simon Brodt <<simon.brodt>>
To: SQL devs <>
Version: 11.3.7 (Apr2011-SP2) [obsolete]
CC: @njnes, @drstmane

Last updated: 2011-09-16 15:04:35 +0200

Comment 16058

Date: 2011-07-29 17:17:58 +0200
From: Simon Brodt <<simon.brodt>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20100101 Firefox/5.0
Build Identifier:

SELECT statement throws str_cast exception "too long for type (var)char(12)" but there are only CLOB or BIGINT attributes involved in the table definition and the query.

Reproducible: Always

Steps to Reproduce:

sql>CREATE SCHEMA "testschema";
operation successful
sql>CREATE TABLE "testschema"."test" (
more> "type" CHARACTER LARGE OBJECT NOT NULL,
more> "output" CHARACTER LARGE OBJECT NOT NULL,
more> "output_min_time_value_1" BIGINT
more>);
operation successful
sql>INSERT INTO "testschema"."test" VALUES ('OUTPUT','greet__person','3');
1 affected row (2.633ms)
sql>SELECT "output_min_time_value_1" FROM "testschema"."test" WHERE "type" = 'OUTPUT' AND "output" = 'greet_person' ;
SQLException:str_cast:value too long for type (var)char(12)

Actual Results:

SQLException

Expected Results:

3

Comment 16059

Date: 2011-07-29 17:47:54 +0200
From: @sjoerdmullender

I can reproduce this.
However, the expected result with the given query would be an empty table.
If I use the correct number of underscores (2) in the select query, I get the expected result of 3.

Comment 16060

Date: 2011-07-29 18:10:34 +0200
From: @drstmane

Problem here is that the literal in the query (here: 'greet_person' is used as reference for the string length, and then casting the (longer) value from table ('greet__person') to that shorter length fails.

In fact, I wonder why that cast is required at all to compare two strings ...

Probably there is a good reason that I just don't see, yet; however, there should not be on overflow exception; rather when the string from the table is longer than the string literal, the equality comparison is known to yield false ...

Comment 16061

Date: 2011-07-29 18:18:51 +0200
From: Simon Brodt <<simon.brodt>>

Try this sequence:

CREATE SCHEMA "testschema";
CREATE TABLE "testschema"."test" (
"type" CHARACTER LARGE OBJECT NOT NULL,
"output" CHARACTER LARGE OBJECT NOT NULL,
"output_min_time_value_1" BIGINT
);
INSERT INTO "testschema"."test" VALUES ('INPUT','see_person','3');
INSERT INTO "testschema"."test" VALUES ('WORKING','greet_person','3');
INSERT INTO "testschema"."test" VALUES ('OUTPUT','greet_person','3');
SELECT "output_min_time_value_1" FROM "testschema"."test" WHERE "type" = 'OUTPUT' AND "output" = 'greet_person' ;

Comment 16062

Date: 2011-08-01 17:28:42 +0200
From: @sjoerdmullender

Changeset 0db7113f62eb made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=0db7113f62eb

Changeset description:

Added test for bug #2847.

Comment 16063

Date: 2011-08-01 17:33:00 +0200
From: @sjoerdmullender

It's a SQL bug.

Comment 16069

Date: 2011-08-03 18:42:13 +0200
From: @njnes

fixed. The supertype function now properly returns teh super type of 2
string types (where one has no length contraints)

Comment 16243

Date: 2011-09-16 15:04:35 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant