Issue Details (XML | Word | Printable)

Key: CORE-218
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: askln
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Bad error messages on SELECT ? FROM

Created: 28/Aug/03 12:00 AM   Updated: 04/Feb/11 07:20 AM
Component/s: Engine
Affects Version/s: 1.0.3, 1.5 RC5
Fix Version/s: None

SF_ID: 796675


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:33 AM
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".

Alice F. Bird added a comment - 14/Jun/06 09:33 AM - edited
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 ;))

Dmitry Yemanov added a comment - 02/Feb/11 07:38 PM
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.