db stats for 2.16.11 SELECT `labour`.`lab_description`, `labour`.`lab_id` FROM `labour` WHERE ((`labour`.`lab_id` IS NOT NULL) AND (`labour`.`lab_term_date` IS NULL)) ORDER BY `labour`.`lab_description`, `labour`.`lab_id`;
1.11ms --------------------------- db stats for 2.9.11 SET FOREIGN_KEY_CHECKS=1; 1.31ms SET sql_mode='NO_BACKSLASH_ESCAPES'; 1.29ms SELECT labour.lab_description, labour.lab_id FROM labour WHERE ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY labour.lab_description, labour.lab_id; 9.32ms SELECT labour.lab_description, labour.lab_id FROM labour WHERE ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY labour.lab_description, labour.lab_id; 9.07ms SELECT count(*) FROM labour LEFT JOIN labprice ON ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE (labprice.lp_id IS NOT NULL); 2.57ms SELECT labprice.lp_id, labprice.lp_lab_id, labprice.lp_unit_price, labprice.lp_comment, labprice.lp_intro_date FROM labour LEFT JOIN labprice ON ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE (labprice.lp_id IS NOT NULL) ORDER BY labour.lab_description; 47.51ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 97) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.12ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 112) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.09ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 125) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 120) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 128) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 118) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 33) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 114) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 119) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 18) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 18) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 96) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 96) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 27) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 115) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 121) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.07ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 31) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 84) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 75) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 28) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 78) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 85) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 87) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 127) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 99) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.32ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 11) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 1.73ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 23) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 103) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 102) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 73) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 36) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 105) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 89) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 37) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 37) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 123) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 124) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 35) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 107) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 107) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 19) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 94) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 83) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 32) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 95) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 88) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 17) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 74) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 106) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 101) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 26) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 26) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 93) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 22) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 22) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 22) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 20) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 40) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 126) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms On Saturday, May 12, 2018 at 12:38:33 PM UTC-4, Anthony wrote: > > Can you use db._timings to grab the generated SQL, both under 2.9.11 and > 2.16.11, to see what the differences are? > > Anthony > > On Saturday, May 12, 2018 at 11:20:50 AM UTC-4, [email protected] > <javascript:> wrote: >> >> After update from web2py 2.9.11 (mysql 5.6.37) to web2py 2.16.11 (mysql >> 5.6.40) the SQLFORM.smartgrid shows zero results and at the top right >> displays a message: Query Not Supported: (1066, "Not unique table/alias: >> 'labprice'"). >> This same code works in 2.9.11: >> db.labprice._common_filter = lambda query: >> db.labprice.lp_term_date==None >> db.labour._common_filter = lambda query: db.labour.lab_term_date==None >> left=db.labprice.on(db.labour.lab_id==db.labprice.lp_lab_id) >> orderby=[db.labour.lab_description] >> form >> =SQLFORM.smartgrid(db.labprice,left=left,fields=fields,headers=headers,paginate='all',maxtextlengths=maxtextlengths,maxtextlength=40, >> >> buttons_placement='left',links_placement='left',deletable=False,orderby=orderby,linked_tables=['repair']) >> Enter code here... >> >> Any help in making it work in the newer version of web2py will be >> appreciated. >> Thanks >> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
