Re: Omit another where clause from original query
2017/09/19 17:19 ... Don Wieland: Of these found rows, I want to omit those rows where there are rows found after the END TimeStamp based on ?below ?where clause: WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND apt.time_start > ‘1504238399' We are trying to find Former Clients who have not been seen after the date range - Lapsed Client Report What are you getting that you want not to see? Certainly if you simply left off the upper half of the 'BETWEEN' you would get all those that you now get and all that have been left out by being dated over ‘1504238399'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Omit another where clause from original query
I have a working query: /* start */ SELECT u.user_id, u.first_name AS u_first_name, u.last_name AS u_last_name, c.client_id AS c_client_id, c.first_name AS c_first_name, c.middle_name AS c_middle_name, c.last_name AS c_last_name, c.address AS c_address, c.city AS c_city, c.state AS c_state, c.zip AS c_zip, c.dob AS dob_ymd, c.phone_home AS c_phone_home, c.phone_cell AS c_phone_cell, c.phone_work AS c_phone_work, c.email AS c_email, c.other_contacts AS c_other_contacts, count(*) AS apt_qty FROM tl_appt apt JOIN tl_clients c ON c.client_id = apt.client_id JOIN tl_rooms r ON r.room_id = apt.room_id JOIN tl_users u ON u.user_id = apt.user_id WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND apt.time_start BETWEEN '150156' AND '1504238399' GROUP BY c.client_id HAVING count(*) > 0 ORDER BY u.first_name, u.last_name, c.last_name, c.first_name; /* end */ I want to add another condition: Of these found rows, I want to omit those rows where there are rows found after the END TimeStamp based on below where clause: WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND apt.time_start > ‘1504238399' We are trying to find Former Clients who have not been seen after the date range - Lapsed Client Report Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band
Re: using alias in where clause
On 1/28/2016 6:30 PM, Larry Martell wrote: On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: On 1/28/2016 3:32 PM, Larry Martell wrote: On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: On 1/28/2016 1:14 PM, Larry Martell wrote: On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: 2016/01/25 19:16 ... Larry Martell: SELECT IFNULL(f_tag_bottom, IFNULL(f_tag_bottom_major_axis, IFNULL(f_tag_bottom_minor_axis, IFNULL(f_tag_ch_x_bottom, IFNULL(f_tag_ch_y_bottom, NULL) as ftag, STDDEV(ch_x_top) Of course, this isn't your real problem, but you could use COALESCE instead of all those IFNULLs (and you don't need the last one): SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, STDDEV(ch_x_top) As Johnny Withers points out, you may repeat the expression in the WHERE-clause: WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7' If really only one of those is not NULL, it is equivalent to this: 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and I didn't know I could use that in a where clause. This worked great for the requirement I had, but of course, once that was implemented my client changed the requirements. Now they want to know which of the 5 f_tag_* columns was matched. Not sure how I'll do that. Probably need another query. One option to consider is to add another column to the query with a CASE similar to this... SELECT , ... original fields ... , CASE WHEN f_tag_bottom THEN 'f_tag_bottom' WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis' ... repeat for the rest of the fields to test ... ELSE 'none' END as match_flag FROM ... Technically, the term in the WHERE clause should prevent a 'none' result but I put it there to help future-proof the code. Won't that find the first one of the f_tags that is not null, but not necessarily the one that was matched by the where clause? I slightly cheated in my example. My CASE...END was listing terms in the same order as the COALESCE() function you were using in the WHERE clause. The cheat was that only a non-null value could be TRUE. To be more accurate, I should have used ... WHEN f_tag_bottom IS NOT NULL THEN ... That way you end up with a true boolean check within the CASE decision tree. As the COALESCE() is testing its terms in the same sequence as the CASE...END, there should be no difference between the two checks. But, that also adds to the maintenance cost of this query. If you should change the order of the f_tag checks in the COALESCE() function, you would need to change the CASE...END to the same sequence. Yes, I see that, but does the case only look at the filtered rows? For example, lets say there's this data: row 1: f_tag_bottom = "ABC" row 2: f_tag_bottom_major_axis = "XYZ" and my where clause has this: WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ' won't the CASE pick up row 1? Whereas I want it to pick up row 2. Yes it would. Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make both functions (CASE and COALESCE) find the same field value in the same row at the same time. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 1/28/2016 6:30 PM, Larry Martell wrote: >> >> On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> >> wrote: >>> >>> >>> >>> On 1/28/2016 3:32 PM, Larry Martell wrote: >>>> >>>> >>>> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green >>>> <shawn.l.gr...@oracle.com> >>>> wrote: >>>>> >>>>> >>>>> >>>>> >>>>> On 1/28/2016 1:14 PM, Larry Martell wrote: >>>>>> >>>>>> >>>>>> >>>>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: >>>>>>> >>>>>>> >>>>>>> >>>>>>> 2016/01/25 19:16 ... Larry Martell: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> SELECT IFNULL(f_tag_bottom, >>>>>>>> IFNULL(f_tag_bottom_major_axis, >>>>>>>> IFNULL(f_tag_bottom_minor_axis, >>>>>>>> IFNULL(f_tag_ch_x_bottom, >>>>>>>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>>>>>>>STDDEV(ch_x_top) >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Of course, this isn't your real problem, but you could use COALESCE >>>>>>> instead >>>>>>> of all those IFNULLs (and you don't need the last one): >>>>>>>SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as >>>>>>> ftag, >>>>>>>STDDEV(ch_x_top) >>>>>>> >>>>>>> >>>>>>> As Johnny Withers points out, you may repeat the expression in the >>>>>>> WHERE-clause: >>>>>>>WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = >>>>>>> 'E-CD7' >>>>>>> If really only one of those is not NULL, it is equivalent to this: >>>>>>>'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, >>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and >>>>>> I didn't know I could use that in a where clause. This worked great >>>>>> for the requirement I had, but of course, once that was implemented my >>>>>> client changed the requirements. Now they want to know which of the 5 >>>>>> f_tag_* columns was matched. Not sure how I'll do that. Probably need >>>>>> another query. >>>>>> >>>>> One option to consider is to add another column to the query with a >>>>> CASE >>>>> similar to this... >>>>> >>>>> SELECT >>>>> , ... original fields ... >>>>> , CASE >>>>> WHEN f_tag_bottom THEN 'f_tag_bottom' >>>>> WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis' >>>>> ... repeat for the rest of the fields to test ... >>>>> ELSE 'none' >>>>> END as match_flag >>>>> FROM ... >>>>> >>>>> Technically, the term in the WHERE clause should prevent a 'none' >>>>> result >>>>> but >>>>> I put it there to help future-proof the code. >>>> >>>> >>>> >>>> Won't that find the first one of the f_tags that is not null, but not >>>> necessarily the one that was matched by the where clause? >>>> >>> >>> I slightly cheated in my example. >>> >>> My CASE...END was listing terms in the same order as the COALESCE() >>> function >>> you were using in the WHERE clause. The cheat was that only a non-null >>> value >>> could be TRUE. To be more accurate, I should have used >>> ... WHEN f_tag_bottom IS NOT NULL THEN ... >>> That way you end up with a true boolean check within the CASE decision >>> tree. >>> >>> As the COALESCE() is testing its terms in the same sequence as the >>> CASE...END, there should be no difference between the two checks. But, >>> that >>> also adds to the maintenance cost of this query. If you should change the >>> order of the f_tag checks in the COALESCE() function, you would need to >>> change the CASE...END to the same sequence. >> >> >> Yes, I see that, but does the case only look at the filtered rows? For >> example, lets say there's this data: >> >> row 1: f_tag_bottom = "ABC" >> row 2: f_tag_bottom_major_axis = "XYZ" >> >> and my where clause has this: >> >> WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ' >> >> won't the CASE pick up row 1? Whereas I want it to pick up row 2. >> > > Yes it would. > > Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make > both functions (CASE and COALESCE) find the same field value in the same row > at the same time. Thanks very much Shawn. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: > 2016/01/25 19:16 ... Larry Martell: >> >> SELECT IFNULL(f_tag_bottom, >> IFNULL(f_tag_bottom_major_axis, >> IFNULL(f_tag_bottom_minor_axis, >>IFNULL(f_tag_ch_x_bottom, >> IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >> STDDEV(ch_x_top) >> > > Of course, this isn't your real problem, but you could use COALESCE instead > of all those IFNULLs (and you don't need the last one): > SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, > STDDEV(ch_x_top) > > > As Johnny Withers points out, you may repeat the expression in the > WHERE-clause: > WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7' > If really only one of those is not NULL, it is equivalent to this: > 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and I didn't know I could use that in a where clause. This worked great for the requirement I had, but of course, once that was implemented my client changed the requirements. Now they want to know which of the 5 f_tag_* columns was matched. Not sure how I'll do that. Probably need another query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On 1/28/2016 1:14 PM, Larry Martell wrote:
On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:
2016/01/25 19:16 ... Larry Martell:
SELECT IFNULL(f_tag_bottom,
IFNULL(f_tag_bottom_major_axis,
IFNULL(f_tag_bottom_minor_axis,
IFNULL(f_tag_ch_x_bottom,
IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
STDDEV(ch_x_top)
Of course, this isn't your real problem, but you could use COALESCE instead
of all those IFNULLs (and you don't need the last one):
SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
STDDEV(ch_x_top)
As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.
One option to consider is to add another column to the query with a CASE
similar to this...
SELECT
, ... original fields ...
, CASE
WHEN f_tag_bottom THEN 'f_tag_bottom'
WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
... repeat for the rest of the fields to test ...
ELSE 'none'
END as match_flag
FROM ...
Technically, the term in the WHERE clause should prevent a 'none' result
but I put it there to help future-proof the code.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On 1/28/2016 3:32 PM, Larry Martell wrote:
On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote:
On 1/28/2016 1:14 PM, Larry Martell wrote:
On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:
2016/01/25 19:16 ... Larry Martell:
SELECT IFNULL(f_tag_bottom,
IFNULL(f_tag_bottom_major_axis,
IFNULL(f_tag_bottom_minor_axis,
IFNULL(f_tag_ch_x_bottom,
IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
STDDEV(ch_x_top)
Of course, this isn't your real problem, but you could use COALESCE
instead
of all those IFNULLs (and you don't need the last one):
SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
STDDEV(ch_x_top)
As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.
One option to consider is to add another column to the query with a CASE
similar to this...
SELECT
, ... original fields ...
, CASE
WHEN f_tag_bottom THEN 'f_tag_bottom'
WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
... repeat for the rest of the fields to test ...
ELSE 'none'
END as match_flag
FROM ...
Technically, the term in the WHERE clause should prevent a 'none' result but
I put it there to help future-proof the code.
Won't that find the first one of the f_tags that is not null, but not
necessarily the one that was matched by the where clause?
I slightly cheated in my example.
My CASE...END was listing terms in the same order as the COALESCE()
function you were using in the WHERE clause. The cheat was that only a
non-null value could be TRUE. To be more accurate, I should have used
... WHEN f_tag_bottom IS NOT NULL THEN ...
That way you end up with a true boolean check within the CASE decision
tree.
As the COALESCE() is testing its terms in the same sequence as the
CASE...END, there should be no difference between the two checks. But,
that also adds to the maintenance cost of this query. If you should
change the order of the f_tag checks in the COALESCE() function, you
would need to change the CASE...END to the same sequence.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 1/28/2016 1:14 PM, Larry Martell wrote: >> >> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: >>> >>> 2016/01/25 19:16 ... Larry Martell: >>>> >>>> >>>> SELECT IFNULL(f_tag_bottom, >>>> IFNULL(f_tag_bottom_major_axis, >>>>IFNULL(f_tag_bottom_minor_axis, >>>> IFNULL(f_tag_ch_x_bottom, >>>> IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>>> STDDEV(ch_x_top) >>>> >>> >>> >>> Of course, this isn't your real problem, but you could use COALESCE >>> instead >>> of all those IFNULLs (and you don't need the last one): >>> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, >>> STDDEV(ch_x_top) >>> >>> >>> As Johnny Withers points out, you may repeat the expression in the >>> WHERE-clause: >>> WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7' >>> If really only one of those is not NULL, it is equivalent to this: >>> 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, >>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) >> >> >> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and >> I didn't know I could use that in a where clause. This worked great >> for the requirement I had, but of course, once that was implemented my >> client changed the requirements. Now they want to know which of the 5 >> f_tag_* columns was matched. Not sure how I'll do that. Probably need >> another query. >> > One option to consider is to add another column to the query with a CASE > similar to this... > > SELECT > , ... original fields ... > , CASE > WHEN f_tag_bottom THEN 'f_tag_bottom' > WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis' > ... repeat for the rest of the fields to test ... > ELSE 'none' > END as match_flag > FROM ... > > Technically, the term in the WHERE clause should prevent a 'none' result but > I put it there to help future-proof the code. Won't that find the first one of the f_tags that is not null, but not necessarily the one that was matched by the where clause? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 1/28/2016 3:32 PM, Larry Martell wrote: >> >> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> >> wrote: >>> >>> >>> >>> On 1/28/2016 1:14 PM, Larry Martell wrote: >>>> >>>> >>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: >>>>> >>>>> >>>>> 2016/01/25 19:16 ... Larry Martell: >>>>>> >>>>>> >>>>>> >>>>>> SELECT IFNULL(f_tag_bottom, >>>>>>IFNULL(f_tag_bottom_major_axis, >>>>>> IFNULL(f_tag_bottom_minor_axis, >>>>>> IFNULL(f_tag_ch_x_bottom, >>>>>> IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>>>>> STDDEV(ch_x_top) >>>>>> >>>>> >>>>> >>>>> >>>>> Of course, this isn't your real problem, but you could use COALESCE >>>>> instead >>>>> of all those IFNULLs (and you don't need the last one): >>>>> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, >>>>> STDDEV(ch_x_top) >>>>> >>>>> >>>>> As Johnny Withers points out, you may repeat the expression in the >>>>> WHERE-clause: >>>>> WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, >>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = >>>>> 'E-CD7' >>>>> If really only one of those is not NULL, it is equivalent to this: >>>>> 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, >>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) >>>> >>>> >>>> >>>> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and >>>> I didn't know I could use that in a where clause. This worked great >>>> for the requirement I had, but of course, once that was implemented my >>>> client changed the requirements. Now they want to know which of the 5 >>>> f_tag_* columns was matched. Not sure how I'll do that. Probably need >>>> another query. >>>> >>> One option to consider is to add another column to the query with a CASE >>> similar to this... >>> >>> SELECT >>> , ... original fields ... >>> , CASE >>>WHEN f_tag_bottom THEN 'f_tag_bottom' >>>WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis' >>>... repeat for the rest of the fields to test ... >>>ELSE 'none' >>>END as match_flag >>> FROM ... >>> >>> Technically, the term in the WHERE clause should prevent a 'none' result >>> but >>> I put it there to help future-proof the code. >> >> >> Won't that find the first one of the f_tags that is not null, but not >> necessarily the one that was matched by the where clause? >> > > I slightly cheated in my example. > > My CASE...END was listing terms in the same order as the COALESCE() function > you were using in the WHERE clause. The cheat was that only a non-null value > could be TRUE. To be more accurate, I should have used >... WHEN f_tag_bottom IS NOT NULL THEN ... > That way you end up with a true boolean check within the CASE decision tree. > > As the COALESCE() is testing its terms in the same sequence as the > CASE...END, there should be no difference between the two checks. But, that > also adds to the maintenance cost of this query. If you should change the > order of the f_tag checks in the COALESCE() function, you would need to > change the CASE...END to the same sequence. Yes, I see that, but does the case only look at the filtered rows? For example, lets say there's this data: row 1: f_tag_bottom = "ABC" row 2: f_tag_bottom_major_axis = "XYZ" and my where clause has this: WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ' won't the CASE pick up row 1? Whereas I want it to pick up row 2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
2016/01/25 19:16 ... Larry Martell: SELECT IFNULL(f_tag_bottom, IFNULL(f_tag_bottom_major_axis, IFNULL(f_tag_bottom_minor_axis, IFNULL(f_tag_ch_x_bottom, IFNULL(f_tag_ch_y_bottom, NULL) as ftag, STDDEV(ch_x_top) Of course, this isn't your real problem, but you could use COALESCE instead of all those IFNULLs (and you don't need the last one): SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, STDDEV(ch_x_top) As Johnny Withers points out, you may repeat the expression in the WHERE-clause: WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7' If really only one of those is not NULL, it is equivalent to this: 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell <larry.mart...@gmail.com> wrote: > On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers <joh...@pixelated.net> > wrote: > > You should probably turn this into a UNION and put an index on each > column: > > > > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom = > > 'E-CD7' > > UNION ALL > > SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom = > > 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE > > f_tag_bottom_minor_axis = 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE > > f_tag_bottom_major_axis = 'E-CD7' > > UNION ALL > > SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7' > > ; > > This may work for me, but I need to explore this more tomorrow. I need > the select to control the rows included in the aggregation. For > example, the rows where the f_tag_* col that is used does not = > 'E-CD7' should not be included in the aggregation. Also, I grossly > simplified the query for this post. In reality I have 15 items in the > where clause and a having as well. > I see, I missed the STDDEV() function you had, perhaps you could add that column to each SELECT in the untion, then wrap the entire union inside another select: SELECT ftag, STDDEV(ch_x_top) FROM ( ..union stuff here... ) > > > Doing this any other way will prevent index usage and a full table scan > will > > be required. > > Yes, I will be adding indices - right now I am just worried about > getting the query to work. But this is important as it's a big table. > So I do appreciate you mentioning it. > In reality, you could repeat the IFNULL(...) in the where clause the same way you have it in the column list. Not the optimal solution but it'd work for a proof of concept. > > > Is there a possibility of more than one column matching? How would you > > handle that? > > I was told only 1 of the 5 will be populated and the other 4 will be > null. But still, I said I have to code for the case where that is not > true. So then I was told to use the first one I find that is not null, > looking in the order I had in my original post. > You could also wrap another select around the union to handle more than one of the columns having a value. You could use the outer select to pick the one you wanted, something similar to: SELECT IFNULL(f_tag_ch_y_bottom,IFULL(f_tag_ch_x_bottom,IFNULL(,STDEV(ch_x_top) FROM ( SELECT f_tag_ch_y_bottom,NULL as f_tag_ch_x_bottom,NULL AS f_tag_bottom_minor,..., ch_x_top FROM data_cst WHERE f_tag_ch_y_bottom = 'E-CD7' UNION ALL SELECT NULL AS f_tag_ch_y_bottom, f_tag_ch_x_bottom,NULL AS f_tag_bottom_minor,...,ch_x_top FROM data_cst WHERE f_tag_ch_x_bottom = 'E-CD7' UNION ) And so on for each column/query. > > > > On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell <larry.mart...@gmail.com> > > wrote: > >> > >> I know I cannot use an alias in a where clause, but I am trying to > >> figure out how to achieve what I need. > >> > >> If I could have an alias in a where clause my sql would look like this: > >> > >> SELECT IFNULL(f_tag_bottom, > >> IFNULL(f_tag_bottom_major_axis, > >> IFNULL(f_tag_bottom_minor_axis, > >> IFNULL(f_tag_ch_x_bottom, > >>IFNULL(f_tag_ch_y_bottom, NULL) as ftag, > >>STDDEV(ch_x_top) > >> FROM data_cst > >> WHERE ftag = 'E-CD7' > >> GROUP BY wafer_id, lot_id > >> > >> But I can't use ftag in the where clause. I can't put it in a having > >> clause, as that would exclude the already aggregated rows and I want > >> to filter then before the aggregation. Anyone have any idea how I can > >> do this? > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe:http://lists.mysql.com/mysql > >> > > > > > > > > -- > > - > > Johnny Withers > > 601.209.4985 > > joh...@pixelated.net > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: using alias in where clause
On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers <joh...@pixelated.net> wrote: > You should probably turn this into a UNION and put an index on each column: > > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom = > 'E-CD7' > UNION ALL > SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom = > 'E-CD7' > UNION ALL > SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE > f_tag_bottom_minor_axis = 'E-CD7' > UNION ALL > SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE > f_tag_bottom_major_axis = 'E-CD7' > UNION ALL > SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7' > ; This may work for me, but I need to explore this more tomorrow. I need the select to control the rows included in the aggregation. For example, the rows where the f_tag_* col that is used does not = 'E-CD7' should not be included in the aggregation. Also, I grossly simplified the query for this post. In reality I have 15 items in the where clause and a having as well. > Doing this any other way will prevent index usage and a full table scan will > be required. Yes, I will be adding indices - right now I am just worried about getting the query to work. But this is important as it's a big table. So I do appreciate you mentioning it. > Is there a possibility of more than one column matching? How would you > handle that? I was told only 1 of the 5 will be populated and the other 4 will be null. But still, I said I have to code for the case where that is not true. So then I was told to use the first one I find that is not null, looking in the order I had in my original post. > On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell <larry.mart...@gmail.com> > wrote: >> >> I know I cannot use an alias in a where clause, but I am trying to >> figure out how to achieve what I need. >> >> If I could have an alias in a where clause my sql would look like this: >> >> SELECT IFNULL(f_tag_bottom, >> IFNULL(f_tag_bottom_major_axis, >> IFNULL(f_tag_bottom_minor_axis, >> IFNULL(f_tag_ch_x_bottom, >>IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>STDDEV(ch_x_top) >> FROM data_cst >> WHERE ftag = 'E-CD7' >> GROUP BY wafer_id, lot_id >> >> But I can't use ftag in the where clause. I can't put it in a having >> clause, as that would exclude the already aggregated rows and I want >> to filter then before the aggregation. Anyone have any idea how I can >> do this? >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> > > > > -- > - > Johnny Withers > 601.209.4985 > joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
You should probably turn this into a UNION and put an index on each column: SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom = 'E-CD7' UNION ALL SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom = 'E-CD7' UNION ALL SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE f_tag_bottom_minor_axis = 'E-CD7' UNION ALL SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE f_tag_bottom_major_axis = 'E-CD7' UNION ALL SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7' ; Doing this any other way will prevent index usage and a full table scan will be required. Is there a possibility of more than one column matching? How would you handle that? On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell <larry.mart...@gmail.com> wrote: > I know I cannot use an alias in a where clause, but I am trying to > figure out how to achieve what I need. > > If I could have an alias in a where clause my sql would look like this: > > SELECT IFNULL(f_tag_bottom, > IFNULL(f_tag_bottom_major_axis, > IFNULL(f_tag_bottom_minor_axis, > IFNULL(f_tag_ch_x_bottom, >IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >STDDEV(ch_x_top) > FROM data_cst > WHERE ftag = 'E-CD7' > GROUP BY wafer_id, lot_id > > But I can't use ftag in the where clause. I can't put it in a having > clause, as that would exclude the already aggregated rows and I want > to filter then before the aggregation. Anyone have any idea how I can > do this? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: using alias in where clause
Am 26.01.2016 um 01:16 schrieb Larry Martell: I know I cannot use an alias in a where clause, but I am trying to figure out how to achieve what I need. If I could have an alias in a where clause my sql would look like this: SELECT IFNULL(f_tag_bottom, IFNULL(f_tag_bottom_major_axis, IFNULL(f_tag_bottom_minor_axis, IFNULL(f_tag_ch_x_bottom, IFNULL(f_tag_ch_y_bottom, NULL) as ftag, STDDEV(ch_x_top) FROM data_cst WHERE ftag = 'E-CD7' GROUP BY wafer_id, lot_id But I can't use ftag in the where clause. I can't put it in a having clause, as that would exclude the already aggregated rows and I want to filter then before the aggregation. Anyone have any idea how I can do this? why would you want to do that? it would make the where clause not be able to use proper indexes signature.asc Description: OpenPGP digital signature
Re: using alias in where clause
Have you tried using a select case statement for ftag? > On Jan 25, 2016, at 6:39 PM, Larry Martell <larry.mart...@gmail.com> wrote: > > On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> wrote: >> >> >> Am 26.01.2016 um 01:16 schrieb Larry Martell: >>> >>> I know I cannot use an alias in a where clause, but I am trying to >>> figure out how to achieve what I need. >>> >>> If I could have an alias in a where clause my sql would look like this: >>> >>> SELECT IFNULL(f_tag_bottom, >>> IFNULL(f_tag_bottom_major_axis, >>> IFNULL(f_tag_bottom_minor_axis, >>> IFNULL(f_tag_ch_x_bottom, >>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>> STDDEV(ch_x_top) >>> FROM data_cst >>> WHERE ftag = 'E-CD7' >>> GROUP BY wafer_id, lot_id >>> >>> But I can't use ftag in the where clause. I can't put it in a having >>> clause, as that would exclude the already aggregated rows and I want >>> to filter then before the aggregation. Anyone have any idea how I can >>> do this? >> >> >> why would you want to do that? >> it would make the where clause not be able to use proper indexes > > I do not know which of the 5 f_tag_* columns will be populated and I > need to filter on the one that is populated. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
using alias in where clause
I know I cannot use an alias in a where clause, but I am trying to figure out how to achieve what I need. If I could have an alias in a where clause my sql would look like this: SELECT IFNULL(f_tag_bottom, IFNULL(f_tag_bottom_major_axis, IFNULL(f_tag_bottom_minor_axis, IFNULL(f_tag_ch_x_bottom, IFNULL(f_tag_ch_y_bottom, NULL) as ftag, STDDEV(ch_x_top) FROM data_cst WHERE ftag = 'E-CD7' GROUP BY wafer_id, lot_id But I can't use ftag in the where clause. I can't put it in a having clause, as that would exclude the already aggregated rows and I want to filter then before the aggregation. Anyone have any idea how I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> wrote: > > > Am 26.01.2016 um 01:16 schrieb Larry Martell: >> >> I know I cannot use an alias in a where clause, but I am trying to >> figure out how to achieve what I need. >> >> If I could have an alias in a where clause my sql would look like this: >> >> SELECT IFNULL(f_tag_bottom, >> IFNULL(f_tag_bottom_major_axis, >> IFNULL(f_tag_bottom_minor_axis, >>IFNULL(f_tag_ch_x_bottom, >> IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >> STDDEV(ch_x_top) >> FROM data_cst >> WHERE ftag = 'E-CD7' >> GROUP BY wafer_id, lot_id >> >> But I can't use ftag in the where clause. I can't put it in a having >> clause, as that would exclude the already aggregated rows and I want >> to filter then before the aggregation. Anyone have any idea how I can >> do this? > > > why would you want to do that? > it would make the where clause not be able to use proper indexes I do not know which of the 5 f_tag_* columns will be populated and I need to filter on the one that is populated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using alias in where clause
On Mon, Jan 25, 2016 at 8:01 PM, Rebecca Love <wacce...@gmail.com> wrote: > Have you tried using a select case statement for ftag? How would that help? Unless I'm missing something, I'd still have to have a reference to the column alias in the where clause. >> On Jan 25, 2016, at 6:39 PM, Larry Martell <larry.mart...@gmail.com> wrote: >> >> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> >> wrote: >>> >>> >>> Am 26.01.2016 um 01:16 schrieb Larry Martell: >>>> >>>> I know I cannot use an alias in a where clause, but I am trying to >>>> figure out how to achieve what I need. >>>> >>>> If I could have an alias in a where clause my sql would look like this: >>>> >>>> SELECT IFNULL(f_tag_bottom, >>>> IFNULL(f_tag_bottom_major_axis, >>>> IFNULL(f_tag_bottom_minor_axis, >>>> IFNULL(f_tag_ch_x_bottom, >>>> IFNULL(f_tag_ch_y_bottom, NULL) as ftag, >>>>STDDEV(ch_x_top) >>>> FROM data_cst >>>> WHERE ftag = 'E-CD7' >>>> GROUP BY wafer_id, lot_id >>>> >>>> But I can't use ftag in the where clause. I can't put it in a having >>>> clause, as that would exclude the already aggregated rows and I want >>>> to filter then before the aggregation. Anyone have any idea how I can >>>> do this? >>> >>> >>> why would you want to do that? >>> it would make the where clause not be able to use proper indexes >> >> I do not know which of the 5 f_tag_* columns will be populated and I >> need to filter on the one that is populated. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Where is mysql-workbench SCM?
Found it (after some days of searching): https://code.launchpad.net/ubuntu/+source/mysql-workbench must be the official repository according to https://dev.mysql.com/doc/refman/5.7/en/installing-development-tree.html. -Kalle Am 15.10.2015 um 22:11 schrieb Karl-Philipp Richter: > Hi, > The only list of mysql-related SCM repositories I found was > https://github.com/mysql which didn't contain a repository for > `mysql-workbench`. I only found the source tarball at > http://dev.mysql.com/downloads/workbench/. The development section at > http://dev.mysql.com/downloads/workbench/ doesn't explain the > development of `mysql-workbench`. > > Any help or pointers are appreciated. > > -Kalle > signature.asc Description: OpenPGP digital signature
Where is mysql-workbench SCM?
Hi, The only list of mysql-related SCM repositories I found was https://github.com/mysql which didn't contain a repository for `mysql-workbench`. I only found the source tarball at http://dev.mysql.com/downloads/workbench/. The development section at http://dev.mysql.com/downloads/workbench/ doesn't explain the development of `mysql-workbench`. Any help or pointers are appreciated. -Kalle signature.asc Description: OpenPGP digital signature
Re: Where to ask a question about installation and configuration
Hello Steve, To what list should I post with a post-installation config and startup question? This list, the MySQL General Mailing List, is the right place if the question is about MySQL! Cheers -- Claudio
Where to ask a question about installation and configuration
To what list should I post with a post-installation config and startup question? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hello Christophe, On 3/23/2014 2:34 PM, Christophe wrote: Hi all, Le 20/03/2014 13:20, Christophe a écrit : Hi Morgan, Harald, and all, Thanks for the answers. While reading your answers, I realize one more time this problem does not seem so obvious ... That's why I asked ;) . Beyond the specific case I mentioned, my goal is also to get a general discussion about theorical behaviour of MySQL while handling WHERE clauses. I don't have access to the application for now, but will try EXPLAIN as soon as I can . Here is the Result of Explain : http://perso.stux6.net/mysql/20140323/EXPLAIN.txt I don't really know what is tells in details, but according to key field, it seems the IDX_CS_DWProcessed key is processed first, as I'd like it to be ... Other question : is an INDEX on DATETIME field really useful ? Yes, they can be quite useful depending on selective they are. In fact, you may want to create a combination index that contains the 'processed' flag as one of the first or final fields (depending on how you use it) to make an index that is even more selective than one you have already. Normally a flag field like 'processed' has only 2 values: yes or no. Yours may contain multiple other values (I don't know) so the cardinality on that index may have made it the most selective choice for this particular query. http://dev.mysql.com/doc/refman/5.6/en/execution-plan-information.html http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html The general rule is, a table scan (sequential access) will be more efficient than an indexed lookup of rows (random access) if the indexed retrieval will need to get more than about 30% of the rows of a table. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi all, Le 20/03/2014 13:20, Christophe a écrit : Hi Morgan, Harald, and all, Thanks for the answers. While reading your answers, I realize one more time this problem does not seem so obvious ... That's why I asked ;) . Beyond the specific case I mentioned, my goal is also to get a general discussion about theorical behaviour of MySQL while handling WHERE clauses. I don't have access to the application for now, but will try EXPLAIN as soon as I can . Here is the Result of Explain : http://perso.stux6.net/mysql/20140323/EXPLAIN.txt I don't really know what is tells in details, but according to key field, it seems the IDX_CS_DWProcessed key is processed first, as I'd like it to be ... Other question : is an INDEX on DATETIME field really useful ? Regards, Christophe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Michael,
Le 18/03/2014 20:28, Michael Dykman a écrit :
Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row. I would suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate @yesterday);
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
Regards,
Christophe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Morgan, Harald, and all, Thanks for the answers. While reading your answers, I realize one more time this problem does not seem so obvious ... That's why I asked ;) . Beyond the specific case I mentioned, my goal is also to get a general discussion about theorical behaviour of MySQL while handling WHERE clauses. I don't have access to the application for now, but will try EXPLAIN as soon as I can . Regards, Christophe. Le 19/03/2014 17:12, Morgan Tocker a écrit : Hi Harald, you can call me Harald My apologies! the question was is there any performance impact and (DWProcessed / Lowest cardinality and indexed) so *yes* it does matter, i have seen MySQL more than once not using any index because a unlucky order of where clauses up to copy a 16 GB table in a table file instead using a existing primary key If you have experienced that the order of the where clause has changed which execution plans are possible, I suggest taking a look at optimizer trace: http://dev.mysql.com/doc/internals/en/optimizer-tracing.html This is more detailed than EXPLAIN and will show indexes being considered, and the reasons that they are excluded. The 1=1 example I gave will also show as eliminated in optimizer trace. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Christophe,
On 20.03.14 13:18, Christophe wrote:
Hi Michael,
Le 18/03/2014 20:28, Michael Dykman a écrit :
Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row. I would suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate @yesterday);
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
Actually, the optimizer is often capable of detecting constant expressions and
evaluating them only once, regardless of how many rows that are accessed in the
query.
Run EXPLAIN EXTENDED for the query, followed by SHOW WARNINGS.
If you see cache(your expression) in the output, it is a sign that the
optimizer detected that your expression is constant, so it will be evaluated
once and cached for subsequent uses.
Thanks,
Roy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
From: Christophe t...@stuxnet.org
To: mysql@lists.mysql.com
Cc:
Sent: Tuesday, 18 March 2014, 19:10
Subject: Precedence in WHERE clauses.
Hi list,
I'd like to get your advice about precedence in where clauses in MySQL
(5.0.51, and 5.1.66 in this case / from lenny and squeeze Debian
packages ).
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(),
INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
Regards,
Christophe.
What is the output from explain?
EXPLAIN SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24 HOUR);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
When a MySQL server receives a query, it goes through a process called query
optimization and tries to determine the best way to execute it (based on
availability of indexes etc). You can think of this as similar to how GPS
software picks the fastest route - it is very similar.
The order of the WHERE clause does not matter, and in fact more complicated
transformations happen in query optimization automatically. For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
1=1 is detected as a tautology and removed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Am 19.03.2014 15:05, schrieb Morgan Tocker: When a MySQL server receives a query, it goes through a process called query optimization and tries to determine the best way to execute it (based on availability of indexes etc) The order of the WHERE clause does not matter that is simply not true otherwise that documentation would not exist http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html signature.asc Description: OpenPGP digital signature
Re: Precedence in WHERE clauses.
Reindl, The order of the WHERE clause does not matter that is simply not true otherwise that documentation would not exist http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html Yes, the order of the columns in composite indexes *does matter*. This is a different question to what Christophe asked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Am 19.03.2014 16:27, schrieb Morgan Tocker:
Reindl,
you can call me Harald
The order of the WHERE clause does not matter
that is simply not true
otherwise that documentation would not exist
http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
Yes, the order of the columns in composite indexes *does matter*. This is a
different question to what Christophe asked.
the question was is there any performance impact
and (DWProcessed / Lowest cardinality and indexed)
so *yes* it does matter, i have seen MySQL more than once not using any
index because a unlucky order of where clauses up to copy a 16 GB
table in a table file instead using a existing primary key
___
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
Is there any performance impact on query processing, about the order of
WHERE clauses ?
signature.asc
Description: OpenPGP digital signature
Re: Precedence in WHERE clauses.
G'morning
A function in a where equals what ?
On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
When a MySQL server receives a query, it goes through a process called
query optimization and tries to determine the best way to execute it
(based on availability of indexes etc). You can think of this as similar
to how GPS software picks the fastest route - it is very similar.
The order of the WHERE clause does not matter, and in fact more
complicated transformations happen in query optimization automatically.
For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
1=1 is detected as a tautology and removed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
Mogens Melander
+66 8701 33224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
On 3/19/2014 7:27 PM, Mogens Melander wrote:
G'morning
A function in a where equals what ?
On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
Hi Christophe,
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
When a MySQL server receives a query, it goes through a process called
query optimization and tries to determine the best way to execute it
(based on availability of indexes etc). You can think of this as similar
to how GPS software picks the fastest route - it is very similar.
The order of the WHERE clause does not matter, and in fact more
complicated transformations happen in query optimization automatically.
For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
1=1 is detected as a tautology and removed.
--
Hello Mogens,
The entire WHERE expression becomes a row-by-row TRUE/FALSE test for
which rows should become part of the results. The expressions are
evaluated in whichever order the Optimizer thinks will get us to the
answer with the least effort.
In this particular query case, there are three tests to perform
1) DWProcessed = 0
2) PreviousStatus NOT IN ('PENDING', 'ACCEPTED')
3) SubscribeDate DATE_SUB(NOW(), INTERVAL 24 HOUR)
The NOT IN test of #2 is actually shorthand for a negated OR expression.
It is evaluated like this
(PreviousStatus 'PENDING') AND (PreviousStatus 'ACCEPTED')
According to the original WHERE clause, all three terms are AND-ed
together which means that if any one of those tests fail for a
particular row, that row does not become part of the final result.
The inner workings of the optimizer are complex. There is quite a bit
already written about how it works. And, you can review that section of
the code if you are truly adventurous . MySQL is open source and it's
all there for anyone to review.
I also suggest that a good introduction to the entire process is in
sections 8.2.1.2 through 8.2.1.14 of the manual. These provide a nice
overview of the various access and simplification methods that the
Optimizer can choose from while trying to work out the optimal method of
resolving a SQL command.
https://dev.mysql.com/doc/refman/5.6/en/where-optimizations.html
Best wishes,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Precedence in WHERE clauses.
Hi list,
I'd like to get your advice about precedence in where clauses in MySQL
(5.0.51, and 5.1.66 in this case / from lenny and squeeze Debian
packages ).
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
Regards,
Christophe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
My advice to you is to make use of the EXPLAIN facility which
porovides the most accurate information about how MySQL is going to
treat your query.
Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row. I would suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate @yesterday);
On Tue, Mar 18, 2014 at 3:10 PM, Christophe t...@stuxnet.org wrote:
Hi list,
I'd like to get your advice about precedence in where clauses in MySQL
(5.0.51, and 5.1.66 in this case / from lenny and squeeze Debian
packages ).
Considering the following simple query :
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate DATE_SUB(NOW(), INTERVAL 24
HOUR);
Which of these filters are processed first ?
I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
being processed first, but I can't really find any useful information
about this .
Is there any performance impact on query processing, about the order of
WHERE clauses ?
Regards,
Christophe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Nested WHERE
Awesome! Thank you very much Claudio! :) On Thu, Nov 21, 2013 at 10:40 AM, Claudio Nanni claudio.na...@gmail.comwrote: Hi Jopoy, Try this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username HAVING total_usage 322100 ORDER BY total_usage DESC; On values derived from group functions you have to use HAVING instead of WHERE, WHERE filters the records before the grouping, HAVING once grouping is done. Cheers Claudio 2013/11/21 Jopoy Solano m...@jopoy.com Hi! I'm not sure how to phrase this question... anyway, here it is: I'm trying to show users in DB radius who have exceeded 322100 bytes (3GB) within the current month. As of writing I can only display total usage by user with this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username ORDER BY total_usage DESC; I wanted to add something like a WHERE total_usage 322100 line but I don't know where to insert it. Any help would be greatly appreciated. Jopoy -- Claudio
Re: Nested WHERE
Hi Jopoy, Try this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username HAVING total_usage 322100 ORDER BY total_usage DESC; On values derived from group functions you have to use HAVING instead of WHERE, WHERE filters the records before the grouping, HAVING once grouping is done. Cheers Claudio 2013/11/21 Jopoy Solano m...@jopoy.com Hi! I'm not sure how to phrase this question... anyway, here it is: I'm trying to show users in DB radius who have exceeded 322100 bytes (3GB) within the current month. As of writing I can only display total usage by user with this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username ORDER BY total_usage DESC; I wanted to add something like a WHERE total_usage 322100 line but I don't know where to insert it. Any help would be greatly appreciated. Jopoy -- Claudio
Nested WHERE
Hi! I'm not sure how to phrase this question... anyway, here it is: I'm trying to show users in DB radius who have exceeded 322100 bytes (3GB) within the current month. As of writing I can only display total usage by user with this: SELECT username,sum(acctoutputoctets) AS total_usage FROM radacct WHERE EXTRACT(YEAR_MONTH FROM acctstarttime) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)and EXTRACT(YEAR_MONTH FROM acctstarttime) EXTRACT(YEAR_MONTH FROM CURRENT_DATE + INTERVAL 1 MONTH) GROUP BY username ORDER BY total_usage DESC; I wanted to add something like a WHERE total_usage 322100 line but I don't know where to insert it. Any help would be greatly appreciated. Jopoy
RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Hi,
Or if you are interested in limiting the number of rows add a limit clause,
so that entire resultset of entire table is not returned ,
I am not sure how will that be done , but i have seen some GUIs doing that,
would look for a solution from group.
Thanks
Abhishek
-Original Message-
From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: 24 September 2012 04:58
To: fuller.art...@gmail.com; pownall...@gmail.com
Cc: mysql@lists.mysql.com
Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT
table WHERE...;
Possibly run your constructed query thru a regex expression e.g.
String mydata = SELECT * from table WHERE ab;; Pattern pattern =
Pattern.compile('WHERE'); Matcher matcher = pattern.matcher(mydata);
if (matcher.find()) { //WHERE clause found proceed normally } else
throw new Exception(WHERE clause not found); Martin
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de
confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig.
Diese Nachricht dient lediglich dem Austausch von Informationen und
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten
Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt
uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas
le destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la
copie de ceci est interdite. Ce message sert à l'information seulement
et n'aura pas n'importe quel effet légalement obligatoire. Étant donné
que les email peuvent facilement être sujets à la manipulation, nous ne
pouvons accepter aucune responsabilité pour le contenu fourni.
Date: Sun, 23 Sep 2012 18:38:58 -0400
Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT
table WHERE...;
From: fuller.art...@gmail.com
To: pownall...@gmail.com
CC: mysql@lists.mysql.com
Tim,
I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for
some rows, as restricted by the Where clause.
Unfortunately, I don't think that can be done. But I'm not certain of
that; there might be a trick.
Arthur
www.artfulsoftware.com
On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com
wrote:
select * from table where column=value means it will return only
rows that match. as long as you have proper indexing there should
not be any issues.
On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz
luis.daniel.lu...@gmail.com wrote:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Even if you could block them, they would be easy to get around: SELECT * FROM tbl WHERE 1; If you have long running queries, you should investigate the reasons (other than lack of WHERE). * MyISAM locks the table for any writes. This prevents a SELECT from starting or a select can prevent the INSERT/UPDATE/DELETE from starting. Consider switching to InnoDB. * How big is the table? Why does the user want the whole table (if it is a plain SELECT *)? If you are doing Data Warehousing, consider summary tables. Let's see the big picture; I expect there is a way to solve the _real_ problem, which I guess is performance. -Original Message- From: Arthur Fuller [mailto:fuller.art...@gmail.com] Sent: Sunday, September 23, 2012 3:39 PM To: Tim Pownall Cc: mysql@lists.mysql.com Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...; Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Helo, Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. LD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz: Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. no and the idea is broken by design what is wrong with a select * from table with small tbales having only a handful of records? how will you work with broken select count(*) from table;? signature.asc Description: OpenPGP digital signature
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: Helo, Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. LD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Thanks, Tim Pownall GNU/Linux Systems Monitoring 610-621-9712 pownall...@gmail.com
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
that does not change the fact that it is valid to NOT have a where statement and for me it sound plain stupid to think about blocking queries without where-statements - the application developers will hopefully kill any admin who finds a solution for this... * i maintain a cms-system since 10 years * mainmenu: select id,title from table order by sortfield; * and YES in the backend the is no single reson to reduce the result * and NO using a key would not make anything faster so why would there be a WHERE make any sense and why does someone like to break the application just for fun? Am 23.09.2012 21:50, schrieb Tim Pownall: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote: Helo, Just wondering if is possible to block SELECT queries that doesnt have a WHERE statement within. signature.asc Description: OpenPGP digital signature
Re: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Tim, I think you misunderstood the question. Daniel wants to block Select queries that ask for all rwows, and permit only queries that ask for some rows, as restricted by the Where clause. Unfortunately, I don't think that can be done. But I'm not certain of that; there might be a trick. Arthur www.artfulsoftware.com On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote: select * from table where column=value means it will return only rows that match. as long as you have proper indexing there should not be any issues. On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz luis.daniel.lu...@gmail.com wrote:
RE: How to block SELECT * FROM table; but not SELECT * FROMT table WHERE...;
Possibly run your constructed query thru a regex expression e.g.
String mydata = SELECT * from table WHERE ab;;
Pattern pattern = Pattern.compile('WHERE');
Matcher matcher = pattern.matcher(mydata);
if (matcher.find())
{
//WHERE clause found proceed normally
}
else throw new Exception(WHERE clause not found);
Martin
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Sun, 23 Sep 2012 18:38:58 -0400
Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table
WHERE...;
From: fuller.art...@gmail.com
To: pownall...@gmail.com
CC: mysql@lists.mysql.com
Tim,
I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for some
rows, as restricted by the Where clause.
Unfortunately, I don't think that can be done. But I'm not certain of that;
there might be a trick.
Arthur
www.artfulsoftware.com
On Sun, Sep 23, 2012 at 3:50 PM, Tim Pownall pownall...@gmail.com wrote:
select * from table where column=value means it will return only rows that
match. as long as you have proper indexing there should not be any issues.
On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz
luis.daniel.lu...@gmail.com wrote:
distinct count operation with the use of where count $num
Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: distinct count operation with the use of where count $num
Hi, you have to use `HAVING' instead of `WHERE' like this. SELECT DISTINCT `term`, COUNT(*) AS count FROM blp_sql_distinct_temp_table GROUP BY `term` HAVING count = 5 ORDER BY count DESC; put `HAVING' next of `GROUP BY'. `WHERE' behaves at before aggregate of `GROUP BY'. your SQL means like SELECT .. FROM (SELECT * FROM .. WHERE count = 5) AS dummy GROUP BY .. because of that, mysqld says `Unknown column .. in where clause' regards, 2012/6/17 Haluk Karamete halukkaram...@gmail.com: Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: USING WHERE; USING TEMPORARY; USING filesort
- Original Message - From: Daevid Vincent dae...@daevid.com d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30; Could you put an index on sg.scene_id? Not sure, but it might help the grouping be more efficient. In general, though, you mqy be running into the limit of how big a temporary result table in memory can get before it gets moved to disk. There's two parameters that govern that iirc, max_tmp_table_size and another. Have a look at the manual. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: USING WHERE; USING TEMPORARY; USING filesort
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent dae...@daevid.com wrote: Anyone have any thoughts on how I might optimize this query? As always, it's all about the indexes. The index it chose on your main table looks pretty weak. You probably should move those non-joining columns out of your join condition in case their location is influencing the plan, and try experimenting with multi-column indexes. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
USING WHERE; USING TEMPORARY; USING filesort
Anyone have any thoughts on how I might optimize this query? It takes about
2 seconds. I know that seems quick, but we get nearly 30,000 hits per second
and so if we can shave this down, it helps. Also we do use memcache, but
even with that, we still see this in the slow-log sometimes. I have indexes
on everything used in this query and even a compound one as you see in the
EXPLAIN.
I'm not going to lose sleep over it, but I thought if there was something
simple or a way to refactor I'd give it a shot. I thought changing the query
to use JOIN ON syntax would have helped, but it didn't do anything really?!
Also, this is all being used with PHP, so I'm fine with pulling things out
into two or three queries if you suggest it will make a faster difference.
-- old query:
SELECT sg.`scene_id`, COUNT(*) AS num FROM `scenes_list` AS s, `dvds` AS d,
`scenes_genres` AS sg, `scenes_downloads_new` AS sd WHERE sg.`genre_id` IN
('1','8','10','19','38','58','65') AND d.`dvd_id`=s.`dvd_id` AND
sg.`scene_id`=s.`scene_id` AND d.`status`='ok' AND
d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY
sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;
-- refactored: is this correct order?? Smallest table and most filters first
right to narrow the dataset as quick as possible?
-- EXPLAIN
SELECT
s.`scene_id`,
COUNT(*) AS num
FROM`dvds` AS d
JOIN `scenes_list` AS s ON d.`dvd_id` = s.`dvd_id`
AND d.`date_release` != '-00-00'
AND d.`status` = 'ok'
JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN ('1', '8', '10', '19', '38', '58',
'65')
JOIN `scenes_downloads_new` AS sd ON sd.`scene_id` = s.`scene_id`
GROUP BY sg.`scene_id`
ORDER BY num DESC, sd.`count_dl_monthly` DESC
LIMIT 30
;
SELECT COUNT(*) FROM dvds; -- 12181
SELECT COUNT(*) FROM scenes_downloads_new; -- 66054
SELECT COUNT(*) FROM scenes_list; -- 67197
SELECT COUNT(*) FROM scenes_genres; -- 344272
-- why do I still hit the filesort and temp table? how can I get rid of
that?
id select_type TABLE TYPEpossible_keys
KEY key_len ref ROWS Extra
-- --- -- --
-- --
--- - --
--
1 SIMPLE d ref
PRIMARY,date_release,STATUS,status_release,dvd_release status_release 1
const2436 USING WHERE; USING TEMPORARY; USING
filesort
1 SIMPLE s ref PRIMARY,dvd_id_2,dvd_id
dvd_id 4.d.dvd_id 6 USING WHERE
1 SIMPLE sd eq_ref PRIMARY
PRIMARY 3.s.scene_id 1 USING WHERE
1 SIMPLE sg ref PRIMARY,scene_id,genre_id
scene_id4.s.scene_id 5 USING WHERE
If I take off the ORDER BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does add
Using index to the scene_id row (weird).
If I take off the GROUP BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does
remove Using Filesort.
Taking them both off is optimal I suspect, and again, barring the fact the
results are wrong, it takes 1 second for the query. Should I read that to
mean, it is what it is and that's the best I can expect from that
multi-join query? Is there any benefit to splitting this up and if so, how
should I split it?
d
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
credit where due
While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: credit where due
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman mdyk...@gmail.com wrote: While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. -- - michael dykman - mdyk...@gmail.com May the Source be with you. I wholeheartedly agree with Michael about Shawn's contributions to this list, and I have not been bashful about saying so publicly and privately. David
Re: credit where due
Thanks Shawn! Ninus from Montreal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: credit where due
On 10/19/2011 13:29, Michael Dykman wrote: While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. I am very humbled and honored. Thank you very kindly. We, the old teams from MySQL, have fought very hard to maintain our identities as the product itself has changed ownership. Some battles we won, some we did not. Our support and development teams are still predominately intact since before the Sun acquisition. We have had to learn how to use some new tools and adjust to different corporate philosophies but our dedication to our customers or the quality of the product has never waned. Certainly there have needed to be adjustments along the way. Some of them were painful (the loss of our old friend Eventum still haunts us today) some were easy (like having essentially one tier of support services for all customers). I don't get as much time to spend monitoring this list as I used to. I contributed to this list before I worked for MySQL and I will continue being part of this community as long as MySQL is part of my life. If you would like to join me and become part of the MySQL team in any capacity, we are always scouting for new talent in all geographical areas. Please do not post any responses or resumes to this list or send them to my personal account. I can no longer accept them (it's one of those policy changes I mentioned earlier). Instead please look for a listing that you may be interested in at http://www.oracle.com/us/corporate/careers/index.html and start the process there. Again, thank you very much. Humbly yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: credit where due
I also want to say Thank you Shawn for your valuable contribution. On the note on Resumes -I thought I would put it out here. My company is looking for an excellent MySQL DBA (Oracle and MSSQL experience a plus) Please send me your resume. Thanks! Sabika On Wed, Oct 19, 2011 at 12:30 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 10/19/2011 13:29, Michael Dykman wrote: While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. I am very humbled and honored. Thank you very kindly. We, the old teams from MySQL, have fought very hard to maintain our identities as the product itself has changed ownership. Some battles we won, some we did not. Our support and development teams are still predominately intact since before the Sun acquisition. We have had to learn how to use some new tools and adjust to different corporate philosophies but our dedication to our customers or the quality of the product has never waned. Certainly there have needed to be adjustments along the way. Some of them were painful (the loss of our old friend Eventum still haunts us today) some were easy (like having essentially one tier of support services for all customers). I don't get as much time to spend monitoring this list as I used to. I contributed to this list before I worked for MySQL and I will continue being part of this community as long as MySQL is part of my life. If you would like to join me and become part of the MySQL team in any capacity, we are always scouting for new talent in all geographical areas. Please do not post any responses or resumes to this list or send them to my personal account. I can no longer accept them (it's one of those policy changes I mentioned earlier). Instead please look for a listing that you may be interested in at http://www.oracle.com/us/**corporate/careers/index.htmlhttp://www.oracle.com/us/corporate/careers/index.html and start the process there. Again, thank you very much. Humbly yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=sabika.makhdoom@gmail.**comhttp://lists.mysql.com/mysql?unsub=sabika.makhd...@gmail.com
Re: Update on inner join - looks good to me, where did I go wrong?
On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen cars...@bitbybit.dk wrote: `userTable.userid` = `userTable`.`userid` Thank you Carsten. That was indeed the problem! Have a peaceful weekend. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update on inner join - looks good to me, where did I go wrong?
I'm trying to update on an join, but I can't find my error: UPDATE `userTable` SET `someField`=Jimmy Page FROM `userTable` INNER JOIN `anotherTable` ON `userTable.userid`=`anotherTable.userid` WHERE `userTable.someField`=Jim Morrison AND `anotherTable.date` NOW(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM That error is for reserved words, and I am escaping all the fields and tables (using the backticks). So why the error? This is on a CentOS 4 or 5 server, with MySQL 5.0.77, accessed from the CLI. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` - ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison - AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
`userTable.userid` = `userTable`.`userid` / Carsten On 09-09-2011 23:01, Dotan Cohen wrote: Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` -ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison -AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using where; Using temporary; Using filesort
I'm trying to optimize a query that doesn't seem all that complicated, however I can't seem to get it to not use a temp table and filesort. developer@vm_vz_daevid:~$ mysql --version mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 EXPLAIN EXTENDED SELECT -- d.date_release, -- d.dvd_title, -- s.type, -- s.id_place, s.scene_id AS index_id, s.dvd_id FROM dvds AS d JOIN scenes_list AS s ON s.dvd_id = d.dvd_id AND d.status = 'ok' AND d.date_release != '-00-00' ORDER BY d.date_release DESC, d.dvd_title ASC, s.type ASC, s.id_place ASC; *** 1. row *** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY,date_release,status,status_release key: status_release key_len: 1 ref: const rows: 1976 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: s type: ref possible_keys: dvd_id_2,dvd_id key: dvd_id key_len: 4 ref: videoszcontent.d.dvd_id rows: 6 Extra: Using where 2 rows in set, 1 warning (0.00 sec) There are proper indexes on most every column in both tables (as you can see there). [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! [b] The commented out columns above I thought might help with the ORDER BY for some reason from my reading here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html they did not. [c] lopping off the ORDER BY all together stops the Using temporary; Using filesort of course. Yeah! But now I'm left with a table of data in random order. Re-sorting it in PHP seems like an even bigger waste of cycles, when no doubt MySQL can sort hella-faster. [d] just doing ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents the using temporary but still does filesort and again I'm in the boat of [c] I guess my question is this: Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L. and no matter what, MySQL is going to give you a Cleveland Steamer? In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. d. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using where; Using temporary; Using filesort
s 2011/05/27 12:26 -0700, Daevid Vincent [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! show warnings 2011/05/27 12:26 -0700, Daevid Vincent In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. Well, in general, of course there are problems for which no heuristic works--and indices _are_ heuristics. Houmuch real time does it take? I have two tables, one of addresses, another of the people living there s names, for the join wherof explain extended shows the same, and it does not bother me because it takes so little time. The output is only 1324 rows long. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using where; Using temporary; Using filesort
I sent this Friday, but it never made it to the list?! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, May 27, 2011 12:27 PM To: mysql@lists.mysql.com Subject: Using where; Using temporary; Using filesort I'm trying to optimize a query that doesn't seem all that complicated, however I can't seem to get it to not use a temp table and filesort. developer@vm_vz_daevid:~$ mysql --version mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 EXPLAIN EXTENDED SELECT -- d.date_release, -- d.dvd_title, -- s.type, -- s.id_place, s.scene_id AS index_id, s.dvd_id FROM dvds AS d JOIN scenes_list AS s ON s.dvd_id = d.dvd_id AND d.status = 'ok' AND d.date_release != '-00-00' ORDER BY d.date_release DESC, d.dvd_title ASC, s.type ASC, s.id_place ASC; *** 1. row *** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY,date_release,status,status_release key: status_release key_len: 1 ref: const rows: 1976 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: s type: ref possible_keys: dvd_id_2,dvd_id key: dvd_id key_len: 4 ref: videoszcontent.d.dvd_id rows: 6 Extra: Using where 2 rows in set, 1 warning (0.00 sec) There are proper indexes on most every column in both tables (as you can see there). [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! [b] The commented out columns above I thought might help with the ORDER BY for some reason from my reading here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html they did not. [c] lopping off the ORDER BY all together stops the Using temporary; Using filesort of course. Yeah! But now I'm left with a table of data in random order. Re-sorting it in PHP seems like an even bigger waste of cycles, when no doubt MySQL can sort hella-faster. [d] just doing ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents the using temporary but still does filesort and again I'm in the boat of [c] I guess my question is this: Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L. and no matter what, MySQL is going to give you a Cleveland Steamer? In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. d. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using where; Using temporary; Using filesort
Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal No. Optimisation is better in 5.6 than in 5.0, though. Did you try adding multi-column indexes to cover the join and the order by clause? 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L I don't know a general answer to that question. To figure out the answer in a particular case, I usually have to see the Create Table statements, see how the query performs with representative data, and experiment with various index setups. PB - On 5/31/2011 1:27 PM, Daevid Vincent wrote: I sent this Friday, but it never made it to the list?! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, May 27, 2011 12:27 PM To: mysql@lists.mysql.com Subject: Using where; Using temporary; Using filesort I'm trying to optimize a query that doesn't seem all that complicated, however I can't seem to get it to not use a temp table and filesort. developer@vm_vz_daevid:~$ mysql --version mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 EXPLAIN EXTENDED SELECT -- d.date_release, -- d.dvd_title, -- s.type, -- s.id_place, s.scene_id AS index_id, s.dvd_id FROM dvds AS d JOIN scenes_list AS s ON s.dvd_id = d.dvd_id AND d.status = 'ok' AND d.date_release != '-00-00' ORDER BY d.date_release DESC, d.dvd_title ASC, s.type ASC, s.id_place ASC; *** 1. row *** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY,date_release,status,status_release key: status_release key_len: 1 ref: const rows: 1976 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: s type: ref possible_keys: dvd_id_2,dvd_id key: dvd_id key_len: 4 ref: videoszcontent.d.dvd_id rows: 6 Extra: Using where 2 rows in set, 1 warning (0.00 sec) There are proper indexes on most every column in both tables (as you can see there). [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! [b] The commented out columns above I thought might help with the ORDER BY for some reason from my reading here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html they did not. [c] lopping off the ORDER BY all together stops the Using temporary; Using filesort of course. Yeah! But now I'm left with a table of data in random order. Re-sorting it in PHP seems like an even bigger waste of cycles, when no doubt MySQL can sort hella-faster. [d] just doing ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents the using temporary but still does filesort and again I'm in the boat of [c] I guess my question is this: Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L. and no matter what, MySQL is going to give you a Cleveland Steamer? In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. d. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
URGENT: Change Default Location of where Database Files get written?
I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: URGENT: Change Default Location of where Database Files get written?
Add: datadir=/path/to/datadir/mysql to your my.cnf file and restart mysql. -Original Message- From: Tina Matter [mailto:ti...@umich.edu] Sent: Friday, May 13, 2011 8:22 AM To: mysql@lists.mysql.com Subject: URGENT: Change Default Location of where Database Files get written? I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
On Fri, May 13, 2011 08:21, Tina Matter wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 Since your are on a linux box, the simplest method is to create the database, but no tables and then replace the directory with a symbolic link to the desired location. Make sure the permissions at the new directory match that of the other directories. If you are using innodb you will need to set it to use separate files for each table. Hope this helps. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
On Fri, May 13, 2011 at 9:21 AM, Tina Matter ti...@umich.edu wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location: /opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename http://lmgtfy.com/?q=mysql+location+of+database+files -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
There absolutely is; there is a configuration file belonging to MySQL named `my.cnf`. It can exist in many places and there's a hierarchal order of precedense. The most common of which is /etc/my.cnf. Within this file you may specify the `datadir` option to identify location you wish your data to reside. This is a static variable that can't be altered whilst the server is in motion and there are things you may need to do before considering changing this value. There is a wealth of documentation on this configuration file that can be found at... http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html HTH Andy On Fri, May 13, 2011 at 4:21 PM, Tina Matter ti...@umich.edu wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
2011/04/25 18:45 +, Larry McGhaw CREATE VIEW `myview2` AS SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName` FROM `mytable` a LEFT JOIN `types` b ON a.`Type` = b.`ID`; Well, for this construct IF(b.`Name` IS NULL, '', b.`Name`) there is a special function IFNULL: IFNULL(b.`Name`, '') It has the same special type-treatment that IF has. I quite often use it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
I would go with join rather than where condition. 2011/4/26 Halász Sándor h...@tbbs.net 2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
Hello Halбsz, Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Halбsz Sбndor h...@tbbs.net To: Andre Polykanine Date created: , 7:00:03 AM Subject: LEFT JOIN and WHERE: identical or not and what is better?, etc. 2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
On 04/26/11 05:32, Halász Sándor wrote: 2011/04/25 18:45 +, Larry McGhaw CREATE VIEW `myview2` AS SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName` FROM `mytable` a LEFT JOIN `types` b ON a.`Type` = b.`ID`; Well, for this construct IF(b.`Name` IS NULL, '', b.`Name`) there is a special function IFNULL: IFNULL(b.`Name`, '') It has the same special type-treatment that IF has. Wow, thanks! I wasn't aware of it, but this looks helpful in my case (at least simplifying those expressions)! Yours, Daniel -- http://www.pro-vegan.info/ -- Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz To go: Hea-Mon-Pri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
2011/04/26 17:55 +0300, Andre Polykanine Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! I think so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LEFT JOIN and WHERE: identical or not and what is better?
Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
'where' is a filter. You're limiting records based on a criterion. 'on' is used for joining. On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine an...@oire.org wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mmal...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
I can be wrong about that, but I think the difference between them should be irrelevant so it makes me think about a paranoiac thought. For me, the only difference is: Chose the one you feel better to understand your code. Am I wrong or not? -- João Cândido de Souza Neto Andre Polykanine an...@oire.org escreveu na mensagem news:199779304.20110425174...@oire.org... Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. Which one is faster? Probably the second since NULLs do not have to be considered -- probably not much faster though. Which one is better? That'll depend on your needs, if you only need records from both tables that have a matching row in the other, the second is better. If you need all blogs, even those without a matching user (can that even occur?), the first one is better. I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. JW On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine an...@oire.org wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LEFT JOIN and WHERE: identical or not and what is better?
Hi Andre, everybody! Andre Polykanine wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? In your subject line, you are mixing unrelated things: - LEFT JOIN is an alternative to INNER JOIN. It tell the database to return not only matching row combinations but also those where the first (left-hand) table has a row with a NULL column. - ON is an alternative to WHERE. For both aspects, the manual has more information than I will be able to think of in this mail. So the real difference between your statements is not LEFT JOIN vs WHERE, or ON vs WHERE, it is LEFT JOIN vs inner join. It will become important if you have rows in table Blogs whose column UserId holds NULL rather than any definite value. To understand that, you will have to read about NULL and the three-valued logic of SQL (whose comparisons can return true, false, and unknown). In general, an inner join should be faster - but who cares? SQL statements must be coded for correctness first, not for speed - and that will determine your choice of LEFT JOIN vs inner join. Tuning and optimization come later (in priority). The same holds for style etc: Clean programming is nice (and helpful in understanding and maintenance), but correctness comes first. Regards, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
Hello Daniel,
My best advice is to not use a custom MySQL function in a view when the
parameter to that function
is a column or expression that has the potential to result in NULL because of
being on the right side
of a left outer join (or the left side of a right outer join). This
particular set of circumstances
seems to cause MySQL to treat the resulting expression as unknown when used
in a where clause on the view
itself - such that any use of the expression in the where clause will evaluate
to unknown/false.
As a workaround - this view for example behaves as expected:
CREATE VIEW `myview2` AS
SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
Thanks
lm
-Original Message-
From: Daniel Kraft [mailto:d...@domob.eu]
Sent: Sunday, April 24, 2011 2:03 PM
To: Larry McGhaw
Cc: Daevid Vincent; mysql@lists.mysql.com
Subject: Re: WHERE does not work on calculated view field - Found word(s) list
error in the Text body
On 04/22/11 22:41, Larry McGhaw wrote:
It does appear to be some type of bug to me.
Hm... do you have an idea how to work around this bug then?
Yours,
Daniel
Clearly from the select, the Typename field is not null, as shown here.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview`;
++--+--+--+--+
| ID | Type | TypeName | TypeName Is NULL | TypeName IS NOT NULL |
++--+--+--+--+
| 1 | NULL | |0 |1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in any manner, no results are
returned.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where
TYPE
NAME IS NOT NULL;
Empty set (0.00 sec)
-Original Message-
From: Daniel Kraft [mailto:d...@domob.eu]
Sent: Friday, April 22, 2011 1:05 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: WHERE does not work on calculated view field - Found word(s)
list error in the Text body
Hi,
thanks for the fast reply!
On 04/22/11 21:39, Daevid Vincent wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Try this maybe:
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
When I try those, I get:
ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'
What would be the difference? (I've never used HAVING before.)
Yours,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
Hi, On 04/25/11 20:45, Larry McGhaw wrote: My best advice is to not use a custom MySQL function in a view when the parameter to that function is a column or expression that has the potential to result in NULL because of being on the right side of a left outer join (or the left side of a right outer join). This particular set of circumstances seems to cause MySQL to treat the resulting expression as unknown when used in a where clause on the view itself - such that any use of the expression in the where clause will evaluate to unknown/false. As a workaround - this view for example behaves as expected: CREATE VIEW `myview2` AS SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName` FROM `mytable` a LEFT JOIN `types` b ON a.`Type` = b.`ID`; now you mention it, it seems obvious -- but I didn't think about that solution before... But 'inlining' my function this way seems to fix the problem also in my real application. Thanks a lot! Yours, Daniel -- http://www.pro-vegan.info/ -- Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz To go: Hea-Mon-Pri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
On 04/22/11 22:41, Larry McGhaw wrote:
It does appear to be some type of bug to me.
Hm... do you have an idea how to work around this bug then?
Yours,
Daniel
Clearly from the select, the Typename field is not null, as shown here.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview`;
++--+--+--+--+
| ID | Type | TypeName | TypeName Is NULL | TypeName IS NOT NULL |
++--+--+--+--+
| 1 | NULL | |0 |1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in any manner, no results are returned.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where
TYPE
NAME IS NOT NULL;
Empty set (0.00 sec)
-Original Message-
From: Daniel Kraft [mailto:d...@domob.eu]
Sent: Friday, April 22, 2011 1:05 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: WHERE does not work on calculated view field - Found word(s) list
error in the Text body
Hi,
thanks for the fast reply!
On 04/22/11 21:39, Daevid Vincent wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Try this maybe:
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
When I try those, I get:
ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'
What would be the difference? (I've never used HAVING before.)
Yours,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field
Hi Daniel,
Could you check the 'myview' once again? I think you thought to create the
view as follows:
CREATE VIEW `myview2` AS SELECT a.*, EMPTY_STRING(b.`Name`) AS
`TypeName`FROM `mytable` a LEFT JOIN `types` b ON *a.ID* *= b.`ID`*;
Now your select queries will give results as follows:
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NULL;
+--+
| COUNT(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Regards,
Vinodh.k
MySQL DBA
On Sat, Apr 23, 2011 at 1:50 AM, Daniel Kraft d...@domob.eu wrote:
Hi Carsten,
On 04/22/11 22:11, Carsten Pedersen wrote:
On 22.04.2011 21:37, Daniel Kraft wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Hint: What's the output of SELECT * FROM `myview`?
I get:
mysql select * from myview;
++--+--+
| ID | Type | TypeName |
++--+--+
| 1 | NULL | |
++--+--+
1 row in set (0.00 sec)
mysql select *, `TypeName` IS NOT NULL from myview;
++--+--++
| ID | Type | TypeName | `TypeName` IS NOT NULL |
++--+--++
| 1 | NULL | | 1 |
++--+--++
1 row in set (0.00 sec)
Should this tell me something? To me, it looks as expected and fine.
Cheers,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=daffodil...@gmail.com
Re: WHERE does not work on calculated view field
Hi,
thanks for the answer!
On 04/23/11 11:33, ars k wrote:
Could you check the 'myview' once again? I think you thought to create the
view as follows:
CREATE VIEW `myview2` AS SELECT a.*, EMPTY_STRING(b.`Name`) AS
`TypeName`FROM `mytable` a LEFT JOIN `types` b ON *a.ID* *= b.`ID`*;
Hm, no, I don't think so -- what I want to achieve is to link the
Type-field (as index) in mytable to the corresponding entry in types, so
that I can get the name of this type (and other data in my real
application of course).
What you propose compares the ID of entries in mytable (customers, say)
to IDs of types, which doesn't make much sense to me. Or did I get this
wrong? (As I said, I'm more of less learning-by-doing and no expert!)
Yours,
Daniel
Now your select queries will give results as follows:
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+--+
| COUNT(*) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NULL;
+--+
| COUNT(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Regards,
Vinodh.k
MySQL DBA
On Sat, Apr 23, 2011 at 1:50 AM, Daniel Kraftd...@domob.eu wrote:
Hi Carsten,
On 04/22/11 22:11, Carsten Pedersen wrote:
On 22.04.2011 21:37, Daniel Kraft wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Hint: What's the output of SELECT * FROM `myview`?
I get:
mysql select * from myview;
++--+--+
| ID | Type | TypeName |
++--+--+
| 1 | NULL | |
++--+--+
1 row in set (0.00 sec)
mysql select *, `TypeName` IS NOT NULL from myview;
++--+--++
| ID | Type | TypeName | `TypeName` IS NOT NULL |
++--+--++
| 1 | NULL | | 1 |
++--+--++
1 row in set (0.00 sec)
Should this tell me something? To me, it looks as expected and fine.
Cheers,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=daffodil...@gmail.com
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WHERE does not work on calculated view field
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Yours,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE does not work on calculated view field
-Original Message-
From: Daniel Kraft [mailto:d...@domob.eu]
Sent: Friday, April 22, 2011 12:37 PM
To: mysql@lists.mysql.com
Subject: WHERE does not work on calculated view field
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Try this maybe:
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field
Hi,
thanks for the fast reply!
On 04/22/11 21:39, Daevid Vincent wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Try this maybe:
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
When I try those, I get:
ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'
What would be the difference? (I've never used HAVING before.)
Yours,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field
On 22.04.2011 21:37, Daniel Kraft wrote:
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a lot!
Hint: What's the output of SELECT * FROM `myview`?
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field
Hi Carsten,
On 04/22/11 22:11, Carsten Pedersen wrote:
On 22.04.2011 21:37, Daniel Kraft wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Hint: What's the output of SELECT * FROM `myview`?
I get:
mysql select * from myview;
++--+--+
| ID | Type | TypeName |
++--+--+
| 1 | NULL | |
++--+--+
1 row in set (0.00 sec)
mysql select *, `TypeName` IS NOT NULL from myview;
++--+--++
| ID | Type | TypeName | `TypeName` IS NOT NULL |
++--+--++
| 1 | NULL | | 1 |
++--+--++
1 row in set (0.00 sec)
Should this tell me something? To me, it looks as expected and fine.
Cheers,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
It does appear to be some type of bug to me.
Clearly from the select, the Typename field is not null, as shown here.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview`;
++--+--+--+--+
| ID | Type | TypeName | TypeName Is NULL | TypeName IS NOT NULL |
++--+--+--+--+
| 1 | NULL | |0 |1 |
++--+--+--+--+
1 row in set (0.00 sec)
But when referenced in the where clause in any manner, no results are returned.
mysql SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where TYPE
NAME IS NOT NULL;
Empty set (0.00 sec)
-Original Message-
From: Daniel Kraft [mailto:d...@domob.eu]
Sent: Friday, April 22, 2011 1:05 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: WHERE does not work on calculated view field - Found word(s) list
error in the Text body
Hi,
thanks for the fast reply!
On 04/22/11 21:39, Daevid Vincent wrote:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a
lot!
Try this maybe:
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
When I try those, I get:
ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'
What would be the difference? (I've never used HAVING before.)
Yours,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=lmcg...@connx.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
On 22.04.2011 22:41, Larry McGhaw wrote: It does appear to be some type of bug to me. I agree. I was thrown by Daniels first and third comment, which I guess should read second and third I reproduced the behavior in 5.1.53-community on Windows. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unknown column 'users.users_id' in 'where clause'
Thanks, but how can I pass the current users value from the other query ? On Thu, Feb 3, 2011 at 4:22 PM, Simcha Younger sim...@syounger.com wrote: On Thu, 3 Feb 2011 13:55:36 + Tompkins Neil neil.tompk...@googlemail.com wrote: SELECT DISTINCT(away_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.away_users_id = *users.users_id*) Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause' for the part of the statement that I've marked as *bold* and how I can over come this problem ? You have to add the users table to the subquery. Your subquery only has the fixtures_results table in the 'from' clause, and the subquery is not aware of the tables you use in the outer query. -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Unknown column 'users.users_id' in 'where clause'
Hi, I've the following SELECT statement SELECT users.gamer_tag, UNIX_TIMESTAMP(users.created_on) AS time_registered, (SELECT fixtures_results.last_changed FROM fixtures_results WHERE (home_users_id = users.users_id AND home_teams_id = users_teams.teams_id) OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id) AND fixtures_results.status = 'approved' ORDER BY fixtures_results.last_changed ASC LIMIT 1) AS time_at_team, (SELECT COUNT(DISTINCT(teams_id)) FROM (SELECT DISTINCT(home_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.home_users_id = *users.users_id*) AND fixtures_results.status = 'approved' UNION ALL SELECT DISTINCT(away_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.away_users_id = *users.users_id*) AND fixtures_results.status = 'approved') s1) AS number_teams FROM users INNER JOIN users_teams ON users.users_id = users_teams.users_id WHERE UNIX_TIMESTAMP(users.created_on) 0 AND (SELECT fixtures_results.last_changed FROM fixtures_results WHERE (home_users_id = users.users_id AND home_teams_id = users_teams.teams_id) OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id) AND fixtures_results.status = 'approved' ORDER BY fixtures_results.last_changed ASC LIMIT 1) '' ORDER BY time_at_team ASC, time_registered ASC Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause' for the part of the statement that I've marked as *bold* and how I can over come this problem ? Cheers Neil
Re: Unknown column 'users.users_id' in 'where clause'
On Thu, 3 Feb 2011 13:55:36 + Tompkins Neil neil.tompk...@googlemail.com wrote: SELECT DISTINCT(away_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.away_users_id = *users.users_id*) Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause' for the part of the statement that I've marked as *bold* and how I can over come this problem ? You have to add the users table to the subquery. Your subquery only has the fixtures_results table in the 'from' clause, and the subquery is not aware of the tables you use in the outer query. -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with Date in Where Clause
Greetings All, I am looking for a little help in setting a where clause. I have a dateAdded field that is a DATETIME field. I am looking to pull records from Midnight to midnight the previous day. I thought just passing the date (without time) would get it but I keep getting an empty record set. So looking for something that works a bit better. Any suggestions? Blessed Be Phillip Never ascribe to malice what can be explained by incompetence -- Hanlon's Razor
Re: Help with Date in Where Clause
On Monday 31 January 2011 21:12, Phillip Baker wrote: Greetings All, I am looking for a little help in setting a where clause. I have a dateAdded field that is a DATETIME field. I am looking to pull records from Midnight to midnight the previous day. I thought just passing the date (without time) would get it but I keep getting an empty record set. So looking for something that works a bit better. select * from your_table where convert(dateAdded, date)='2011-01-31'; -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with Date in Where Clause
Thank you very much Jørn Blessed Be Phillip Never ascribe to malice what can be explained by incompetence -- Hanlon's Razor On Mon, Jan 31, 2011 at 1:18 PM, Jørn Dahl-Stamnes sq...@dahl-stamnes.netwrote: Jørn
Re: Help with Date in Where Clause
On 1/31/2011 15:12, Phillip Baker wrote: Greetings All, I am looking for a little help in setting a where clause. I have a dateAdded field that is a DATETIME field. I am looking to pull records from Midnight to midnight the previous day. I thought just passing the date (without time) would get it but I keep getting an empty record set. So looking for something that works a bit better. Any suggestions? Blessed Be Phillip Never ascribe to malice what can be explained by incompetence -- Hanlon's Razor All of the datetime values for yesterday actually exist as a range of datetime values between midnight that morning (inclusive) and midnight the next morning (not part of the search). So your WHERE clause needs to resemble ... WHERE dtcolumn = '2011-01-21 00:00:00' and dtcolumn '2011-01-22 00:00:00' This pattern has the added advantage of not eliminating the possibility of using an INDEX on the dtcolumn column by wrapping it inside a function. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
