Bad error messages on SELECT ? FROM [CORE218]

[firebird-automations]

Submitted by: askln (askln)

SFID: 796675#⁠
Submitted By: askln

Given

CREATE TABLE t (id INTEGER);

the following statements give corresponding errors:

1) SELECT ? FROM t => Data type unknown
2) SELECT id + ? FROM t => expression evaluation not
supported
3) SELECT id * ? FROM t => Array/BLOB/DATE data types
not allowed in arithmetic

While 1) is technically correct, it is misleading in a
sense that user believes that something like SELECT
CAST(? AS INTEGER) might work, which of course does not :)
So I think that only the second message is correct.

Tested with FB 1.0.3 and 1.5RC5 on Windows, using IBX
and IBO.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-12-12 18:46
Sender: dimitr
Logged In: YES
user_id=61270

"expression evaluation not supported" actually means that an
expression cannot be evaluated for the given parameter
types, so this is pretty the same as a type mismatch and IMO
your example "select 'a' + 1" returned a correct error message.

Now about your first examples. Provided that CAST could work
for parameters, which message would you consider appropriate
for expressions containing parameters: "Datatype unknown" or
"Expression evaluation not supported"? Formally, they're
both correct. As soon as we agree on a proper message, I'll
fix the code. Now we have a complete mess of types checking
during prepare, compare e.g. "select max(?) from
rdb$database" and "select sum(?) from rdb$database".

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2003-08-28 19:06
Sender: askln
Logged In: YES
user_id=757561

Upon further experiments, found more incorrect messages /
inconsistent behaviour:

4) SELECT 'a' + 1 FROM t => expression evaluation not supported
(should be "type mismatch")

CREATE TABLE t1 (id INTEGER, s VARCHAR(100));

5) SELECT * FROM t1 WHERE s=1 => ok (1 gets converted to string)
6) SELECT * FROM t1 WHERE 'a'=1 => conversion error from string "a" (it would be logical that 1 would convert to string also)
7) SELECT * FROM t1 WHERE s||''=1 => Cannot transliterate character between character sets (what gives?)
8) SELECT * FROM t1 WHERE s=id => Cannot transliterate character between character sets
9) SELECT * FROM t1 WHERE s=id||'' => ok (!) (double-negative gives positive, I suppose ;))

[firebird-automations]

Modified by: Alice F. Bird (firebirds)

description: SFID: 796675#⁠
Submitted By: askln

Given

CREATE TABLE t (id INTEGER);

the following statements give corresponding errors:

1) SELECT ? FROM t => Data type unknown
2) SELECT id + ? FROM t => expression evaluation not
supported
3) SELECT id * ? FROM t => Array/BLOB/DATE data types
not allowed in arithmetic

While 1) is technically correct, it is misleading in a
sense that user believes that something like SELECT
CAST(? AS INTEGER) might work, which of course does not :)
So I think that only the second message is correct.

Tested with FB 1.0.3 and 1.5RC5 on Windows, using IBX
and IBO.

=>

SFID: 796675#⁠
Submitted By: askln

Given

CREATE TABLE t (id INTEGER);

the following statements give corresponding errors:

1) SELECT ? FROM t => Data type unknown
2) SELECT id + ? FROM t => expression evaluation not
supported
3) SELECT id * ? FROM t => Array/BLOB/DATE data types
not allowed in arithmetic

While 1) is technically correct, it is misleading in a
sense that user believes that something like SELECT
CAST(? AS INTEGER) might work, which of course does not :)
So I think that only the second message is correct.

Tested with FB 1.0.3 and 1.5RC5 on Windows, using IBX
and IBO.

[firebird-automations]

Modified by: @pcisar

Version: 1.5 RC5 [ 10020 ]

Version: 1.0.3 [ 10006 ]

Component: Engine [ 10000 ]

SF_ID: 796675 =>

[firebird-automations]

Modified by: @pcisar

assignee: Dmitry Yemanov [ dimitr ] =>

[firebird-automations]

Modified by: @asfernandes

Link: This issue block progress on CORE1379 [ CORE1379 ]

[firebird-automations]

Modified by: @asfernandes

Link: This issue block progress on CORE1379 [ CORE1379 ] =>

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10242 ] => Firebird [ 14472 ]

[firebird-automations]

Commented by: @dyemanov

Testing against v2.5:

(1) is correct, and the CAST trick does work nowadays.
(4) is correct, see my explanation in the prior comment.
(5), (6), (7), (8) and (9) may either work or throw a conversion error, depending on the data inside the table. A string must be converted to a number, not vice versa. The last one compares two strings, thus it can never throw a conversion error.

I cannot see any "cannot transliterate" errors.

So IMHO only (2) and (3) deserve fixing, I'd prefer to guess the parameter type based on the ID column.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]