List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 21 1999 10:03pm
Subject:Re: Bug in MySQL (of sorts)
View as plain text  
At 7:48 PM -0500 9/20/99, Benjamin Pflugmann wrote:
>Hi.
>While others already gave good answers, I wanted to throw in some
>considerations:
>
>SQL operates on sets. And it is completely legal (i.e. syntactically
>and semantically correct) to write a condition which will return an
>empty set. The cited example is not much different from "WHERE 1=0"
>and there are several valid reasons why one would want to write such a
>query.

That's right.  Here's one using the example WHERE clause shown above:

SELECT * FROM tbl_name WHERE 0

(okay, so I've optimized the expression a bit. :-))


Now, if MySQL started being "smart" about what I wanted to do, it'd
reject this query as absurd, because "obviously I couldn't have meant
that."

But I do mean it.  It's useful.  It's a query that takes almost
no time to execute, and thus is an extremely quick way to test
within an application program whether or not the given table
exists.

It's also a useful thing for getting the metadata for a table's
columns.  For example, in a DBI script, you can execute that
query (replacing the * with whatever you want for the selection
list), and as soon as you invoke $sth->execute ($query), boom! you
can access the column metadata through the attributes of $sth
such as $sth->{NAME} or $sth->{NULLABLE}.  If all you're interested
in is column information, it's a waste to run a big query, and
WHERE 0 is a fine way to run a quick one.

--
Paul DuBois, paul@stripped
Thread
Bug in MySQL (of sorts)Bryan Porter21 Sep
  • Re: Bug in MySQL (of sorts)Scott Hess21 Sep
  • Re: Bug in MySQL (of sorts)Bob Kline21 Sep
    • Re: Bug in MySQL (of sorts)Michael Widenius22 Sep
  • Re: Bug in MySQL (of sorts)Balint TOTH21 Sep
  • Re: Bug in MySQL (of sorts)Benjamin Pflugmann21 Sep
    • Re: Bug in MySQL (of sorts)Steve Edberg21 Sep
      • Re: Bug in MySQL (of sorts)Benjamin Pflugmann21 Sep
    • Re: Bug in MySQL (of sorts)Paul DuBois22 Sep
  • Re: Bug in MySQL (of sorts)Scott Hess21 Sep