I have a group query which returns different results, first time it returns wrong result and then returns correct result.
我有一个组查询，它返回不同的结果，第一次返回错误的结果，然后返回正确的结果。
select
c.customer_name, c.customer_address, invoice_no,
sum(nvl(invoiced_amount,0)) invoice_amount,
sum(nvl(received_amount,0)) received_amount,
sum(nvl(returned_amount,0)) returned_amount
from sales_detail d, sales s, customer c
where d.inquiry_id = s.inquiry_id
and s.customer_id = c.customer_id
and s.sale_date between '01-jan-2012' and '31-dec-2012'
group by c.customer_name, c.customer_address, invoice_no
It returns multiple rows and provide correct rows count but doubles the value of some invoices, usually it happens when it runs first time and later gives correct results.
Following is an example:
它返回多个行并提供正确的行数，但是会使一些发票的值加倍，通常在它第一次运行时发生，稍后给出正确的结果。下面是一个例子:
Actual data in database
实际数据在数据库
invoice_no | invoiced_amount | received_amount | returned_amount
000010020 | 500 | 230 | 0
Result return by the query
查询结果返回。
invoice_no | invoiced_amount | received_amount | returned_amount
000010020 | 1000 | 460 | 0
Query started behaving like this some days ago, otherwise was working perfectly.
查询在几天前就开始这样做了，否则就很完美了。
Additional information
额外的信息
- There are 5 indexes on sales_detail table
- 在sales_detail表上有5个索引。
- Customer is a view, physical table is in another schema with the same name
- Customer是视图，物理表位于另一个具有相同名称的模式中。
Environment information
环境信息
- OS: Windows 2003 Server
- 操作系统:Windows 2003服务器
- Database: Oracle 9.0.1.1 Enterprise
- 数据库:甲骨文9.0.1.1企业
2 个解决方案
#1
2
This is not a question we can answer. You have a simple GROUP BY query and it will sum those attributes in a consistent fashion. If you get different results it is because the underlying data is different when you run the query.
这不是我们能回答的问题。通过查询，您有一个简单的组，它将以一致的方式对这些属性进行求和。如果您得到不同的结果，那是因为在运行查询时底层数据是不同的。
You provide a sample of the "actual data" but neglect to say which table it comes from. Let's assume it is the sales_detail table. If there are records in a table which sum to X but the query returns an aggregated value of 2 X you must have double counting. That is, the joins in the query are causing you to get two instances of each sales_detail record.
您提供了一个“实际数据”的示例，但忽略了它来自哪个表。假设它是sales_detail表。如果表中有对X求和的记录，但查询返回的值为2 X，则必须重复计算。也就是说，查询中的连接导致您获得每个sales_detail记录的两个实例。
Now you also say this is a transient phenomenon. If so, then it seems likely that something is temporarily spawning a second version of some record. Perhaps you have an EOD process which generates a additional customer record for some unfathomable reason, and then later clears it down - or closes off the old one?
现在你也说这是一个暂时的现象。如果是这样的话，那么很有可能是某个东西暂时产生了另一个版本的记录。也许您有一个EOD过程，它会生成一个额外的客户记录，以达到某种不可测的原因，然后再将其清除——或者关闭旧的?
This is the limit of the help we can give you. This is not an Oracle problem, this is some peculiarity in your database. You can see the data, we can't. You know - or should know - the business processes, we don't.
这是我们能给你的帮助的限度。这不是一个Oracle问题，这是数据库中的一些特性。你可以看到数据，我们不能。你知道——或者应该知道——业务流程，我们不知道。
So all we can do is give you some pointers.
我们能做的就是给你一些指针。
"usually it happens when it runs first time and later gives correct results."
“通常情况下，当它第一次运行时就会发生，之后会给出正确的结果。”
How have you established this pattern? "Usually" is the same as "always": you're going to need more precision to help you solve this. What investigation have you done to discover why the results vary? For instance, have you established what other other activity is happening when you run this query? Have you listed out the raw data before you run the query?
你是如何建立这种模式的?“通常”和“总是”是一样的:你需要更精确的来帮助你解决这个问题。你做了什么调查来发现为什么结果会有所不同?例如，当您运行这个查询时，您是否确定了其他其他活动正在发生什么?在运行查询之前，您已经列出了原始数据吗?
"Query started behaving like this some days ago, otherwise was working perfectly."
“几天前，查询就开始这样做了，否则就完全正常了。”
So what changed a few days ago?
那么几天前发生了什么变化呢?
"Invoices have been checked individually"
“发票已单独核对”
Yes but you have a multiple table join. Spurious records or poorly-written conditions can result in the same record being joined more than once.
是的，但是您有一个多表连接。伪造的记录或较差的书面条件可以导致同一记录被多次加入。
"it might be a bug"
"这可能是个错误"
Possibly. You're using the initial production release of a major version, and those are notoriously prone to bugs. However, it seems unlikely that software, even a point zero version, should suddenly start exhibiting buggy behaviour if - as you assert - nothing has changed.
可能。您使用的是一个主要版本的最初版本，而这些版本很容易出现bug。然而，即使是零版本的软件也不太可能突然开始表现出有bug的行为，如果——正如你断言的那样——什么都没有改变。
But even if you're right and it is a bug, that knowledge doesn't help you. You're using the base release of Oracle 9iR1, a version of the product which has been obsolete for over a decade. So obviously your organisation isn't keen on upgrading, and presumably doesn't pay for Support so patching is not an option (even if Oracle would furnish you with patches for such old software, which they won't).
但即使你是对的，这是一个错误，这些知识并不能帮助你。你使用的是Oracle 9iR1的基本版本，这个版本已经过时了十多年。很明显，你的组织并不热衷于升级，而且大概不会支付支持，所以patching不是一种选择(即使Oracle会为你提供这些旧软件的补丁，他们不会这样做)。
So, if it is a bug you'll need to track it down yourself and come up with a workaround yourself. Good luck with that.
所以，如果它是一个bug，你需要自己追踪它，然后自己解决问题。祝你好运。
#2
0
It looks to me, either the customer table or sales table has a duplicate or the join is not on PK.
在我看来，无论是customer表还是sales表都有一个副本，或者join不是在PK上。
#1
2
This is not a question we can answer. You have a simple GROUP BY query and it will sum those attributes in a consistent fashion. If you get different results it is because the underlying data is different when you run the query.
这不是我们能回答的问题。通过查询，您有一个简单的组，它将以一致的方式对这些属性进行求和。如果您得到不同的结果，那是因为在运行查询时底层数据是不同的。
You provide a sample of the "actual data" but neglect to say which table it comes from. Let's assume it is the sales_detail table. If there are records in a table which sum to X but the query returns an aggregated value of 2 X you must have double counting. That is, the joins in the query are causing you to get two instances of each sales_detail record.
您提供了一个“实际数据”的示例，但忽略了它来自哪个表。假设它是sales_detail表。如果表中有对X求和的记录，但查询返回的值为2 X，则必须重复计算。也就是说，查询中的连接导致您获得每个sales_detail记录的两个实例。
Now you also say this is a transient phenomenon. If so, then it seems likely that something is temporarily spawning a second version of some record. Perhaps you have an EOD process which generates a additional customer record for some unfathomable reason, and then later clears it down - or closes off the old one?
现在你也说这是一个暂时的现象。如果是这样的话，那么很有可能是某个东西暂时产生了另一个版本的记录。也许您有一个EOD过程，它会生成一个额外的客户记录，以达到某种不可测的原因，然后再将其清除——或者关闭旧的?
This is the limit of the help we can give you. This is not an Oracle problem, this is some peculiarity in your database. You can see the data, we can't. You know - or should know - the business processes, we don't.
这是我们能给你的帮助的限度。这不是一个Oracle问题，这是数据库中的一些特性。你可以看到数据，我们不能。你知道——或者应该知道——业务流程，我们不知道。
So all we can do is give you some pointers.
我们能做的就是给你一些指针。
"usually it happens when it runs first time and later gives correct results."
“通常情况下，当它第一次运行时就会发生，之后会给出正确的结果。”
How have you established this pattern? "Usually" is the same as "always": you're going to need more precision to help you solve this. What investigation have you done to discover why the results vary? For instance, have you established what other other activity is happening when you run this query? Have you listed out the raw data before you run the query?
你是如何建立这种模式的?“通常”和“总是”是一样的:你需要更精确的来帮助你解决这个问题。你做了什么调查来发现为什么结果会有所不同?例如，当您运行这个查询时，您是否确定了其他其他活动正在发生什么?在运行查询之前，您已经列出了原始数据吗?
"Query started behaving like this some days ago, otherwise was working perfectly."
“几天前，查询就开始这样做了，否则就完全正常了。”
So what changed a few days ago?
那么几天前发生了什么变化呢?
"Invoices have been checked individually"
“发票已单独核对”
Yes but you have a multiple table join. Spurious records or poorly-written conditions can result in the same record being joined more than once.
是的，但是您有一个多表连接。伪造的记录或较差的书面条件可以导致同一记录被多次加入。
"it might be a bug"
"这可能是个错误"
Possibly. You're using the initial production release of a major version, and those are notoriously prone to bugs. However, it seems unlikely that software, even a point zero version, should suddenly start exhibiting buggy behaviour if - as you assert - nothing has changed.
可能。您使用的是一个主要版本的最初版本，而这些版本很容易出现bug。然而，即使是零版本的软件也不太可能突然开始表现出有bug的行为，如果——正如你断言的那样——什么都没有改变。
But even if you're right and it is a bug, that knowledge doesn't help you. You're using the base release of Oracle 9iR1, a version of the product which has been obsolete for over a decade. So obviously your organisation isn't keen on upgrading, and presumably doesn't pay for Support so patching is not an option (even if Oracle would furnish you with patches for such old software, which they won't).
但即使你是对的，这是一个错误，这些知识并不能帮助你。你使用的是Oracle 9iR1的基本版本，这个版本已经过时了十多年。很明显，你的组织并不热衷于升级，而且大概不会支付支持，所以patching不是一种选择(即使Oracle会为你提供这些旧软件的补丁，他们不会这样做)。
So, if it is a bug you'll need to track it down yourself and come up with a workaround yourself. Good luck with that.
所以，如果它是一个bug，你需要自己追踪它，然后自己解决问题。祝你好运。
#2
0
It looks to me, either the customer table or sales table has a duplicate or the join is not on PK.
在我看来，无论是customer表还是sales表都有一个副本，或者join不是在PK上。