Re: SQL question

On Fri, 25 Jan 2002, Bolt Thrower wrote:

> In the query:
>
> select field, length(field) from table where length(field) < 10;
>
> How many times is the length() function called for each row? Once or
> twice?

I would expect it to run both times.

I did a similar query with an explain:

explain verbose select behavior_descr, length(behavior_descr) from
behaviors where length(behavior_descr) < 20;
NOTICE: QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 1.04 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043
:restypmod 34 :resname behavior_descr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 34 :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
length :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
EXPR :typeOid 23 :opType func :oper { FUNC :funcid 1319 :functype 23 }
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
:args ({ VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34
:varlevelsup 0 :varnoold 1 :varoattno 2})}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 97 :opid 66 :opresulttype 16 } :args ({ EXPR
:typeOid 23 :opType func :oper { FUNC :funcid 1319 :functype 23 } :args
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
({ VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34 :varlevelsup 0
:varnoold 1 :varoattno 2})} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ 20 0 0 0 ] })}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:

It looks like length() is getting run twice here.

-- Brett
http://www.chapelperilous.net/
------------------------------------------------------------------------
And I alone am returned to wag the tail.