search

turbot / steampipe-postgres-fdw

The Steampipe foreign data wrapper (FDW) is a zero-ETL product that provides Postgres foreign tables which translate queries into API calls to cloud services and APIs. It's bundled with Steampipe and also available as a set of standalone extensions for use in your own Postgres database.
https://steampipe.io/
Apache License 2.0
70 stars 16 forks source link

unexpected behavior with a author_id = (select id from foo) query #39

Open kaidaguerre opened 3 years ago

kaidaguerre commented 3 years ago

The join works, but the sub-select does not. Also interesting is that the log shows a ? as the value:

2021-03-23 23:07:21.363 EDT [12667] LOG:  statement: select id, text from twitter_user_tweet_timeline where author_id = (select '1318177503995985921')
2021-03-23T23:07:21.369-0400 [WARN]  plugin.steampipe-plugin-twitter.plugin: [WARN]  executeListCall we have single key column
2021-03-23T23:07:21.369-0400 [WARN]  plugin.steampipe-plugin-twitter.plugin: [WARN]  listUserTweetTimeline: authorID=?
~/src/steampipe-plugin-twitter $ steampipe query
Welcome to Steampipe v0.3.2
For more information, type .help
> select id, text from twitter_user_tweet_timeline where author_id = '1318177503995985921'
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = any(array['1318177503995985921'])
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = (select '1318177503995985921')
+----+------+
| id | text |
+----+------+
+----+------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = (select id from twitter_user where username = 'steampipeio')
+----+------+
| id | text |
+----+------+
+----+------+
> 
> select t.id, t.text from twitter_user_tweet_timeline as t, twitter_user as u where t.author_id = u.id and u.username = 'steampipeio'
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
e-gineer commented 3 years ago

As @johnsmyth suggested, using in instead of = works ... which is great, but I believe the = should still work in this case.

> select id, text from twitter_user_tweet_timeline where author_id in (select id from twitter_user where username = 'steampipeio')
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
kaidaguerre commented 3 years ago

this is the same issue as https://github.com/turbot/steampipe-postgres-fdw/issues/46

the query plan for = is 

explain select id, text from twitter_user_tweet where user_id = (select id from twitter_user where username = 'steampipeio')
+--------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                             |
+--------------------------------------------------------------------------------------------------------+
| Foreign Scan on twitter_user_tweet  (cost=20000000000000.00..50000000000000.00 rows=1000000 width=300) |
|   Filter: (user_id = $0)                                                                               |
|   InitPlan 1 (returns $0)                                                                              |
|     ->  Foreign Scan on twitter_user  (cost=0.00..20000000000000.00 rows=1000000 width=200)            |
|           Filter: (username = 'steampipeio'::text)                                                     |
+--------------------------------------------------------------------------------------------------------+
>

the query plan for in is 

explain select id, text from twitter_user_tweet where user_id in (select id from twitter_user where username = 'steampipeio')
+-------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                      |
+-------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=20000000002500.00..20000000002800.02 rows=500000 width=64)                   |
|   ->  HashAggregate  (cost=20000000002500.00..20000000002500.01 rows=1 width=200)               |
|         Group Key: twitter_user.id                                                              |
|         ->  Foreign Scan on twitter_user  (cost=0.00..20000000000000.00 rows=1000000 width=200) |
|               Filter: (username = 'steampipeio'::text)                                          |
|   ->  Foreign Scan on twitter_user_tweet  (cost=0.00..300.00 rows=1 width=300)                  |
|         Filter: (user_id = twitter_user.id)                                                     |
+-------------------------------------------------------------------------------------------------+
kaidaguerre commented 3 years ago

https://github.com/turbot/steampipe-postgres-fdw/issues/46#issuecomment-859558531