2012/03/29

SimpleDB - "Too many value tests per predicate in the query expression"

當用Simple下Querey的時候,condition超過20個就會出現下面的錯誤
Too many value tests per predicate in the query expression

Client error : Too many value tests per predicate in the query expression.

像這類的語法都不行:
SELECT * FROM domain WHERE id='1' OR id='2' OR ... id='20' OR id='21'

SELECT * FROM domain WHERE id in ('1', '2', ... '20', '21')

SELECT * FROM domain WHERE id in ('1', '2', ... '20') OR id in ('21', ...)

只能考慮分批一次抓20個,然後再合併處理。



2012/05/07 發現一種可以破解20個條件限制的辦法:
SELECT * FROM `domain` WHERE 
  account in ('key1', 'key2', 'key3', 'key4', 'key5', 'key6', 'key7', 'key8', 'key9', 'key10', 
    'key11', 'key12', 'key13', 'key14', 'key15', 'key16', 'key17', 'key18', 'key19', 'key20') OR 
  dummy IS NOT NULL OR 
  account in ('key21', 'key22', 'key23', 'key24', 'key25', 'key26', 'key27', 'key28', 'key29', 'key30', 
    'key31', 'key32', 'key33', 'key34', 'key35', 'key36', 'key37', 'key38', 'key39', 'key40')
沒錯,就是加了"OR dummy IS NOT NULL"這個多餘的條件在中間就可以下達多個條件了!

不過當你有這樣的需求時,就應該想一下是不是設計面有問題,或是該用 relational database了。



參考資料
Amazon SimpleDB » Developer Guide » Amazon SimpleDB Concepts » Limits
Query 101: Building Amazon SimpleDB Queries


1 則留言:

  1. SDB Explorer has been made as an industry leading graphical user interface (GUI) to explore Amazon SimpleDB service. SDB Explorer facilitates core functionality of Amazon’s NoSQL SimpleDB in productive way. You can run Amazon Simpledb SELECT queries and perform any operation on result sets.

    An enhancement to existing Run Select Query feature has been made for saving your time like in any standard Database Query Tool. SDBExplorer supports following features -

    Highlight the query.
    Comment-out the query.
    Editing queries.

    Highlight the query - Highlight a part of a query and execute, only the highlighted part will be executed i.e. with today’s launch you can highlight a part of query with your mouse and execute only the highlighted part from the whole query.

    Comment-out the query - Comment-out Amazon SimpleDB queries that you do not want to execute i.e. you can comment out Amazon SimpleDB query which you don’t want to execute. The Commented query will not get executed.

    Editing queries - Editing queries using SDB Explorer has now become much easier. It Supports basic text editing features like copy, paste, undo and redo, that you would be able to do in any of the commonly used database query tools.

    回覆刪除