You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hawq.apache.org by "Ruilong Huo (JIRA)" <ji...@apache.org> on 2016/06/20 02:47:05 UTC
[jira] [Created] (HAWQ-835) Cannot retrieve tuple from temp table
created in function
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= <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)