hawq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ruilong Huo (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HAWQ-835) Cannot retrieve tuple from temp table created in function
Date Mon, 20 Jun 2016 02:47:05 GMT
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)

Mime
View raw message