Thread: Implicit conversion bugaboo in beta2?
Guys,
got this problem in 7.4 beta 2:
treedemo=# SELECT LPAD ((team_name), (LENGTH(team_name) + (3*(tlevel-2)))) AS
teams_display,team_id, lnode
treedemo-# FROM teams
treedemo-# WHERE lnode > 0
treedemo-# ORDER BY lnode;
ERROR: function lpad(character varying, bigint) does not exist
(the above query worked fine in 7.3.4, as I recall)
treedemo=# \df lpad
List of functions
Result data type | Name | Argument data types
------------------+------+---------------------
text | lpad | text, integer
text | lpad | text, integer, text
Now, I've been in favor of reducing problematic implicit conversions. But
VARCHAR --> TEXT is one that needs to stay, as there's no possibility of
ambiguity, and most users count on doing it transparently.
Either that, or we need to build all string function for varchar.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> ERROR: function lpad(character varying, bigint) does not exist
> (the above query worked fine in 7.3.4, as I recall)
Really? I get
regression=# select lpad('xyz'::varchar, 4::int8);
ERROR: Function lpad(character varying, bigint) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
in 7.3.4, and the same spelled a tad differently in CVS tip ...
regards, tom lane
Tom,
> regression=# select lpad('xyz'::varchar, 4::int8);
> ERROR: Function lpad(character varying, bigint) does not exist
> Unable to identify a function that satisfies the given argument
> types You may need to add explicit typecasts
Oops! Sorry. The problem is there, it's just something different than I
orginally thought; the issue is the BIGINT. What confuses me is how the
bigint got there; it's from this view:
CREATE VIEW vw_teams AS
SELECT teams_desc.team_id, team_name, team_code, notes,
MIN(teams_tree.treeno) as lnode, MAX(teams_tree.treeno) as rnode,
parent.team_id as parent_id, COUNT(*)/2 as tlevel
FROM teams_desc JOIN teams_tree USING (team_id)
JOIN teams_tree parent ON parent.treeno < teams_tree.treeno
JOIN teams_tree parents ON parents.treeno < teams_tree.treeno
WHERE parent.treeno = (SELECT max(p1.treeno) FROM teams_tree p1
WHERE p1.treeno < teams_tree.treeno
AND EXISTS (select treeno from teams_tree p2
where p2.treeno > teams_tree.treeno
and p2.team_id = p1.team_id))
AND EXISTS (select parents2.team_id from teams_tree parents2
where parents2.treeno > teams_tree.treeno
AND parents2.team_id = parents.team_id)
GROUP BY teams_desc.team_id, team_name, team_code, notes, parent.team_id;
In 7.4 beta2, the "tlevel" column comes out as BIGINT, not INT as it certainly
did in 7.2.4 and I think it did in 7.3.4.
Are we now defaulting COUNT(*) to BIGINT? IF so, that's going to be a *huge*
backwards compatibility warning for people ....
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Are we now defaulting COUNT(*) to BIGINT?
Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been
int4 since 7.1...
regards, tom lane
Tom, > Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been > int4 since 7.1... Hmmm ... can't be 7.2. The query is taken from a production database written for 7.2; I'd have noticed the BIGINT problem before now. Either that, or in 7.2 we were doing implicit conversion from BIGINT to INT for function calls? However, it's certainly possible it happend in 7.3, as this particular app was not ported to 7.3. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been
>> int4 since 7.1...
> Hmmm ... can't be 7.2. The query is taken from a production database written
> for 7.2; I'd have noticed the BIGINT problem before now. Either that, or in
> 7.2 we were doing implicit conversion from BIGINT to INT for function calls?
That could be --- I don't recall exactly when we decided implicit
bigint->int conversion was a bad idea ...
regards, tom lane
Tom, > That could be --- I don't recall exactly when we decided implicit > bigint->int conversion was a bad idea ... Well, it is a bad idea, so I won't argue. Sorry for the false alarm. -- Josh Berkus Aglio Database Solutions San Francisco