Since MATCH is evaluated AFTER the where, you need to use HAVING
SELECT
url,newwebsites.description,newwebsites.title,newwebsites.catid,category.ful
lname,MATCH
newwebsites.description AGAINST ('aliens') as GOO from newwebsites,category
LEFT
JOIN userrestrictions ON userrestrictions.name REGEXP
'[[:<:]]username|GLOBALAUTHADMIN[[:>:]]' AND
newwebsites.catid=userrestrictions.catid where
category.catid=newwebsites.catid
AND userrestrictions.catid IS NULL AND category.groupid<='1' ORDER BY
newwebsites.groupid DESC limit 10 HAVING GOO > 0;
----- Original Message -----
From: "Brad Barnett" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 09, 2001 1:43 PM
Subject: Strange queries
> SELECT
>
url,newwebsites.description,newwebsites.title,newwebsites.catid,category.ful
lname,MATCH
> newwebsites.description AGAINST ('aliens') as GOO from
newwebsites,category LEFT
> JOIN userrestrictions ON userrestrictions.name REGEXP
> '[[:<:]]username|GLOBALAUTHADMIN[[:>:]]' AND
> newwebsites.catid=userrestrictions.catid where
category.catid=newwebsites.catid
> AND userrestrictions.catid IS NULL AND category.groupid<='1' ORDER BY
> newwebsites.groupid DESC limit 10;
>
> Can anyone tell me why the above query works, and responds with a column
> labelled "GOO", but if I take the exact same query, and add "AND GOO > 0"
to it,
> it complains that there is :
>
> ERROR 1054: Unknown column 'GOO' in 'where clause'
>
>
> What gives? If the column GOO exists when MYSQL does an output from that
query,
> why can't I address it in the query?
>
> Thanks for any help anyone may have!
>
>
>
>
>
>
>
>
>
>
>
>
> Quoting Andrew Schmidt <[EMAIL PROTECTED]>:
>
> > Have you tried this under a 'stable' version of freebsd?
> >
> > In FreeBSD 4.2 beta, mysql would crash with user locks. Not mysql's
> > fault.
> >
> > now, I understand RC's are generally stable; but I would still make sure
> > that this bug doesn't show up in a stable os.
> >
> > regards,
> >
> > -- Andrew
> >
> > ----- Original Message -----
> > From: <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, April 09, 2001 3:34 PM
> > Subject: mysql uses 99% cpu under freebsd 4.3
> >
> >
> > > >Description:
> > > mysql uses 99% cpu and becomes extremely unresponsive under high load
> > >
> > > >How-To-Repeat:
> > > send about 300 simultaneous visitors to www.chicagobusiness.com and
> > tell
> > them to click around.
> > >
> > >
> > > >Fix:
> > > restart mysql. this usally helps, at least for a few minutes.
> > sometimes
> > it climbs back to 99% though.
> > >
> > > >Submitter-Id: <submitter ID>
> > > >Originator: Jon Nathan
> > > >Organization:
> > > Chaffee Interactive
> > > >MySQL support: extended email support
> > > >Synopsis: mysql uses 99% cpu and becomes unresponsive
> > > >Severity: serious
> > > >Priority: high
> > > >Category: mysql
> > > >Class: support
> > > >Release: mysql-3.23.36 (Source distribution) from freebsd ports
> > >
> > > >Environment:
> > > System: FreeBSD d1.crain.com 4.3-RC FreeBSD 4.3-RC #1: Tue Apr 3
> > 16:17:52
> > GMT 2001 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/CHAFFEE i386
> > >
> > >
> > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
> > > GCC: Using builtin specs.
> > > gcc version 2.95.2 19991024 (release)
> > > Compilation info: CC='cc' CFLAGS='-O2 -pipe -mpentiumpro ' CXX='c++'
> > CXXFLAGS='-O2 -pipe -mpentiumpro -felide-constructors -fno-rtti
> > -fno-except
> > ions' LDFLAGS=''
> > > LIBC:
> > > -r--r--r-- 1 root wheel 1170734 Apr 3 12:24 /usr/lib/libc.a
> > > lrwxr-xr-x 1 root wheel 9 Apr 3 12:24 /usr/lib/libc.so ->
> > libc.so.4
> > > -r--r--r-- 1 root wheel 559764 Apr 3 12:24 /usr/lib/libc.so.4
> > > Configure command:
> > ./configure --localstatedir=/data/db --without-perl --without-debug
> > --witho
> > ut-readline --without-bench --with-mit-threads=no --with-libwrap
> > --with-low-
> > memory --enable-assembler --with-berkeley-db --with-charset=latin1
> > --prefix=
> > /usr/local i386--freebsd4.3
> > > Perl: This is perl, version 5.005_03 built for i386-freebsd
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
>
> ---
>
> Dynamic Hosting
> HTTP://www.L8R.net/
> "We Provide Static Hostnames for Dynamic IP's"
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
