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.