postgres confuses table alias for schema in selects - Mailing list pgsql-admin

                  List of relations
  Schema  |           Name            | Type | Owner
----------+---------------------------+------+-------
 aerodemo | vnd_avail_perf_bucket_sum | view | jjs
(1 row)

/* select * from vnd_avail_perf_bucket_sum; */
/* #1 simple */
select count(*) from vnd_avail_perf_bucket_sum;
 count
-------
  1708
(1 row)

/* #2 simple on view, no join */
select count(*) from
(
select
       item_nbr,
       ic_category_nbr,
       weeks_late,
       count(*) bucket_count,
       line_stat_id
from  vnd_avail_perf_bucket_sum
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query;
 count
-------
  1708
(1 row)

;
/* #3 simple with alias  */
select count(*) from
(
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       count(*) bucket_count,
       bucket.line_stat_id
from  vnd_avail_perf_bucket_sum as bucket
group by
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.line_stat_id
) as count_query;
 count
-------
  1708
(1 row)

;
/* #4 error sum on count
   bucket is a table alias, thinks its  a schema */
select count(*) from
(
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       sum(bucket.count(*)) bucket_count,
       bucket.line_stat_id
from  vnd_avail_perf_bucket_sum as bucket
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query;
psql:pct3.sql:60: ERROR:  schema "bucket" does not exist
LINE 9:        sum(bucket.count(*)) bucket_count,
                   ^
;
/* #4.1 bucket defined in with
   observation: works
*/
with bucket as
(
select
       item_nbr,
       ic_category_nbr,
       weeks_late,
       count(*) bucket_count,
       line_stat_id
from  vnd_avail_perf_bucket_sum
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) select count(*) from bucket;
 count
-------
  1708
(1 row)

;
/* #4.2
   no sum on count error
   bucket is a table alias, thinks its  a schema */
select count(*) from
(
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) bucket_count,
       bucket.line_stat_id
from  vnd_avail_perf_bucket_sum as bucket
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query;
psql:pct3.sql:101: ERROR:  schema "bucket" does not exist
LINE 10:        bucket.count(*) bucket_count,
                ^
;
/* #4.3
   no table alias
*/
select count(*) from
(
select
       item_nbr,
       ic_category_nbr,
       weeks_late,
       count(*) bucket_count,
       line_stat_id
from  vnd_avail_perf_bucket_sum
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query;
 count
-------
  1708
(1 row)

;
/* #5 no-count */
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
      bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
;
psql:pct3.sql:139: ERROR:  relation "bucket_sum" does not exist
LINE 9:       bucket_sum
              ^
/* #6 undefined view bucket_sum
   ??? what is wrong with the from clause */
select count(*) from
(
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
      bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query
;
psql:pct3.sql:163: ERROR:  syntax error at or near "from"
LINE 11: from vnd_avail_perf_bucket_sum bucket,
         ^
/* #6.1  bucket_sum using with
   extraneous ')'
   observation: syntax error near from,
   question: why?
*/
select count(*) from
(
with bucket_sum as
(select
       vs.item_nbr,
       vs.ic_category_nbr,
       count(vs.*) total_buckets,
       vs.line_stat_id
from vnd_avail_perf_bucket vs
group by
       vs.item_nbr,
       vs.ic_category_nbr,
       vs.line_stat_id
)
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
      bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query
;
psql:pct3.sql:201: ERROR:  syntax error at or near "from"
LINE 26: from vnd_avail_perf_bucket_sum bucket,
         ^
/* #6.2  bucket_sum using with, no alias on with select
         no extranoues ')'
   observation: syntax error near from,  
   question: why?
*/
select count(*) from
(
with bucket_sum as
(select
       item_nbr,
       ic_category_nbr,
       count(*) total_buckets,
       line_stat_id
from vnd_avail_perf_bucket
group by
       item_nbr,
       ic_category_nbr,
       line_stat_id
)
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
      bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query
;
psql:pct3.sql:238: ERROR:  schema "bucket" does not exist
LINE 24:        bucket.count(*) / bucket_sum.total_buckets bucket_pct...
                ^
/* #6.3  bucket_sum using with, no alias on with select, fixed extraneous paren
   observation: syntax error near from,
   question: why?
*/
select count(*) from
(
with bucket_sum as
(select
       item_nbr,
       ic_category_nbr,
       count(*) total_buckets,
       line_stat_id
from vnd_avail_perf_bucket
group by
       item_nbr,
       ic_category_nbr,
       line_stat_id
)
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
      bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query
;
psql:pct3.sql:274: ERROR:  schema "bucket" does not exist
LINE 23:        bucket.count(*) / bucket_sum.total_buckets bucket_pct...
                ^
/* #7 bucket is a table alias thinks it's a schema  */
select count(*) from
(
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       sum(bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
) as count_query
;
psql:pct3.sql:295: ERROR:  schema "bucket" does not exist
LINE 8:        sum(bucket.count(*) / bucket_sum.total_buckets) bucke...
                   ^
/* #8  ??? thinks bucket is a schema, but it is a table alias */
with bucket_sum as (
select
       vs.item_nbr,
       vs.ic_category_nbr,
       count(vs.*) total_buckets,
       vs.line_stat_id
from vnd_avail_perf_bucket vs
group by
       vs.item_nbr,
       vs.ic_category_nbr,
       vs.line_stat_id
)
select
       bucket.item_nbr,
       bucket.ic_category_nbr,
       bucket.weeks_late,
       bucket.count(*) / bucket_sum.total_buckets bucket_pct,
       bucket.line_stat_id
from vnd_avail_perf_bucket_sum bucket,
     bucket_sum
where bucket_sum.item_nbr = bucket.item_nbr and
      bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
     bucket_sum.line_stat_id = bucket.line_stat_id
group by
       item_nbr,
       ic_category_nbr,
       weeks_late,
       line_stat_id
;
psql:pct3.sql:326: ERROR:  schema "bucket" does not exist
LINE 18:        bucket.count(*) / bucket_sum.total_buckets bucket_pct...
                ^


--