You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2024/10/26 12:05:31 UTC

[I] (POTENTIAL) Regression [datafusion]

alamb opened a new issue, #13124:
URL: https://github.com/apache/datafusion/issues/13124

   ### Describe the bug
   
   This was reported by @findepi  on https://github.com/apache/datafusion/pull/12864 in  https://github.com/apache/datafusion/pull/12864#issuecomment-2437123342. I am pulling it into its own ticket so we can track it / find it more easily
   
   
   > I think as long as there is no regression we should move on and file the ticket for the remaining issue.
   
   Sorry for not following earlier, was on a full-day event yesterday.
   
   There are regressions.
   I kind of felt it's obvious from the way it works, sorry for not providing good examples earlier.
   
   ### Before the change
   
   ```
   > CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('+123')) t(a); SELECT * FROM t;
   0 row(s) fetched.
   Elapsed 0.005 seconds.
   
   +---+
   | a |
   +---+
   | 4 |
   +---+
   ```
   
   ```
   > CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('abcd')) t(a); SELECT * FROM t;
   0 row(s) fetched.
   Elapsed 0.078 seconds.
   
   +---+
   | a |
   +---+
   | 4 |
   +---+
   ```
   
   ### on current `main`
   
   Wrong result:
   
   ```
   > CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('+123')) t(a); SELECT * FROM t;
   0 row(s) fetched.
   Elapsed 0.071 seconds.
   
   +---+
   | a |
   +---+
   | 3 |
   ```
   
   failure
   ```
   > CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('abcd')) t(a); SELECT * FROM t;
   Arrow error: Cast error: Cannot cast string 'abcd' to value of Int32 type
   ```
   
   
   
   
   
   ### To Reproduce
   
   _No response_
   
   ### Expected behavior
   
   _No response_
   
   ### Additional context
   
   _No response_


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES` [datafusion]

Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2439555453

   Should we consider fixing this? I’m leaning towards not making changes, primarily to stay consistent with PostgreSQL’s syntax. Given the lack of test coverage, it seems this syntax might not be intentionally supported.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES` [datafusion]

Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2439661226

   Just curious -- if we knew about this prior to merging https://github.com/apache/datafusion/pull/12864, would we still merge it?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2441693366

   > Just curious -- if we knew about this prior to merging #12864, would we still merge it?
   
   I think we would have had the same discussion we are having now -- is the benefit gained from https://github.com/apache/datafusion/pull/12864 worth the potential regression in a feature that maybe worked by accident. I also think it is reasonable to have different opinions on the cost/benefit analysis, for what it is worth
   
   The conversation would have been different if there had been tests showing someone was relying on the old behavior
   
   In such cases I normally default to going with the person contributing the change / putting in the effort to maintain it. If someone else was relying on the feature they'll tell us with a PR / issue in the future
   
   I do wish we had infinite capacity to make the software perfect, but given we are constrained with resources I think we need to be pragmatic and align the contributions. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] (POTENTIAL) Regression [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2439553160

   @jayzhan211  notes https://github.com/apache/datafusion/pull/12864#issuecomment-2437192214
   
   > It seems the query is accidentally correct in before this change, because we don't know the result of the function when we build up Values plan. After this change, incorrect values ('abcd') is cast to column a instead of the result of the function.
   > 
   > The ideally solution is to find the result type of the function and check whether it matches the column type.
   > 
   > btw, I wonder is this query valid in postgres or elsewhere?
   > 
   > Valid query in postgres `CREATE TABLE t AS SELECT length(a)::int AS a FROM (VALUES ('+123')) t(a);`
   > 
   > Invalid query in postgres `CREATE TABLE t (a int) AS SELECT length(a)::int AS a FROM (VALUES ('+123')) t(a);`
   > 
   > ```
   > ERROR:  syntax error at or near "AS"
   > LINE 1: CREATE TABLE t(a int) AS SELECT length(a)::int AS a FROM (VA...
   > ```
   > 
   > It seems there is no way to insert value together with table if the column type is defined 🤔
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES` [datafusion]

Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2439984175

   I would prefer not to block on the query not supported by PostgreSQL, especially when it's not intended for support in DataFusion (no tests or issues related).
   
   The query worked previously because we weren’t validating values against the schema, meaning it was correct by coincidence, not by design.
   
   What do you think about this kind of query? Should we not merge it and what is the reason?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES` [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13124:
URL: https://github.com/apache/datafusion/issues/13124#issuecomment-2439573517

   > Should we consider fixing this? I’m leaning towards not making changes, primarily to stay consistent with PostgreSQL’s syntax. Given the lack of test coverage, it seems this syntax might not be intentionally supported.
   
   I personally think it would nice to fix but there is no reason you need to do it personally or we need to block the 43 release for it. My rationale is:
   1. As you say this isn't something postgres supports
   2. there is a workaround for anyone relying on the old behavior -- which is to use another temporary relation
   
   
   Here is the workaround
   
   This fails
   ```sql
   CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('abcd')) t(a); 
   SELECT * FROM t;
   ```
   
   You can rewrite it to:
   
   ```
   > CREATE OR REPLACE TABLE temp as  VALUES ('abcd');
   0 row(s) fetched.
   Elapsed 0.006 seconds.
   
   > CREATE OR REPLACE TABLE t(a int) AS SELECT length(column1) from temp;
   0 row(s) fetched.
   Elapsed 0.008 seconds.
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org