BUG #4083: Return type of MAX and MIN of a VARCHAR column is TEXT
The following bug has been logged online:
Bug reference: 4083
Logged by: Pedro Gimeno
Email address: pgsql-001@personal.formauri.es
PostgreSQL version: 8.2.5
Operating system: Irrelevant
Description: Return type of MAX and MIN of a VARCHAR column is TEXT
Details:
Example:
CREATE TABLE a (x VARCHAR(40));
INSERT INTO a (x) VALUES ("blah");
SELECT MAX(x),MIN(x) FROM a;
The last query's return types are both TEXT, which in Zeos are not treated
in the same way as varchar.
Since the queries are designed to work with multiple servers, the only
workaround we've found so far is to create aggregate functions MAX(varchar)
and MIN(varchar) that return varchar.
"Pedro Gimeno" <pgsql-001@personal.formauri.es> writes:
Description: Return type of MAX and MIN of a VARCHAR column is TEXT
This is not a bug. Most if not all operations with varchar will return
text --- it doesn't have its own operators.
The last query's return types are both TEXT, which in Zeos are not treated
in the same way as varchar.
I think you need to file a bug with Zeos to fix whatever their problem
is with text.
regards, tom lane
Tom Lane wrote:
I think you need to file a bug with Zeos to fix whatever their problem
is with text.
Well, to be fair, type text is not SQL standard. But surely Zeos won't get
very far with this superstrict typing approach. At least a little bit more
context might help us understand.
Peter Eisentraut wrote:
Tom Lane wrote:
I think you need to file a bug with Zeos to fix whatever their
problem is with text.Well, to be fair, type text is not SQL standard. But surely Zeos
won't get very far with this superstrict typing approach. At least a
little bit more context might help us understand.
Sure. Zeos allows connection to multiple servers, not just PostgreSQL;
for example, Firebird, MySQL and MSSQL.
Zeos (as well as the Borland Database Engine, which it just mimics in
this sense) assumes that VARCHAR fields in general (not just
PostgreSQL's) are textual fields with a limit of 255 characters for all
supported servers, which are graphically associated with single-line
edit controls.
On the other side, Zeos (as BDE did before as well as others) recognizes
that many servers have mechanisms for allowing fields with texts longer
than 255 characters and adds support for them; for example in Firebird
the type is BLOB SUB_TYPE TEXT and in MySQL it's TEXT. It assumes that
PostgreSQL's TEXT type falls into that category and associates it with a
"large text" type rather than "short text". The support for them is
quite limited, as they are a kind of "largest common divisor" of the
capabilities of the supported servers.
I think, but I'm not sure, that even the ODBC standard makes such
distinction between short and large text fields.
BTW, of course Tom is right in that most operators return TEXT and not
VARCHAR; I just stumbled upon this problem in a query that used MAX().
-- Pedro Gimeno
Import Notes
Resolved by subject fallback
Pedro Gimeno escribió:
Zeos (as well as the Borland Database Engine, which it just mimics in
this sense) assumes that VARCHAR fields in general (not just
PostgreSQL's) are textual fields with a limit of 255 characters for all
supported servers, which are graphically associated with single-line
edit controls.
Oh, so if you have a column of type VARCHAR(1000) or anything longer
than 255 chars it fails too?
And I'd guess that if you have a varchar(255) column and store a value
longer than 255 bytes (because there are many wide chars) it will fail
as well.
Both cases should really be tested and taken to Zeos if they fail.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Pedro Gimeno escribi�:
Zeos (as well as the Borland Database Engine, which it just mimics in
this sense) assumes that VARCHAR fields in general (not just
PostgreSQL's) are textual fields with a limit of 255 characters for all
supported servers, which are graphically associated with single-line
edit controls.Oh, so if you have a column of type VARCHAR(1000) or anything longer
than 255 chars it fails too?And I'd guess that if you have a varchar(255) column and store a value
longer than 255 bytes (because there are many wide chars) it will fail
as well.Both cases should really be tested and taken to Zeos if they fail.
I haven't tried. The 255 character limit may be a wrong assumption from
my side and not an actual Zeos limitation, but still VARCHAR fields are
treated as single-line edits and TEXT fields are treated as blob-like,
multiline texts. Anyway, in general Delphi does not do very well with
multibyte character sets, which by the way I'm not using. If it's
relevant for this bug report I can find out if Zeos has trouble with
255 character varchar fields. Otherwise you may want to make the test
yourself and report to http://zeosbugs.firmos.at/ which is Zeos' bug
tracker. I don't use >255 VARCHAR fields for compatibility with other
servers.
-- Pedro Gimeno