sql查询执行结果和执行子查询结果不一致数据错误，子查询结果是正确的。帮忙看看什么原因呢

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】42.1
【问题描述】如下sql执行结果错误，单独子查询结果正常，如果在子查询中加入no_rewrite，sql执行结果正常，帮忙看看什么原因呢？

select  a.q_naire_id, a.diagnostician_id, school_code, school_term_id,concat('反向互斥问卷全选最左侧或最右侧答案！', a.q_naire_id) invalid_reason 
from
(
		SELECT  
			distinct r.q_naire_id, a.diagnostician_id, a.school_code, a.school_term_id, is_min_answer, count(distinct is_min_answer) minc
		FROM
			tmp_mazg_L_R a
			join dw_naire_answer_rec r on a.naire_answer_rec_id = r.naire_answer_rec_id
			join dw_question q on a.q_id=q.q_id
		where 
			a.school_code='fsswgyxx' and a.school_term_id='2321'
		group by r.q_naire_id, a.diagnostician_id
		HAVING (minc=1 and is_min_answer=1)
) a

sql和执行子查询结果不一致。

1 个赞
#3

老师，麻烦提供下相关表结构，现场2种场景的执行计划。

1 个赞
#4

完整sql的执行计划

===================================================================
|ID|OPERATOR                       |NAME    |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------------
|0 |MERGE GROUP BY                 |        |1377    |5815101     |
|1 |└─PARTITION SORT               |        |275304  |5737821     |
|2 |  └─HASH JOIN                  |        |275304  |5645341     |
|3 |    ├─HASH JOIN                |        |279447  |147127      |
|4 |    │ ├─PX COORDINATOR         |        |19296   |9841        |
|5 |    │ │ └─EXCHANGE OUT DISTR   |:EX10000|19296   |6871        |
|6 |    │ │   └─TABLE FULL SCAN    |q(q_id) |19296   |248         |
|7 |    │ └─TABLE FULL SCAN        |a       |278331  |40651       |
|8 |    └─PX COORDINATOR           |        |1800426 |4848310     |
|9 |      └─EXCHANGE OUT DISTR     |:EX20000|1800426 |3379728     |
|10|        └─PX PARTITION ITERATOR|        |1800426 |79041       |
|11|          └─TABLE FULL SCAN    |r       |1800426 |79041       |
===================================================================
Outputs & filters:
-------------------------------------
  0 - output([r.q_naire_id(0x7f5600242c70)], [a.diagnostician_id(0x7f5600243270)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], 
      [concat('反向互斥问卷全选最左侧或最右侧答案！', r.q_naire_id(0x7f5600242c70))(0x7f5600249000)]), filter([T_FUN_COUNT(distinct cast(1, 
      INT(1, 0))(0x7f560024f120))(0x7f56002441a0) = 1(0x7f56002454c0)], [a.is_min_answer(0x7f5600243eb0) = 1(0x7f56002464c0)]), rowset=256
      group([r.q_naire_id(0x7f5600242c70)], [a.diagnostician_id(0x7f5600243270)]), agg_func([T_FUN_COUNT(distinct cast(1, INT(1, 0))(0x7f560024f120))(0x7f56002441a0)])
  1 - output([r.q_naire_id(0x7f5600242c70)], [a.diagnostician_id(0x7f5600243270)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], 
      [a.is_min_answer(0x7f5600243eb0)]), filter(nil), rowset=256
      sort_keys([HASH(r.q_naire_id(0x7f5600242c70), a.diagnostician_id(0x7f5600243270))(0x7f5b67eadd30), ASC], [r.q_naire_id(0x7f5600242c70), ASC], [a.diagnostician_id(0x7f5600243270),
       ASC])
  2 - output([r.q_naire_id(0x7f5600242c70)], [a.diagnostician_id(0x7f5600243270)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], 
      [a.is_min_answer(0x7f5600243eb0)]), filter(nil), rowset=256
      equal_conds([a.naire_answer_rec_id(0x7f560023adb0) = r.naire_answer_rec_id(0x7f560023b0a0)(0x7f560023a660)]), other_conds(nil)
  3 - output([a.diagnostician_id(0x7f5600243270)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], [a.naire_answer_rec_id(0x7f560023adb0)],
       [a.is_min_answer(0x7f5600243eb0)]), filter(nil), rowset=256
      equal_conds([a.q_id(0x7f560023e590) = q.q_id(0x7f560023e880)(0x7f560023de40)]), other_conds(nil)
  4 - output([q.q_id(0x7f560023e880)]), filter(nil), rowset=256
  5 - output([q.q_id(0x7f560023e880)]), filter(nil), rowset=256
      is_single, dop=1
  6 - output([q.q_id(0x7f560023e880)]), filter(nil), rowset=256
      access([q.q_id(0x7f560023e880)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([q.q_id(0x7f560023e880)]), range(MIN ; MAX)always true
  7 - output([a.naire_answer_rec_id(0x7f560023adb0)], [a.q_id(0x7f560023e590)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], [a.diagnostician_id(0x7f5600243270)],
       [a.is_min_answer(0x7f5600243eb0)]), filter([a.school_code(0x7f560023fae0) = 'fsswgyxx'(0x7f560023f390)], [cast(a.school_term_id(0x7f5600240d40), DECIMAL(11,
       0))(0x7f56002412b0) = cast('2321', DECIMAL(1, -1))(0x7f5600241e00)(0x7f56002405f0)]), rowset=256
      access([a.naire_answer_rec_id(0x7f560023adb0)], [a.q_id(0x7f560023e590)], [a.school_code(0x7f560023fae0)], [a.school_term_id(0x7f5600240d40)], [a.diagnostician_id(0x7f5600243270)],
       [a.is_min_answer(0x7f5600243eb0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false,false], 
      range_key([a.__pk_increment(0x7f560024aa10)]), range(MIN ; MAX)always true
  8 - output([r.q_naire_id(0x7f5600242c70)], [r.naire_answer_rec_id(0x7f560023b0a0)]), filter(nil), rowset=256
  9 - output([r.q_naire_id(0x7f5600242c70)], [r.naire_answer_rec_id(0x7f560023b0a0)]), filter(nil), rowset=256
      dop=1
 10 - output([r.naire_answer_rec_id(0x7f560023b0a0)], [r.q_naire_id(0x7f5600242c70)]), filter(nil), rowset=256
      force partition granule
 11 - output([r.naire_answer_rec_id(0x7f560023b0a0)], [r.q_naire_id(0x7f5600242c70)]), filter(nil), rowset=256
      access([r.naire_answer_rec_id(0x7f560023b0a0)], [r.q_naire_id(0x7f5600242c70)]), partitions(p[0-99])
      is_index_back=false, is_global_index=false, 
      range_key([r.naire_answer_rec_id(0x7f560023b0a0)], [r.school_code(0x7f5600239d40)]), range(MIN,MIN ; MAX,MAX)always true
Used Hint:
-------------------------------------
  /*+
      
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1
  stmt_id:2, SEL$2 > SEL$6E5640F5 > SEL$6942E515 > SEL$1EED3BDE > SEL$25931A74 > SEL$6B888FCB
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH_AGGREGATION(@"SEL$6B888FCB" PARTITION_SORT)
      LEADING(@"SEL$6B888FCB" (("edu_dw"."q"@"SEL$2" "edu_dw"."a"@"SEL$2") "edu_dw"."r"@"SEL$2"))
      USE_HASH(@"SEL$6B888FCB" "edu_dw"."r"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$6B888FCB" "edu_dw"."r"@"SEL$2" LOCAL LOCAL)
      USE_HASH(@"SEL$6B888FCB" "edu_dw"."a"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$6B888FCB" "edu_dw"."a"@"SEL$2" LOCAL LOCAL)
      INDEX(@"SEL$6B888FCB" "q"@"SEL$2" "q_id")
      FULL(@"SEL$6B888FCB" "a"@"SEL$2")
      FULL(@"SEL$6B888FCB" "r"@"SEL$2")
      SIMPLIFY_DISTINCT(@"SEL$2")
      PROJECT_PRUNE(@"SEL$6E5640F5")
      REPLACE_CONST(@"SEL$6942E515")
      MERGE(@"SEL$1EED3BDE" < "SEL$1")
      OUTER_TO_INNER(@"SEL$25931A74")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  q:
      table_rows:19219
      physical_range_rows:19296
      logical_range_rows:19296
      index_back_rows:0
      output_rows:19296
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[q_id, idx_m_q, idx_m_g_q, idx_y_q, q_answer_code, q_answer_code_for_rpt, dw_question]
      pruned_index_name:[idx_m_q, idx_m_g_q, idx_y_q, q_answer_code, q_answer_code_for_rpt]
      stats version:1701439217343867
      dynamic sampling level:0
  a:
      table_rows:278331
      physical_range_rows:278331
      logical_range_rows:278331
      index_back_rows:0
      output_rows:278331
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[diagnostician_id, naire_answer_rec_id, q_id, tmp_mazg_l_r]
      stats version:1702004269208126
      dynamic sampling level:0
  r:
      table_rows:1800426
      physical_range_rows:1800426
      logical_range_rows:1800426
      index_back_rows:0
      output_rows:1800426
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[school_code, diagnostician_id, school_seq, naire_answer_rec_id, school_term_id, dw_naire_answer_rec]
      pruned_index_name:[school_code, diagnostician_id, school_seq, naire_answer_rec_id, school_term_id]
      stats version:1701871320387399
      dynamic sampling level:0
  Plan Type:
      DISTRIBUTED
  Note:
      Degree of Parallelisim is 1 because of table property
  Expr Constraints:
      cast(1, INT(1, 0)) = 1 result is TRUE

子查询的执行计划

===================================================================
|ID|OPERATOR                       |NAME    |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------------
|0 |MERGE GROUP BY                 |        |1377    |5815101     |
|1 |└─PARTITION SORT               |        |275304  |5737821     |
|2 |  └─HASH JOIN                  |        |275304  |5645341     |
|3 |    ├─HASH JOIN                |        |279447  |147127      |
|4 |    │ ├─PX COORDINATOR         |        |19296   |9841        |
|5 |    │ │ └─EXCHANGE OUT DISTR   |:EX10000|19296   |6871        |
|6 |    │ │   └─TABLE FULL SCAN    |q(q_id) |19296   |248         |
|7 |    │ └─TABLE FULL SCAN        |a       |278331  |40651       |
|8 |    └─PX COORDINATOR           |        |1800426 |4848310     |
|9 |      └─EXCHANGE OUT DISTR     |:EX20000|1800426 |3379728     |
|10|        └─PX PARTITION ITERATOR|        |1800426 |79041       |
|11|          └─TABLE FULL SCAN    |r       |1800426 |79041       |
===================================================================
Outputs & filters:
-------------------------------------
  0 - output([r.q_naire_id(0x7f582ec2eb20)], [a.diagnostician_id(0x7f582ec2f120)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)], 
      [a.is_min_answer(0x7f582ec2fd60)], [T_FUN_COUNT(distinct a.is_min_answer(0x7f582ec2fd60))(0x7f582ec30050)]), filter([T_FUN_COUNT(distinct a.is_min_answer(0x7f582ec2fd60))(0x7f582ec30050) 
      = 1(0x7f582ec31370)], [a.is_min_answer(0x7f582ec2fd60) = 1(0x7f582ec32370)]), rowset=256
      group([r.q_naire_id(0x7f582ec2eb20)], [a.diagnostician_id(0x7f582ec2f120)]), agg_func([T_FUN_COUNT(distinct a.is_min_answer(0x7f582ec2fd60))(0x7f582ec30050)])
  1 - output([r.q_naire_id(0x7f582ec2eb20)], [a.diagnostician_id(0x7f582ec2f120)], [a.is_min_answer(0x7f582ec2fd60)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)]), filter(nil), rowset=256
      sort_keys([HASH(r.q_naire_id(0x7f582ec2eb20), a.diagnostician_id(0x7f582ec2f120))(0x7f5600292650), ASC], [r.q_naire_id(0x7f582ec2eb20), ASC], [a.diagnostician_id(0x7f582ec2f120),
       ASC])
  2 - output([r.q_naire_id(0x7f582ec2eb20)], [a.diagnostician_id(0x7f582ec2f120)], [a.is_min_answer(0x7f582ec2fd60)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)]), filter(nil), rowset=256
      equal_conds([a.naire_answer_rec_id(0x7f582ec26c60) = r.naire_answer_rec_id(0x7f582ec26f50)(0x7f582ec26510)]), other_conds(nil)
  3 - output([a.diagnostician_id(0x7f582ec2f120)], [a.is_min_answer(0x7f582ec2fd60)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)],
       [a.naire_answer_rec_id(0x7f582ec26c60)]), filter(nil), rowset=256
      equal_conds([a.q_id(0x7f582ec2a440) = q.q_id(0x7f582ec2a730)(0x7f582ec29cf0)]), other_conds(nil)
  4 - output([q.q_id(0x7f582ec2a730)]), filter(nil), rowset=256
  5 - output([q.q_id(0x7f582ec2a730)]), filter(nil), rowset=256
      is_single, dop=1
  6 - output([q.q_id(0x7f582ec2a730)]), filter(nil), rowset=256
      access([q.q_id(0x7f582ec2a730)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([q.q_id(0x7f582ec2a730)]), range(MIN ; MAX)always true
  7 - output([a.naire_answer_rec_id(0x7f582ec26c60)], [a.q_id(0x7f582ec2a440)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)], [a.diagnostician_id(0x7f582ec2f120)],
       [a.is_min_answer(0x7f582ec2fd60)]), filter([a.school_code(0x7f582ec2b990) = 'fsswgyxx'(0x7f582ec2b240)], [cast(a.school_term_id(0x7f582ec2cbf0), DECIMAL(11,
       0))(0x7f582ec2d160) = cast('2321', DECIMAL(1, -1))(0x7f582ec2dcb0)(0x7f582ec2c4a0)]), rowset=256
      access([a.naire_answer_rec_id(0x7f582ec26c60)], [a.q_id(0x7f582ec2a440)], [a.school_code(0x7f582ec2b990)], [a.school_term_id(0x7f582ec2cbf0)], [a.diagnostician_id(0x7f582ec2f120)],
       [a.is_min_answer(0x7f582ec2fd60)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false,false], 
      range_key([a.__pk_increment(0x7f582ec337f0)]), range(MIN ; MAX)always true
  8 - output([r.q_naire_id(0x7f582ec2eb20)], [r.naire_answer_rec_id(0x7f582ec26f50)]), filter(nil), rowset=256
  9 - output([r.q_naire_id(0x7f582ec2eb20)], [r.naire_answer_rec_id(0x7f582ec26f50)]), filter(nil), rowset=256
      dop=1
 10 - output([r.naire_answer_rec_id(0x7f582ec26f50)], [r.q_naire_id(0x7f582ec2eb20)]), filter(nil), rowset=256
      force partition granule
 11 - output([r.naire_answer_rec_id(0x7f582ec26f50)], [r.q_naire_id(0x7f582ec2eb20)]), filter(nil), rowset=256
      access([r.naire_answer_rec_id(0x7f582ec26f50)], [r.q_naire_id(0x7f582ec2eb20)]), partitions(p[0-99])
      is_index_back=false, is_global_index=false, 
      range_key([r.naire_answer_rec_id(0x7f582ec26f50)], [r.school_code(0x7f582ec25bf0)]), range(MIN,MIN ; MAX,MAX)always true
Used Hint:
-------------------------------------
  /*+
      
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$354178DD > SEL$0A4D2E68
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH_AGGREGATION(@"SEL$0A4D2E68" PARTITION_SORT)
      LEADING(@"SEL$0A4D2E68" (("edu_dw"."q"@"SEL$1" "edu_dw"."a"@"SEL$1") "edu_dw"."r"@"SEL$1"))
      USE_HASH(@"SEL$0A4D2E68" "edu_dw"."r"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$0A4D2E68" "edu_dw"."r"@"SEL$1" LOCAL LOCAL)
      USE_HASH(@"SEL$0A4D2E68" "edu_dw"."a"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$0A4D2E68" "edu_dw"."a"@"SEL$1" LOCAL LOCAL)
      INDEX(@"SEL$0A4D2E68" "q"@"SEL$1" "q_id")
      FULL(@"SEL$0A4D2E68" "a"@"SEL$1")
      FULL(@"SEL$0A4D2E68" "r"@"SEL$1")
      SIMPLIFY_DISTINCT(@"SEL$1")
      OUTER_TO_INNER(@"SEL$354178DD")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  q:
      table_rows:19219
      physical_range_rows:19296
      logical_range_rows:19296
      index_back_rows:0
      output_rows:19296
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[q_id, idx_m_q, idx_m_g_q, idx_y_q, q_answer_code, q_answer_code_for_rpt, dw_question]
      pruned_index_name:[idx_m_q, idx_m_g_q, idx_y_q, q_answer_code, q_answer_code_for_rpt]
      stats version:1701439217343867
      dynamic sampling level:0
  a:
      table_rows:278331
      physical_range_rows:278331
      logical_range_rows:278331
      index_back_rows:0
      output_rows:278331
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[diagnostician_id, naire_answer_rec_id, q_id, tmp_mazg_l_r]
      stats version:1702004269208126
      dynamic sampling level:0
  r:
      table_rows:1800426
      physical_range_rows:1800426
      logical_range_rows:1800426
      index_back_rows:0
      output_rows:1800426
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[school_code, diagnostician_id, school_seq, naire_answer_rec_id, school_term_id, dw_naire_answer_rec]
      pruned_index_name:[school_code, diagnostician_id, school_seq, naire_answer_rec_id, school_term_id]
      stats version:1701871320387399
      dynamic sampling level:0
  Plan Type:
      DISTRIBUTED
  Note:
      Degree of Parallelisim is 1 because of table property
1 个赞
#5

@秃蛙 执行计划好像一样，很奇怪。表结构见附件
sql.txt (8.4 KB)

如果加上no_rewrite结果是对的

select  a.q_naire_id, a.diagnostician_id, school_code, school_term_id,concat('反向互斥问卷全选最左侧或最右侧答案！', a.q_naire_id) invalid_reason 
from
(


		SELECT  /*+ no_rewrite*/
			distinct r.q_naire_id, a.diagnostician_id, a.school_code, a.school_term_id, is_min_answer, count(distinct is_min_answer) minc
		FROM
			tmp_mazg_L_R a
			join dw_naire_answer_rec r on a.naire_answer_rec_id = r.naire_answer_rec_id
			join dw_question q on a.q_id=q.q_id
		where 
			a.school_code='fsswgyxx' and a.school_term_id='2321'
		group by r.q_naire_id, a.diagnostician_id
		HAVING (minc=1 and is_min_answer=1)
) a
1 个赞
#6

你好 是查询结果条数不一致还是内容不一致，能否提供复现数据呢，暂时没复现出来。

1 个赞
#7

结果条数不一致，数据库版本 5.7.25-OceanBase_CE-v4.2.1.1

#8

@秃蛙 您去掉/+ no_rewrite/ 执行，结果不一致

#9

你好，造数据测试结果集一致，暂未复现出来。是否可以能提供脱敏的复现数据呢。

#10

可以测试下这个。
1）直连 设置set global _nlj_batching_enabled=false;
2）退出重连数据库
3）直连数据库，不带 /+ no_rewrite /执行sql，查看结果集是否正确。

#11

问题已经解决，当前查询sql不符合only_full_group_by的语法，查询了非group by列结果不稳定的，该问题在原生mysql同样有风险。建议用户侧改写sql或业务规避。