WQL SQL error about limit and found_by?

Support Ticket

+status
answered
 

at least it's new to me, from http://thefarmschool.wagn.org/all_emails

 

No results? ActiveRecord::StatementInvalid :: Mysql2::Error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery': (select t.name from cards t where t.id in (select tx.id from cards tx where tx.id in (select txx.left_id from cards txx where txx.right_id in (select txxx.id from cards txxx where txxx.key = '*account' and txxx.trash is false ) and txx.trash is false ) and tx.id not in (select txx.id from cards txx where ((txx.id in (select txxx.right_id from cards txxx JOIN card_revisions AS txxx_rev ON txxx.current_revision_id = txxx_rev.id where txxx.left_id in (select txxxx.id from cards txxxx where txxxx.key = 'inactive' and txxxx.trash is false ) and txxx_rev.content = '1' and txxx.trash is false ) or txx.id in (select txxx.left_id from cards txxx JOIN card_revisions AS txxx_rev ON txxx.current_revision_id = txxx_rev.id where txxx.right_id in (select txxxx.id from cards txxxx where txxxx.key = 'inactive' and txxxx.trash is false ) and txxx_rev.content = '1' and txxx.trash is false )) or txx.type_id = '12') and txx.trash is false ) and tx.trash is false LIMIT 1000000 ) and t.id not in (select tx.id from cards tx where tx.id in (select txx.left_id from cards txx JOIN card_revisions AS txx_rev ON txx.current_revision_id = txx_rev.id where txx.right_id in (select txxx.id from cards txxx where txxx.key = 'email_not_working' and txxx.trash is false ) and txx_rev.content = '1' and txx.trash is false ) and tx.trash is false ) and t.trash is false ORDER BY t.updated_at desc LIMIT 20 OFFSET 0) :: {"found_by": "active people", "not": {"right_plus": ["email not working", {"content": "1"} ] }, "append":"*email", "view":"content" }

 

same issue as this: star-count not working on one card.

 

You can't have a limit on a subquery, at least in mysql (dunno about postgres). "Subquery" in both WQL and SQL means a query nested inside another. So "limit" can only be used on the outermost query.

 

Solution: strip limits from WQL subqueries.

 

Workaround: don't use limits in subqueries :)

--Ethan McCutchen.....2013-09-02 16:01:56 +0000