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

Can't find record when using LIMIT 1 with multiple joins

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Duplicate
    • 10.4.6, 10.4.10
    • N/A
    • Server 1: mysql Ver 15.1 Distrib 10.4.10-MariaDB, for osx10.14 (x86_64) using readline 5.1
      Server 2: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) using readline 5.1

    Description

      Given data sources attached.

      Following SQL statement returns 1032 Can't find record in 'nodes_sources':

      SELECT 
      n0_.title AS title_0, 
      n0_.published_at AS published_at_1, 
      n0_.meta_title AS meta_title_2, 
      n0_.meta_keywords AS meta_keywords_3, 
      n0_.meta_description AS meta_description_4, 
      n0_.id AS id_5, 
      n1_.over_title AS over_title_6, 
      n1_.sub_title AS sub_title_7, 
      n1_.sticky AS sticky_8, 
      n1_.excerpt AS excerpt_9, 
      n1_.content AS content_10, 
      n2_.node_name AS node_name_11, 
      n2_.visible AS visible_14, 
      n2_.status AS status_15, 
      n2_.id AS id_26, 
      u3_.alias AS alias_27, 
      u3_.id AS id_28, 
      n0_.discr AS discr_29, 
      n0_.node_id AS node_id_30, 
      n0_.translation_id AS translation_id_31, 
      n2_.nodeType_id AS nodeType_id_32, 
      n2_.parent_node_id AS parent_node_id_33, 
      u3_.ns_id AS ns_id_34
      FROM ns_blogpost n1_ 
      INNER JOIN nodes_sources n0_ ON n1_.id = n0_.id 
      INNER JOIN nodes n2_ ON n0_.node_id = n2_.id
      LEFT JOIN url_aliases u3_ ON n0_.id = u3_.ns_id 
      WHERE n0_.id <> 59 
      AND n2_.visible = 1 
      AND n2_.status = 30
      AND n0_.translation_id = 1 
      AND n0_.published_at > "2019-11-20 11:30:00" 
      ORDER BY n0_.published_at ASC 
      LIMIT 1
      

      You can test commenting the LIMIT 1 line and query will work again.

      Then, using only one INNER JOIN, query is OK (with or without LIMIT):

      SELECT n0_.title AS title_0, 
      n0_.published_at AS published_at_1, 
      n0_.meta_title AS meta_title_2, 
      n0_.meta_keywords AS meta_keywords_3, 
      n0_.meta_description AS meta_description_4, 
      n0_.id AS id_5, 
      n1_.over_title AS over_title_6, 
      n1_.sub_title AS sub_title_7, 
      n1_.sticky AS sticky_8, 
      n1_.excerpt AS excerpt_9, 
      n1_.content AS content_10, 
      n0_.discr AS discr_29, 
      n0_.node_id AS node_id_30, 
      n0_.translation_id AS translation_id_31
      FROM ns_blogpost n1_
      INNER JOIN nodes_sources n0_ ON n1_.id = n0_.id 
      WHERE n0_.id <> 59 
      AND n0_.translation_id = 1 
      AND n0_.published_at >= "2019-11-20 11:30:00" 
      ORDER BY n0_.published_at ASC 
      LIMIT 1
      

      Attachments

        Issue Links

          Activity

            People

              alice Alice Sherepa
              ambroisemaupate Ambroise Maupate
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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