[
https://issues.apache.org/jira/browse/HAWQ-835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruilong Huo reopened HAWQ-835:
------------------------------
Reopening to add the fix version.
> 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
> Affects Versions: 2.0.0
> Reporter: Ruilong Huo
> Assignee: Ruilong Huo
> Fix For: 2.0.0
>
>
> 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)
