[jira] [Closed] (HAWQ-835) Cannot retrieve tuple from temp table created in function
[
https://issues.apache.org/jira/browse/HAWQ-835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruilong Huo closed HAWQ-835.
> 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
[jira] [Closed] (HAWQ-835) Cannot retrieve tuple from temp table created in function
[
https://issues.apache.org/jira/browse/HAWQ-835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruilong Huo closed HAWQ-835.
> 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
>
> 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
