Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12491

JOIN: solve field name ambiguity when possible

    XMLWordPrintable

    Details

      Description

      With JOINs, sometimes we get this error:

      ERROR 1052 (23000): Column 'ENGINE' in where clause is ambiguous
      

      It happens because multiple tables share the same column name, and we did not reference it in the form `table`.`column`. But sometimes the user didn't because he doesn't think it's necessary, and from a purely logical point of view he's right. I can think at least 2 cases.

      1) Think this ON:

      ON t1.id = t2.id
      

      If we are only joining t1 and t2, the values are identical.

      2) Think about a semi-join:

      ON t1.id = t2.id
      WHERE t2.name IS NULL
      ORDER BY name
      

      Unless we are joining more tables, we mean ORDER BY t1.name. I know that the language allows us to order by a constant NULL value. But in a real case semi-join it seems to me perfectly safe to assume that we are referencing a column from t1.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            f_razzoli Federico Razzoli
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.