Insert ORDER BY column in SELECT to support SELECT * ORDER BY scatter gather

[PrismaPhonic]

Feature Description

This feature would rewrite SELECT * ORDER BY queries to include the column in the ORDER BY to be part of the SELECT. This would allow us to instantly support SELECT * ORDER BY scatter gather queries without having to resort to the authoritative vschema feature.

Example

SELECT * FROM popsicle WHERE flavor LIKE '%vanilla%' ORDER BY id would get translated to ->
SELECT id, * FROM popsicle WHERE flavor LIKE '%vanilla%' ORDER BY id

[PrismaPhonic]

@systay tagging you for visibility.

[PrismaPhonic]

Just as a follow up, the Active Record ORM will often product SELECT <column names> FROM <table name> ORDER BY <different column names>. This feature request could fix this use case as well.

[derekperkins]

Would you strip the added column before sending it back to the client?

[PrismaPhonic]

Correct @derekperkins. This would be totally unknown by the client, and is one solution for having immediate support for scatter gather ORDER BY where the column in the ORDER BY isn't in the SELECT. In fact the query translation example listed above is not valid SQL, but actually is understood by vtgate and will result in successful query completion.

[inolddays]

hi there ! wondering if this feature is already existing in 8.0 rc1 ?

[GuptaManan100]

Support for this has been added in Gen4 in this PR #8070