Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id ED29F200CAE for ; Wed, 21 Jun 2017 10:26:44 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id EBC9C160BE2; Wed, 21 Jun 2017 08:26:44 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 1619F160BD0 for ; Wed, 21 Jun 2017 10:26:43 +0200 (CEST) Received: (qmail 63906 invoked by uid 500); 21 Jun 2017 08:26:42 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 63895 invoked by uid 99); 21 Jun 2017 08:26:41 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Jun 2017 08:26:41 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 0D031192922 for ; Wed, 21 Jun 2017 08:26:41 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -0.897 X-Spam-Level: X-Spam-Status: No, score=-0.897 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-2.796, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id D2NfGYrUiTuo for ; Wed, 21 Jun 2017 08:26:35 +0000 (UTC) Received: from mail-wr0-f176.google.com (mail-wr0-f176.google.com [209.85.128.176]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 72BA25F6CD for ; Wed, 21 Jun 2017 08:26:35 +0000 (UTC) Received: by mail-wr0-f176.google.com with SMTP id r103so124389701wrb.0 for ; Wed, 21 Jun 2017 01:26:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=zmkyXb+CGd5hlj4OCXLeb59wzhmMwhTR2Uxg1ogatN8=; b=Vxvv+R/ecj+Uaix/FzXBCyTZ/10SOTTVWZ0Rn6h2b3x2SzbuaRy7VhW2Vc9K48kufz poX6wOnKiRlkXJf60H7JPa2rlZE9a3DTOE5FDWwt6gKQ3zUcMPotLX2Vjx3mIFnSF5zD OcC4BxZ7L2UthCNfHgdXI2vQ9cotM2GieG/J/IJ+KXOB66mecU17idMBLQrEoAaYgEX+ vVJqSpjsSdFmY+AOkN4M9QqjN7lYOcAIRUfO+JlYaaUc9yjCM7xm7esB2inp/TAXOdZA dnMcxQUVApvCJ6J1I9ThLSTm9JNgjn7x7ED9BU6knEmwPgK7Y3VWW4yQ21qLpNEn9Rxa KE5w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=zmkyXb+CGd5hlj4OCXLeb59wzhmMwhTR2Uxg1ogatN8=; b=do7HVL0FUpeLFYkS5g3IJnmeUom0hF5pRoRRx6cEHgK78IyV8+IqUNTph5n98ff6lZ c/IwT86DdVg0s/mNmOsq6JdfxBbQ8fM6x7vN2nenq5pJyvnNR9CRtsfKhpZyhEA4ZK15 At2IHUwktU/zio+6mAbIepfydT90NvIRkjozPvOxONx0id8Wdca1JF0kjQCxe1ydpTrQ lENZfdhi18gikiRPXcfD+H3VuwaezduAlOTj8CrVHLuqc/aOcuaNxTiu90abjKt0Ez3E nNTCJrI8ATOM5eq+z+QHqxzoGkaJs2yYvfnoeZpwIYW1KsZPu4sRgWATxBbrh98/qKxs /23A== X-Gm-Message-State: AKS2vOwFhO5GkS/BJ4MTJsSVah7W4rUjKgxLqvqqdpyXfkk5ThPEOtb+ iixDsWqImaiUDYClbClzID2hpcKdUXrw X-Received: by 10.28.189.67 with SMTP id n64mr5739244wmf.115.1498033594182; Wed, 21 Jun 2017 01:26:34 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.59.130 with HTTP; Wed, 21 Jun 2017 01:26:33 -0700 (PDT) From: Xiaomeng Huang Date: Wed, 21 Jun 2017 16:26:33 +0800 Message-ID: Subject: wrong result with CTAS(create table as select) To: user@hive.apache.org Content-Type: multipart/alternative; boundary="001a114b0a6e3e32f10552742007" archived-at: Wed, 21 Jun 2017 08:26:45 -0000 --001a114b0a6e3e32f10552742007 Content-Type: text/plain; charset="UTF-8" Hi, I missed a bug when use CTAS. 1. hive> select 'test' as did from abc_test_old > where did = '5FCAFD34-C124-4E13-AF65-27B675C945CC' limit 1; OK test 2. hive> create table abc_test_12345 as > select 'test' as did from abc_test_old > where did = '5FCAFD34-C124-4E13-AF65-27B675C945CC' limit 1; hive> select did from abc_test_12345 limit 1; OK 5FCAFD34-C124-4E13-AF65-27B675C945CC why result is not 'test'? 3. hive> explain > create table abc_test_12345 as > select 'test' as did from abc_test_old > where did = '5FCAFD34-C124-4E13-AF65-27B675C945CC' limit 1; OK STAGE DEPENDENCIES: Stage-1 is a root stage Stage-6 depends on stages: Stage-1 , consists of Stage-3, Stage-2, Stage-4 Stage-3 Stage-0 depends on stages: Stage-3, Stage-2, Stage-5 Stage-7 depends on stages: Stage-0 Stage-2 Stage-4 Stage-5 depends on stages: Stage-4 STAGE PLANS: Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: abc_test_old Statistics: Num rows: 32 Data size: 1152 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (did = '5FCAFD34-C124-4E13-AF65-27B675C945CC') (type: boolean) Statistics: Num rows: 16 Data size: 576 Basic stats: COMPLETE Column stats: NONE Select Operator Statistics: Num rows: 16 Data size: 576 Basic stats: COMPLETE Column stats: NONE Limit Number of rows: 1 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator sort order: Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE Reduce Operator Tree: Select Operator expressions: '5FCAFD34-C124-4E13-AF65-27B675C945CC' (type: string) outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE Limit Number of rows: 1 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: true Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde name: default.abc_test_12345 why expressions is '5FCAFD34-C124-4E13-AF65-27B675C945CC' ? --001a114b0a6e3e32f10552742007 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi, I missed a bug when use CTAS.

1.=C2= =A0
hive> select 'test' as did from abc_test_old<= /div>
=C2=A0 =C2=A0 > where did =3D '5FCAFD34-C124-4E13-AF65-27B= 675C945CC' limit 1;
OK
test =C2=A0


2.=C2=A0
hive>= create table abc_test_12345 as
=C2=A0 =C2=A0 > select 'te= st' as did from abc_test_old
=C2=A0 =C2=A0 > where did =3D= '5FCAFD34-C124-4E13-AF65-27B675C945CC' limit 1;
hive> select did from abc_test_12345 limit 1;
OK
5FCAFD34-C124-4E13-AF65-27B675C945CC= =C2=A0

why result is not 'test= 9;?

3.=C2=A0
hive> explain
=
=C2=A0 =C2=A0 > create table abc_test_12345 as
=C2=A0 =C2= =A0 > select 'test' as did from abc_test_old
=C2=A0 = =C2=A0 > where did =3D '5FCAFD34-C124-4E13-AF65-27B675C945CC' li= mit 1;
OK
STAGE DEPENDENCIES:
=C2=A0 Stage-1 = is a root stage
=C2=A0 Stage-6 depends on stages: Stage-1 , consi= sts of Stage-3, Stage-2, Stage-4
=C2=A0 Stage-3
=C2=A0 = Stage-0 depends on stages: Stage-3, Stage-2, Stage-5
=C2=A0 Stage= -7 depends on stages: Stage-0
=C2=A0 Stage-2
=C2=A0 Sta= ge-4
=C2=A0 Stage-5 depends on stages: Stage-4

STAGE PLANS:
=C2=A0 Stage: Stage-1
=C2=A0 =C2=A0= Map Reduce
=C2=A0 =C2=A0 =C2=A0 Map Operator Tree:
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TableScan
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 alias: abc_test_old
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Statistics: Num rows: 32 Data size: 1152 Basic stats: COM= PLETE Column stats: NONE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Filter Operator
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 predicate: (did =3D '5FCAFD34-C124-4E13-AF65-27B675C945CC') (ty= pe: boolean)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sta= tistics: Num rows: 16 Data size: 576 Basic stats: COMPLETE Column stats: NO= NE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Select Operat= or
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Statis= tics: Num rows: 16 Data size: 576 Basic stats: COMPLETE Column stats: NONE<= /div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Limit
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Numbe= r of rows: 1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Colu= mn stats: NONE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 Reduce Output Operator
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sort order:
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Statistics: Num= rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
= =C2=A0 =C2=A0 =C2=A0 Reduce Operator Tree:
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 Select Operator
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 expressions: '5FCAFD34-C124-4E13-AF65-27B675C945CC= 9; (type: string)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 outpu= tColumnNames: _col0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Statistics= : Num rows: 1 Data size: 36 Basic stats: COMPLETE Column stats: NONE
<= div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Limit
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 Number of rows: 1
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLE= TE Column stats: NONE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 F= ile Output Operator
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 compressed: true
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Statistics: Num rows: 1 Data size: 36 Basic stats: COMPLETE Column s= tats: NONE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 table= :
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 output format: = org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 serde: org.apache.hadoop.h= ive.ql.io.orc.OrcSerde
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 name: default.abc_test_12345

why expressions is=C2=A0'5FCAF= D34-C124-4E13-AF65-27B675C945CC' ?
--001a114b0a6e3e32f10552742007--