DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type;
	b=oUWuVE0os7be2X02GUgPXOrWcYKovnQhje92CwTdHk+N1QwXAC9CvF4vT4Z+JWWISU
	K7Pa2dkHddaXl5t+47nQCz8dK351jkcCYtJVf0RvfJerR7xpGAwEah1Q8BCpNKiIEJ7e
	Q2q0S4Q3RzShls2YaBHP/Dz4KoY6m8mEUBugQ=
Message-ID: <4D5263EC.9000205@gmail.com>
Date: Wed, 09 Feb 2011 11:52:44 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: Josh Berkus <josh@agliodbs.com>, Laszlo Nagy <gandalf@shopzeus.com>,
	pgsql-performance@postgresql.org, Daniel Fekete <danieleff@gmail.com>
Subject: Re: Bad query plan when the wrong data type is used
References: <4D514FFF.9060308@shopzeus.com>	<4D51BE0B.1060404@agliodbs.com>
	<AANLkTikWGEAgaOe4Z3UfT-k=9jZ6Z0GRTD3AzX3hRmcC@mail.gmail.com>
In-Reply-To: <AANLkTikWGEAgaOe4Z3UfT-k=9jZ6Z0GRTD3AzX3hRmcC@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------060906000504060400040808"

This is a multi-part message in MIME format.
--------------060906000504060400040808
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

09.02.11 01:14, Dave Crooke написав(ла):
> You will get the same behaviour from any database product where the 
> query as written requires type coercion - the coercion has to go in 
> the direction of the "wider" type. I have seen the exact same scenario 
> with Oracle, and I view it as a problem with the way the query is 
> written, not with the database server.
>
> Whoever coded the application which is making this query presumably 
> knows that the visa.id <http://visa.id> field is an integer type in 
> the schema they designed, so why are they passing a float? Convert the 
> 4.0 to 4 on the application side instead, it's one function call or cast.
Actually the problem may be in layers, and the  problem may even be not 
noticed until it's late enough. As far as I remember from this list 
there are problems with column being integer and parameter prepared as 
bigint or number. Same for number vs double vs float.
As for me it would be great for optimizer to consider the next:
1) val1::narrow = val2::wide as (val1::narrow = val2::narrow and 
val2::narrow = val2::wide)
2) val1::narrow < val2::wide as (val1::narrow < val2::narrow and 
val1::wide < val2::wide)
3) val1::narrow > val2::wide as (val1::narrow + 1 > val2::narrow and 
val1::wide > val2::wide)
Of course it should use additional check it this allows to use an index.
Surely, this is not an easy thing to implement, but as for me similar 
question are raised quite often in this list.

Best regards, Vitalii Tymchyshyn


--------------060906000504060400040808
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#ffffff">
    09.02.11 01:14, Dave Crooke написав(ла):
    <blockquote
      cite="mid:AANLkTikWGEAgaOe4Z3UfT-k=9jZ6Z0GRTD3AzX3hRmcC@mail.gmail.com"
      type="cite">You will get the same behaviour from any database
      product where the query as written requires type coercion - the
      coercion has to go in the direction of the "wider" type. I have
      seen the exact same scenario with Oracle, and I view it as a
      problem with the way the query is written, not with the database
      server.<br>
      <br>
      Whoever coded the application which is making this query
      presumably knows that the <a moz-do-not-send="true"
        href="http://visa.id">visa.id</a> field is an integer type in
      the schema they designed, so why are they passing a float? Convert
      the 4.0 to 4 on the application side instead, it's one function
      call or cast.<br>
    </blockquote>
    Actually the problem may be in layers, and the  problem may even be
    not noticed until it's late enough. As far as I remember from this
    list there are problems with column being integer and parameter
    prepared as bigint or number. Same for number vs double vs float.<br>
    As for me it would be great for optimizer to consider the next:<br>
    1) val1::narrow = val2::wide as (val1::narrow = val2::narrow and
    val2::narrow = val2::wide)<br>
    2) val1::narrow &lt; val2::wide as (val1::narrow &lt; val2::narrow
    and val1::wide &lt; val2::wide)<br>
    3) val1::narrow &gt; val2::wide as (val1::narrow + 1 &gt;
    val2::narrow and val1::wide &gt; val2::wide)<br>
    Of course it should use additional check it this allows to use an
    index. <br>
    Surely, this is not an easy thing to implement, but as for me
    similar question are raised quite often in this list.<br>
    <br>
    Best regards, Vitalii Tymchyshyn<br>
    <br>
  </body>
</html>

--------------060906000504060400040808--