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](https://github.com/FirebirdSQL/firebird/issues?q=CORE1379+in%3Atitle) \[ [CORE1379](https://github.com/FirebirdSQL/firebird/issues?q=CORE1379+in%3Atitle) \]

[firebird-automations]

Modified by: @asfernandes

Link: This issue block progress on [CORE1379](https://github.com/FirebirdSQL/firebird/issues?q=CORE1379+in%3Atitle) \[ [CORE1379](https://github.com/FirebirdSQL/firebird/issues?q=CORE1379+in%3Atitle) \] =\>

[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 \]