如何强制表选择越过块
如何使 Sybase 的数据库引擎以非数字顺序返回未排序的记录列表？
~~~
我有一个问题，我需要在应用程序中重现错误，我从按顺序生成 ID 的表中进行选择，但 ID 不是选择中的最后一个。
让我解释。
ID STATUS
_____________
1234 C
1235 C
1236 O
上面是3个ID。 我有代码，其中这些将是 a
select @p_id = ID from table where (conditions).
但是，没有子句来检查 status = 'O'（打开）。 请记住，Sybase 将最后返回的记录保存到变量中。
~~~~~
我被要求向测试团队提供一些会使结果不起作用的东西。 如果 Sybase 在无序列表中选择上述内容，它可能会按升序显示，或者，如果数据库引擎需要更改存储数据块或某些技术魔法内容，则顺序可能会混乱。 最初的错误是当过程返回 1234 而不是 1236 时。
有没有一种方法可以让我100% 保证 Sybase 将搜索数据块并且必须有效地双倍返回“打破”升序搜索，并且返回的不是最后一条记录，而是任何其他记录？ （除了最大记录之外的所有记录最终都会出错，因为它们都是“已关闭”）
我想要某种神奇的 SQL 代码来确保事物不会按照精确的数字顺序搜索表。 理想情况下，我希望不必更改该过程，因为测试团队希望看到完全相同的过程被破坏（就像按 id desc 排序会伪造结果一样简单）。
How can I make Sybase's database engine return an unsorted list of records in non-numeric order?
~~~
I have an issue where I need to reproduce an error in the application where I select from a table where the ID is generated in sequence, but the ID is not the last one in the selection.
Let me explain.
ID STATUS
_____________
1234 C
1235 C
1236 O
Above is 3 IDs. I had code where these would be the results of a
select @p_id = ID from table where (conditions).
However, there wasn't a clause to check for status = 'O' (open). Remember Sybase saves the last returned record into a variable.
~~~~~
I'm being asked to give the testing team something that will make the results not work. If Sybase selects the above in an unordered list, it could appear in ascending order, or, if the database engine needs to change blocks of stored data or something technical magic stuff, the order could be messed up. The original error was when the procedure would return say 1234 instead of 1236.
Is there a way that I can have a 100% guarantee that Sybase will search over a block of data and have to double back, effectively 'breaking' the ascending search, and returning not the last record, but any other one? (all records except the maximum will end up erroring, because they are all 'Closed')
I want some sort of magical SQL code that will make sure things don't search the table in exactly numeric order. Ideally I'd like to not have to change the procedure, as the test team want to see the exact same procedure breaking (as easy as plonking a order by id desc would fudge the results).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果不指定顺序，则无法保证结果的返回顺序。 然而，索引的构建方式将取决于插入顺序、索引类型和索引键的内容。
执行此类单例 SELECT 通常不是一个好主意。 您应该始终使用
WHERE子句指定特定记录，或者使用游标、TOPn 或类似内容。 当有人试图理解您的代码时，问题就出现了，因为某些数据库在看到多次命中时采用第一个值，有些采用最后值，有些采用随机值（他们称之为“实现定义的”），有些会抛出错误。这是否与 1156837 有关？ :)
If you don't specify an order, there is no way to guarantee the return order of the results. It will be however the index is built - and can depend on the order of insertion, the type of index, and the content of index keys.
It's generally a bad idea to do those sorts of singleton
SELECTs. You should always specify a specific record with theWHEREclause, or use a cursor, orTOPn or similar. The problem comes when someone tries to understand your code, because some databases when they see multiple hits take the first value, some take the last value, some take a random value (they call that "implementation-defined"), and some throw an error.Is this by any chance related to 1156837? :)