[ https://issues.apache.org/jira/browse/HAWQ-835?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15352541#comment-15352541 ]
ASF GitHub Bot commented on HAWQ-835: ------------------------------------- Github user huor closed the pull request at: https://github.com/apache/incubator-hawq/pull/754 > 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: Ruilong Huo > > 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= <NULL> > CONTEXT: SQL statement "SELECT madlib.kmeanspp_seeding( $1 , $2 , $3 , $4 > , NULL, $5 )" > PL/pgSQL function "kmeanspp" line 10 at SQL statement > WARNING: D1 = <NULL>, D2 = <NULL> > 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)