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 7C7B2200B30 for ; Mon, 20 Jun 2016 04:47:11 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 7B2D3160A66; Mon, 20 Jun 2016 02:47:11 +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 7E529160A53 for ; Mon, 20 Jun 2016 04:47:10 +0200 (CEST) Received: (qmail 98897 invoked by uid 500); 20 Jun 2016 02:47:09 -0000 Mailing-List: contact dev-help@hawq.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hawq.incubator.apache.org Delivered-To: mailing list dev@hawq.incubator.apache.org Received: (qmail 98886 invoked by uid 99); 20 Jun 2016 02:47:09 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 20 Jun 2016 02:47:09 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 3B4BB1A06F7 for ; Mon, 20 Jun 2016 02:47:09 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -5.446 X-Spam-Level: X-Spam-Status: No, score=-5.446 tagged_above=-999 required=6.31 tests=[KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_HI=-5, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-1.426] autolearn=disabled Received: from mx2-lw-us.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id LD0juKpDaNsw for ; Mon, 20 Jun 2016 02:47:06 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx2-lw-us.apache.org (ASF Mail Server at mx2-lw-us.apache.org) with SMTP id 174765FAC5 for ; Mon, 20 Jun 2016 02:47:05 +0000 (UTC) Received: (qmail 98813 invoked by uid 99); 20 Jun 2016 02:47:05 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 20 Jun 2016 02:47:05 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 4C42F2C1F5D for ; Mon, 20 Jun 2016 02:47:05 +0000 (UTC) Date: Mon, 20 Jun 2016 02:47:05 +0000 (UTC) From: "Ruilong Huo (JIRA)" To: dev@hawq.incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (HAWQ-835) Cannot retrieve tuple from temp table created in function MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Mon, 20 Jun 2016 02:47:11 -0000 Ruilong Huo created HAWQ-835: -------------------------------- Summary: Cannot retrieve tuple from temp table created in function Key: HAWQ-835 URL: https://issues.apache.org/jira/browse/HAWQ-835 Project: Apache HAWQ Issue Type: Bug Components: Core, Query Execution Reporter: Ruilong Huo Assignee: George Caragea With function which create temp table and insert tuple into it, if the function is run multiple times, it might cannot retrieve tuple from the temp table in the second run of the function and so on. Here are the steps to reproduce: Step 1: prepare schema and data {noformat} CREATE TABLE t(pid int, points double precision[]); COPY t (pid, points) FROM stdin DELIMITER '|'; 1 | {14.23, 1.71, 2.43, 15.6, 127, 2.8, 3.0600, 0.2800, 2.29, 5.64, 1.04, 3.92, 1065} 2 | {13.2, 1.78, 2.14, 11.2, 1, 2.65, 2.76, 0.26, 1.28, 4.38, 1.05, 3.49, 1050} 3 | {13.16, 2.36, 2.67, 18.6, 101, 2.8, 3.24, 0.3, 2.81, 5.6799, 1.03, 3.17, 1185} 4 | {14.37, 1.95, 2.5, 16.8, 113, 3.85, 3.49, 0.24, 2.18, 7.8, 0.86, 3.45, 1480} 5 | {13.24, 2.59, 2.87, 21, 118, 2.8, 2.69, 0.39, 1.82, 4.32, 1.04, 2.93, 735} 6 | {14.2, 1.76, 2.45, 15.2, 112, 3.27, 3.39, 0.34, 1.97, 6.75, 1.05, 2.85, 1450} \. {noformat} Step 2: run kmeans {noformat} with q as ( select 1 as num_clusters, * from madlib.kmeanspp( 't', 'points', 3, 'madlib.squared_dist_norm2', 'madlib.avg', 30, 0.001, 1.0 ) ) select q1.* from q as q1, (select * from q) as q2 where q1.num_clusters = q2.num_clusters; ERROR: Kmeans error: No valid initial centroids given. CONTEXT: SQL statement "SELECT madlib.kmeans( $1 , $2 , madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 ), $4 , $6 , $7 , $8 )" PL/pgSQL function "kmeanspp" line 4 at assignment {noformat} Step 3: further investigation shows that it cannot retrieve tuple from temp table {noformat} with q as ( select 1 as num_clusters, * from madlib.kmeanspp( 't', 'points', 3, 'madlib.squared_dist_norm2', 'madlib.avg', 30, 0.001, 1.0 ) ) select q1.* from q as q1, (select * from q) as q2 where q1.num_clusters = q2.num_clusters; WARNING: --- kmeanspp debug begin --- WARNING: --- kmeanspp seeding begin --- WARNING: --- kmeanspp seed summary --- WARNING: ### kmeanspp_seeding: 1. use all source data CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 2. create temp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 0 before create tmp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 140497 after create tmp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 3. generate centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 3. generate centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 140497 after create tmp table CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args has 1 record XXX CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table begin 1 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k, fn_dist, initial_centroids, fn_dist_name CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k="3", items.fn_dist, items.initial_centroids, fn_dist_name="madlib.squared_dist_norm2" CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table end 1 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table metadata begin CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: oid, schemaname, tablename CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args has 1 record YYY CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: "140501", pg_temp_359 _madlib_kmeanspp_args CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table metadata end CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table begin 2 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k, fn_dist, initial_centroids, fn_dist_name CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k="3", items.fn_dist, items.initial_centroids, fn_dist_name="madlib.squared_dist_norm2" CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table end 2 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 4. compute centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: theIteration = 2 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 5. retrieve centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: theResult= {{13.2,1.78,2.14,11.2,1,2.65,2.76,0.26,1.28,4.38,1.05,3.49,1050},{14.37,1.95,2.5,16.8,113,3.85,3.49,0.24,2.18,7.8,0.86,3.45,1480},{13.24,2.59,2.87,21,118,2.8,2.69,0.39,1.82,4.32,1.04,2.93,735}} CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: D1 = 3, D2 = 13 WARNING: --- kmeanspp seed detail begin --- WARNING: 13.2 WARNING: 1.78 WARNING: 2.14 WARNING: 11.2 WARNING: 1 WARNING: 2.65 WARNING: 2.76 WARNING: 0.26 WARNING: 1.28 WARNING: 4.38 WARNING: 1.05 WARNING: 3.49 WARNING: 1050 WARNING: 14.37 WARNING: 1.95 WARNING: 2.5 WARNING: 16.8 WARNING: 113 WARNING: 3.85 WARNING: 3.49 WARNING: 0.24 WARNING: 2.18 WARNING: 7.8 WARNING: 0.86 WARNING: 3.45 WARNING: 1480 WARNING: 13.24 WARNING: 2.59 WARNING: 2.87 WARNING: 21 WARNING: 118 WARNING: 2.8 WARNING: 2.69 WARNING: 0.39 WARNING: 1.82 WARNING: 4.32 WARNING: 1.04 WARNING: 2.93 WARNING: 735 WARNING: --- kmeanspp seed detail end --- WARNING: --- kmeanspp seeding end --- WARNING: --- kmeanspp clustering begin --- WARNING: --- kmeanspp clustering end --- WARNING: --- kmeanspp debug end --- WARNING: --- kmeanspp debug begin --- WARNING: --- kmeanspp seeding begin --- WARNING: --- kmeanspp seed summary --- WARNING: ### kmeanspp_seeding: 1. use all source data CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 2. create temp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 140497 before create tmp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 140497 after create tmp schema CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 3. generate centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 3. generate centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: temp schema id = 140497 after create tmp table CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args has 1 record XXX CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table begin 1 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k, fn_dist, initial_centroids, fn_dist_name CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table end 1 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table metadata begin CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: oid, schemaname, tablename CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args has 1 record YYY CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: "140567", pg_temp_359 _madlib_kmeanspp_args CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table metadata end CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table begin 2 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: k, fn_dist, initial_centroids, fn_dist_name CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: pg_temp._madlib_kmeanspp_args table end 2 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 4. compute centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: theIteration = 0 CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: ### kmeanspp_seeding: 5. retrieve centroids CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: theResult= CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 , NULL, $5 )" PL/pgSQL function "kmeanspp" line 10 at SQL statement WARNING: D1 = , D2 = WARNING: --- kmeanspp seed detail begin --- ERROR: upper bound of FOR loop cannot be NULL CONTEXT: PL/pgSQL function "kmeanspp" line 15 at for with integer loopvar {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)